How to read LONG RAW column into VARCHAR2?

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

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.

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.

Set long 10000000;
spool filename;
select column_name
from table;

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

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

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;

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

Hi,
Does anybody know, can the LONG RAW convert to VARCHAR2 works in developer Report 6.0 ? Please feedback as soon as possible. Thanks

Quote:

---

Originally Posted by ad

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

---

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;
/

8 years old, 8 years - why oh why oh why

Ten years later and 128,845 views, thank you drvenkat.

drvenkat created an account for the unique purpose of posting his single reply.

And so, for that matter, did I.

This is funny, reviving old necro thread for 20 years in a row. Let's keep going lol