将数据从LOB列复制到Long Raw列 [英] Copying data from LOB Column to Long Raw Column

问题描述

我正在寻找一个查询，该查询从具有Blob列的表中选择数据并更新具有LONG RAW列的表.看来Oracle仅支持最多4000个字符.有没有办法将完整数据从Blob复制到长原始数据.

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.

我正在使用以下查询

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 ;

但是DBMS_LOB.SUBSTR仅支持最多4000个字符.

but DBMS_LOB.SUBSTR supports only upto 4000 characters.

我们非常感谢您的帮助.

Any help is highly appreciated.

推荐答案

PL/SQL仅读取/写入LONG RAW的前32k，SQL会将列转换为RAW，因此仅处理前2000个字节

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.

您可以使用Java直接从数据库访问LONG RAW列，如问题.

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

java类:

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

您可以从PL/SQL调用此过程:

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

这篇关于将数据从LOB列复制到Long Raw列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！