-
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;
goodhealth
-
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=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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|