If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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.

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.

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().

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.