Copying data from LOB Column to Long Raw Column

I was looking for a query which picks data from a table having Blob column and update a table having LONG RAW column. It seems Oracle supports only up to 4000 characters. Is there a way to copy full data from blob to long raw.

I was using the follwing query

insert into APPDBA.QA_SOFTWARE_DUMMY 
     select SOFTWARE_ID, UPDATED_BY, CREATE_CHANGE_DATE, FILE_NAME,
            DBMS_LOB.SUBSTR(SOFTWARE_FILE, 4000) SOFTWARE_FILE, SOFTWARE_TYPE 
       from APPDBA.QA_SOFTWARE_DUMMY_TEST ;

but DBMS_LOB.SUBSTR supports only upto 4000 characters.

Any help is highly appreciated.

回答1:

PL/SQL will only read/write the first 32k of a LONG RAW and SQL will convert the column as a RAW so will only deal with the first 2000 bytes.

You can use java to access LONG RAW columns directly from the DB, as demonstrated in the question "Get the LENGTH of a LONG RAW".

Here's a little example, first the setup:

SQL> CREATE TABLE t (ID NUMBER PRIMARY key, source BLOB, destination LONG RAW);

Table created

SQL> DECLARE
  2     l_lob BLOB;
  3  BEGIN
  4     INSERT INTO t VALUES (1, 'FF', '') RETURNING SOURCE INTO l_lob;
  5     FOR i IN 1..10 LOOP
  6        dbms_lob.writeappend(l_lob, 4000,
  7                             utl_raw.overlay('FF', 'FF', 1, 4000, 'FF'));
  8     END LOOP;
  9  END;
 10  /

PL/SQL procedure successfully completed

The java class:

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Raw" AS
  2  import java.io.*;
  3  import java.sql.*;
  4  import oracle.jdbc.driver.*;
  5  
  6  public class Raw {
  7  
  8     public static void updateRaw(int pk) throws SQLException,IOException {
  9  
 10        Connection conn = new OracleDriver().defaultConnection();
 11  
 12        PreparedStatement ps = conn.prepareStatement
 13           ( "SELECT dbms_lob.getlength(source) length, source "
 14           + "FROM t WHERE id = ? FOR UPDATE");
 15        ps.setInt( 1, pk);
 16        ResultSet rs = ps.executeQuery();
 17  
 18        rs.next();
 19        int len = rs.getInt(1);
 20        InputStream source = rs.getBinaryStream(2);
 21        byte[] destArray = new byte[len];
 22        int byteRead = source.read(destArray);
 23        ps = conn.prepareStatement(
 24           "UPDATE t SET destination = ? WHERE id = ?");
 25        ((OraclePreparedStatement) ps).setRAW(1,
 26                                             new oracle.sql.RAW(destArray));
 27        ps.setInt(2, pk);
 28        ps.execute();
 29     }
 30  }
 31  /

Java created

You can call this procedure from PL/SQL:

SQL> CREATE OR REPLACE
  2  PROCEDURE update_raw(p_id NUMBER)
  3  AS LANGUAGE JAVA NAME 'Raw.updateRaw(int)';
  4  /

Procedure created

SQL> exec update_raw(1);

PL/SQL procedure successfully completed

回答2:

Despite the fact that you make a reversal (normaly you should move from LONG to LOB, LONG being obsolete)...

You must use dbms_lob package, and make some plsql:

Eventualy you can use read, getlength...

Doc you can find here Psoug.org or on Oracle doc