如何将CLOB数据从一个数据库传输到另一个具有DBLinks的远程ORACLE数据库 [英] how to transfer CLOB data from one database to another remote ORACLE database having DBLinks

问题描述

问题是，如何将具有DBLink的CLOB数据从一个源数据库传输到另一个Oracle数据库.

The problem is, how to transfer CLOB data from one source database to another Oracle database, having DBLinks.

Oracle无法使用DBLink传输CLOB数据，因此，除了将Oracle中的字段扩展为Varchar2 32.767个字符(Oracle 12的新功能)之外，我们还能提供什么样的解决方案.

Oracle cannot transfer CLOB data using DBLinks so what kind of solution we can have apart from: extending fields in Oracle to Varchar2 32.767 characters (new feature of Oracle 12).

推荐答案

我发布了一个Github项目，该项目通过dblink查询CLOB和BLOB. https://github.com/HowdPrescott/Lob_Over_DBLink

I released a Github project that queries CLOBs and BLOBs over a dblink. https://github.com/HowdPrescott/Lob_Over_DBLink

这是独立功能中的CLOB部分:

Here's the CLOB part in a stand alone function:

create or replace function dblink_clob(
    p_dblink    in varchar2
  , p_clob_col  in varchar2
  , p_rid       in urowid
)
return clob is
    /**  A function to fetch a CLOB column over a dblink  **/
    /**  Laurence Prescott 25-Aug-17  **/
    /**  select dblink_clob('some_dblink', 'some_clob_column', rowid)
           from some_table@some_dblink;
         Note: Does not work on tables that have a virtual column (eg. xmltype).
    **/

    c_chunk_size    constant pls_integer := 4000;
    v_chunk         varchar2(4000);
    v_remote_table  varchar2(128);
    v_clob          clob;
    v_pos           pls_integer := 1;
begin
    dbms_lob.createtemporary(v_clob, true, dbms_lob.call);
    execute immediate 'select object_name from user_objects@' ||p_dblink
                   || ' where data_object_id = dbms_rowid.rowid_object(:bv_rid) '
    into v_remote_table using cast (p_rid as rowid);
    loop
        execute immediate 
            'select dbms_lob.substr@' ||p_dblink|| '(' ||p_clob_col|| ', ' ||c_chunk_size
         || ', ' ||v_pos|| ') from ' ||v_remote_table|| '@' ||p_dblink|| ' where rowid = :rid '
        into v_chunk using p_rid;
        begin dbms_lob.append(v_clob, v_chunk);
        exception when others then
            if sqlcode = -6502 then exit; else raise; end if;
        end;
        if length(v_chunk) < c_chunk_size then exit; end if;
        v_pos := v_pos + c_chunk_size;
    end loop;
    return v_clob;
end dblink_clob;

我认为该示例很容易解释，但是这里有一些说明.
该函数依赖于您可以跨dblink在远程DB上调用函数/过程的事实-在本例中为 dbms_lob.substr().
首先，它通过使用其对象ID(已编码为 rowid )找到远程表名称.这样就省去了将远程表名作为另一个参数传递的问题.
还要注意，p_rid参数是 urowid ，因为它是远程数据库中的 rowid .这就是为什么需要强制转换的原因. 然后，将CLOB提取并重建为4000字节的块，这是PL/SQL中最大的varchar2大小.这些块是varchar2的，可以在dblink中传递.
if length(v_chunk)<在读取完最后一个CLOB之后，将满足c_chunk_size ... 子句(然后将不填充块"缓冲区).
如果CLOB的长度是4000的倍数，则需要 ORA-06502 的异常捕获，即使没有更多数据，也不能满足"if"子句. 您可以仅依靠此捕获并完全删除"if"子句.但是我进行了一些性能测试，发现在大多数情况下，最好保留它.

I think the example is fairly self explanatory, but here's a bit of a description.
The function relies on the fact that you can call functions/procedures on a remote DB across a dblink - in this case dbms_lob.substr().
First it finds the remote table name by using its object id (which is encoded into the rowid). That saves having to pass the remote table name in as another parameter.
Note also that the p_rid parameter is a urowid, as it's a rowid from the remote DB. That's why it needs to be cast.
Then the CLOB is extracted and rebuilt in 4000 byte chunks, which is the maximum varchar2 size in PL/SQL. These chunks are varchar2's and can be passed across the dblink.
The if length(v_chunk) < c_chunk_size ... clause is satisfied after the last of the CLOB has been read (then the "chunk" buffer won't be filled).
The exception catch for ORA-06502 is needed when the length of the CLOB is a multiple of 4000, then the "if" clause is not satisfied, even though there is no more data. You could just rely on this catch and remove the "if" clause altogether. But I did some performance testing and found that in most cases it's better to leave it in.

这篇关于如何将CLOB数据从一个数据库传输到另一个具有DBLinks的远程ORACLE数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！