How to read LONG RAW column into VARCHAR2?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to read LONG RAW column into VARCHAR2?

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    28

    Question

    Dear Friends,

    Can anyone tell me how to read a LONG RAW column into a varchar2 variable ? I tried using UTL_RAW.CAST_TO_VARCHAR2 packaged procedure but getting the following error : ORA-00997: illegal use of LONG datatype

    Any ideas ?

    Thanks. Your help is really appreciated.

    Wishing you all a very happy NEW YEAR !

    -ad

  2. #2
    Join Date
    Aug 2000
    Posts
    163
    As far as I know long raw is much much longer Oracle datatype than Oracle varchar2 datatype. You cannot put a larger value in a smaller variable; i.e. if you declare a varchar2 variable and try to populate it wih long raw data it won't work.
    Also, none of the varchar2 or string functions (per example, length, instring, trunc, decode, ect) will not work for long raw datatype.

  3. #3
    Join Date
    Dec 2000
    Posts
    28
    Mary, Thanks for your reply. Actually, It does not matter which way I get the information from LONG RAW. But I need to write the value from this column into a text file.

    Can you suggest any method to do this ?

    Thanks.

  4. #4
    Join Date
    Jan 2001
    Posts
    2
    Set long 10000000;
    spool filename;
    select column_name
    from table;

    This will put the contents of the column into the filename you specify.

  5. #5
    Join Date
    Dec 2000
    Posts
    28
    Hi Shawn,

    That will work for LONG datatype column. But LONG RAW stores the value in HEX format. So if i spool the output it shows only a single character 'A' in the file. This won't be a problem if we had BLOB instead of LONG RAW. Anyhow I am planning to change the datatype.

    Thanks anyway.

    bye
    ad

  6. #6
    Join Date
    Dec 2000
    Posts
    75
    Ad,

    Try the following code using ULT_RAW if you haven't done so.

    -------------------------------------------------------------

    /*Problem description*/
    To convert long raw to varchar

    /*proposed solution*/


    1. Create utl_raw package specification in $ORACLE_HOME\rdbms\admin\utlraw.sql.
    2. create utl_raw package body in $ORACLE_HOME\rdbms\admin\prvtrawb.plb
    3. When you want to convert raw to character , fetch into a cursor and then convert.

    declare
    a varchar(255);
    b long raw;
    cursor c1 is select long_raw from raw_table;
    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;

    goodhealth

  7. #7
    Join Date
    Dec 2000
    Posts
    28
    Hi Goodhealth,

    Thanks for the code snippet. It works... But one small issue... My long raw column contains data length > 32760 chars. In PL/SQL varchar2 variable I am able to declare MAXimum of 32760 chars. Can you give me a hint on how can we read the complete column ( I think we have data upto 1 GB ) for each row.

    Is there a way to read LONG RAW column as chunks of 32760 characters ? If i can do that, then I can use UTL_FILE ( again imposes a limit of 1024 chars for each write) But if I can read from LONG RAW as chunks of 32760 characters I can probably use UTL_FILE procedures to dump it to a OS file after a sequence of SUBSTR().

    Please let me know. In the mean time, have a great day !

    Thanks
    ad

  8. #8
    Join Date
    Jun 2009
    Posts
    1
    [QUOTE=ad;21979]Hi Goodhealth,

    Thanks for the code snippet. It works... But one small issue... My long raw column contains data length > 32760 chars. In PL/SQL varchar2 variable I am able to declare MAXimum of 32760 chars. Can you give me a hint on how can we read the complete column ( I think we have data upto 1 GB ) for each row.

    Is there a way to read LONG RAW column as chunks of 32760 characters ? If i can do that, then I can use UTL_FILE ( again imposes a limit of 1024 chars for each write) But if I can read from LONG RAW as chunks of 32760 characters I can probably use UTL_FILE procedures to dump it to a OS file after a sequence of SUBSTR().

    Please let me know. In the mean time, have a great day !

    Thanks
    ad[/QUOTE]

    Use substr to loop through in smaller chunks if the column is more than 32767. See the code below. It does not have looping logic. Needs to be added. Also, you need to concatenate and write to a file.

    set serveroutput on size 256000000
    declare
    a varchar(32767);
    cursor c1 is select idt_rec from ids_upd_sync_nsa;
    begin
    for r2 in c1 loop
    a:=UTL_RAW.CAST_TO_VARCHAR2(substr(r2.idt_rec,2000));
    dbms_output.put_line(a);
    dbms_output.put_line(r2.idt_rec);
    dbms_output.put_line(length(a));
    end loop;
    end;
    /

  9. #9
    Join Date
    Oct 2000
    Posts
    250
    Hi,
    Does anybody know, can the LONG RAW convert to VARCHAR2 works in developer Report 6.0 ? Please feedback as soon as possible. Thanks

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    8 years old, 8 years - why oh why oh why

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