How to select a LONG table column?

Oracle Database Tips by Donald BurlesonMay 18, 2015

Question: I need to select a LONG RAW column from a table. How do you select a LONG or LONG RAW table column in SQL?

Answer: First, the LONG and LONG RAW datatypes are superseded by the new LOB datatypes (CLOB, BLOB and BFILE), and I would consider re-defining the table to have a LOB column instead of raw.

Also see Insert into long raw column Tips.

Here is a simple way to display a long raw using utl_raw.cast_to_varchar2:

set long 32767

create table test (c1 long raw);

--insert into test values (utl_raw.cast_to_raw('hello world'));

declare
a varchar(255);
b long raw;
cursor c1 is select c1 from test;
begin
open c1;
loop
fetch c1 into b ;
exit when c1%notfound;
a:=UTL_RAW.CAST_TO_VARCHAR2(b);
dbms_output.put_line(a);
end loop;
end;
/

In SQL*Plus, you can use the "set long" command:

set long 32767
select long_column from mytable;

Outside of SQL*Plus, you need to write a PL/SQL function to convert the LONG column into standard text. David Hunt has this example of a function to convert a LONG to a char datatype:

REM **********************************************************
REM Author: David Hunt
REM Date Created: July 18, 2015
REM Purpose: Creates function that returns contents of
REM         PART_COMNT:COMNT_TXT. (a LONG field)
REM    Warning: if the contents of COMNT_TXT exceed 32767 chrs, then
REM            none of the data from that row's LONG RETURN;
REM                     an error message results.
REM **********************************************************
create or replace function Get_Comnt_txt
    (RowID_of_Long in rowid)
    return varchar2
is
    Long_to_Varchar        varchar(32767);
begin
    select comnt_txt into Long_to_Varchar
        from part_comnt
        where rowid = rowid_of_long;
    return long_to_varchar;
exception
    when others then
        return 'Error occurred on rowid: '||rowid_of_long;
end; /

select replace(get_Comnt_txt(rowid),chr(10),null)comnt_txt
from part_comnt;

COMNT_TXT
----------------------------------------------------------------
This is short text in a LONG column.
This is row 2; a <carriage return> is here ->This is row 2; a <carriage return> is here ->This is row 2; a <carriage return> is here ->

Laurent Schneider notes such a function to search a LONG column within a table:

create table rv_test (a number, b long)
/
insert into rv_test values (1, 'asdfjasraghavkjasdfdsafqafl;kasdfnbdsamnbfdasraghavasdfl')
/
insert into rv_test values (2, 'asdfjasdf;lkjadsflkjdasf;lkjasdflkjdsafl;kasdfnbdsamnbfdasraghavasdflkjasdf')
/
insert into rv_test values (3, & #39;asdfjasdf;lasdfa192387sfdsflkjdasf;lkjasdfdsafq3lkjdsafl;kasdfnbdsamnbfdasra
ghavasdflraghav')
/
commit

-- ************************
create or replace type rv_test_type as object(a number, b clob);
/

create or replace type rv_test_table as table of rv_test_type;
/

create or replace function rv_test_search(x varchar2)
return rv_test_table pipelined is
  r rv_test_type;
begin
  for f in (select * from rv_test) loop
    if (f.b like x) then
      r:= rv_test_type(f.a,f.b);
      pipe row(r);
    end if;
  end loop;
end;
/

select a from table(rv_test_search('%raghav%'));

��