How to select and insert a long type column:

[OraMan]

Oracle 8.1.7.3

What is the syntax for select and insert a long type column

[rohitsn]

Hi,
LONG columns cannot be referenced when creating a table with query (CREATE TABLE...AS SELECT...) or when inserting into a table with a query (INSERT INTO ... SELECT..). These are restrictions of the long datatype.The following is the workaround for it:

1. Create a varray or nested table type with varchar2(4000).

create type t_long as varray(1000) of varchar2(4000);
/

Note that you can create a VARRAY of the required size or a nested table.

2. Create the function that fetches the long column from the table into this VARRAY and returns the same. NOTE: You should replace long_column and long_table with the appropriate column and table names.

create or replace function f_long return t_long as v_var_long t_long := t_long();
i integer;
cursor c_long is
select long_column from long_table;
begin
i:=1;
for c in c_long
loop
v_var_long.extend;
v_var_long(i) := c.long_column;
i := i+1;
end loop;
return v_var_long;
end;
/

3. Now select the column_value from the function cast as a table using:

select distinct t.column_value FROM TABLE(CAST(f_long() AS t_long)) t;
OR
create a table selecting from this table with a long column using:
OR
create table L2 as select t.column_value from TABLE(CAST(f_long() AS t_long)) t;
OR
insert into another long column selecting from this long column using:
insert into L3 select t.column_value from TABLE(CAST(f_long() AS t_long)) t;

Please note that this workaround is applicable if and only if your data in the long column < 4K because the upper limit of varchar2 is 4000.

Regards,
Rohit Nirkhe,Oracle/Apps DBA,OCP 8i

[TimHall]

You should use CLOBs instead of LONGs and BLOBs instead of LONG RAWs. You'll save yourself loads of grief in the long run.

Cheers