-
Oracle 8.1.7.3
What is the syntax for select and insert a long type column
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|