PL/SQL to insert history row with long raw column in Oracle

Question

I have a long raw column in an Oracle table. Insert with select is not working because of the long raw column which is part of my select statement as well. Basically I am trying to insert to insert history row with couple of parameters changed. Hence I was thinking of using PL/SQL in Oracle. I have no experience in PL/SQL neither I got anything after googling for couple of days. Can anyone help me with a sample PL/ SQL for my problem ? Thanks in advance !!!

Answer 1

LONG and LONG RAW datatypes are deprecated, and have been for many years now. You really are much better off getting away from them.

Having said that, if you're using PL/SQL, you will be limited to 32,760 bytes of data, which is the max that the LONG RAW PL/SQL datatype will hold. However, the LONG RAW database datatype, can hold up to 2GB of data. So, if any rows in your table contain data longer than 32,760 bytes, you will not be able to retrieve it using PL/SQL. This is a fundamental limitation of LONG and LONG RAW datatypes, and one of the reasons Oracle has deprecated their use.

In that case, the only options are Pro*C or OCI.

More information can be found here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#CJAEGDEB

Hope that helps.

Answer 2

You can work with a LONG RAW column directly in PL/SQL if your data is limited to 32kB:

FOR cc IN (SELECT col1, col2... col_raw FROM your_table) LOOP
   INSERT INTO your_other_table (col1, col2... col_raw) 
      VALUES (cc.col1, cc.col2... cc.col_raw);
END LOOP;

This will fail if any LONG RAW is larger than 32k.

In that case you will have to use another language. You could use java since it is included in the DB. I already answered a couple of questions on SO with LONG RAW and java:

• Copying data from LOB Column to Long Raw Column (will work with LONG RAW to LONG RAW too, just replace the UPDATE with an INSERT)
• Get the LENGTH of a LONG RAW

In any case as you have noticed it is a pain to work with this data type. If converting to LOB is not possible you will have to use a workaround.