How to select and insert a long type column:
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to select and insert a long type column:

  1. #1
    Join Date
    Jan 2000
    Location
    HOUSTON, TX
    Posts
    75

    Thumbs up

    Oracle 8.1.7.3

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

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    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
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width