ANBOB™

前不久在研究《Oracle国产化改造迁移时的问题：Number 数据类型中的 invalid number》时，发现 TO_* 函数在 12c 中有一些增强，同样对 LOB 类型也有增强。正巧最近遇到一个 11g 数据库需要通过 dblink 更新 BLOB 字段的需求，因此对此进行了研究，并做了简单记录。

关于LOB的笔记还能追溯到13年前,那时整理很简单

oracle 批量插入photo(图片)到数据库blob

BLOB select、insert、exp with sqlplus

gateway dblink transport mssql image datatype to oracle blob datatype

ORACLE 12C 对于LOB增强

Oracle 12.2通过扩展TO_BLOB和TO_CLOB函数，使其能够直接将BFILE转换为相应的LOB指针，从而使其变得更加容易。

1. TO_BLOB enhancement : loading file to BLOB

As easy as :

TO_BLOB(BFILENAME(directory_name, file_name))


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> insert into test5 values(1,to_blob(bfilename('DATAPUMP','tpt-oracle-master2024.zip')));
insert into test5 values(1,to_blob(bfilename('DATAPUMP','tpt-oracle-master2024.zip')))
                                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got FILE

SQL> declare
  l_bfile  bfile;
  l_blob   blob;

  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
  2  begin
  insert into test5 (id, photo)
  values (1, empty_blob())
  return photo into l_blob;

  l_bfile := bfilename('DATAPUMP', 'tpt-oracle-master2023.zip');
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
  -- loadfromfile deprecated.
  -- dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile));
   dbms_lob.loadblobfromfile (
   dest_lob    => l_blob,
   src_bfile   => l_bfile,
    amount      => dbms_lob.lobmaxsize,
    dest_offset => l_dest_offset,
   src_offset  => l_src_offset);
   dbms_lob.fileclose(l_bfile);
   commit;
   end;
/  

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(photo),id from test5;

DBMS_LOB.GETLENGTH(PHOTO)         ID
------------------------- ----------
                  1738825          1

SQL> ho ls -l /home/oracle/tpt-oracle-master2023.zip
-rw-r--r-- 1 oracle oinstall 1738825 Apr  9 15:36 /home/oracle/tpt-oracle-master2023.zip


Oracle 12.2 – TO_CLOB and TO_BLOB enhancements

# 19c
SQL> create table test5(id int,photo blob);
Table created.

SQL> insert into test5 values(1,to_blob(bfilename('DATAPUMP','tpt-oracle-master2024.zip')));
1 row created.

SQL> select dbms_lob.getlength(photo),id from test5;
DBMS_LOB.GETLENGTH(PHOTO)         ID
------------------------- ----------
                  2290839          1

SQL> host ls /home/oracle/tpt-oracle-master2024.zip
-rw-r--r--. 1 oracle oinstall 2290839 May 11 06:33 tpt-oracle-master2024.zip

2. TO_CLOB enhancement : converting BFILE/BLOB to CLOB

– File to CLOB :

TO_CLOB(BFILENAME(directory_name, file_name), charset_id)

to_clob和to_blob差不多，不再演示，只是增加了字符集，如下

select to_clob(bfilename('DATA_DIR', 'test.xml'), nls_charset_id('AL32UTF8'))  from dual;

3. DBMS_LOB enhancement : Writing CLOB to file

DBMS_LOB已经扩展为一个内置的CLOB2FILE过程，用于将CLOB内容写入文件，它取代了在以前的版本中可用DBMS_XSLPROCESSOR.CLOB2FILE，现在只是重定向到DBMS_LOB routine.

DBMS_LOB.CLOB2FILE(clob_value, directory_name, file_name [, charset_id, open_mode])

/**
 *       [...]
 *       openmode   -> (optional) mode to open the output file in.
 *                     wb -- write byte mode
 *                     ab -- append byte mode
 *                     default is wb
 */

procedure writeblob2file (
  p_directory in varchar2
, p_filename  in varchar2
, p_data      in blob
)
is

  l_file   utl_file.file_type;
  l_pos    integer := 1;
  l_amt    pls_integer := dbms_lob.getchunksize(p_data);
  l_buf    raw(32767);
  
begin

  l_file := utl_file.fopen(p_directory, p_filename, 'wb', 32767);
  loop
    begin
      dbms_lob.read(p_data, l_amt, l_pos, l_buf);
    exception
      when no_data_found then
        exit;
    end;
    utl_file.put_raw(l_file, l_buf);
    l_pos := l_pos + l_amt;
  end loop;
  utl_file.fclose(l_file);
  
end;

4.Distributed LOBs:  cross DB over database link

Oracle Database 12.2支持通过数据库链接对CLOB、BLOB和XMLTYPE数据类型进行一些操作。您可以在这里阅读有关此功能的更多信息
在12c前如11g, select 跨dblink 包含blob时会提示 ORA-22992: cannot use LOB locators selected from remote tables，但是 insert into xx select * from tblob@dblink和 update 可行，或者PLSQL

To update an existing BLOB do the following.
declare
  l_bfile  bfile;
  l_blob   blob;

  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
begin
  select blob_data
  into   l_blob
  from   tab1
  where  id = 1
  for update;

  l_bfile := bfilename('BLOB_DIR', 'MyImage.gif');
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
  dbms_lob.trim(l_blob, 0);
  -- loadfromfile deprecated.
  -- dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile));
  dbms_lob.loadblobfromfile (
    dest_lob    => l_blob,
    src_bfile   => l_bfile,
    amount      => dbms_lob.lobmaxsize,
    dest_offset => l_dest_offset,
    src_offset  => l_src_offset);
  dbms_lob.fileclose(l_bfile);
end;
/

create table anbob.t (
  c1 clob
);
create table anbob.t2 (
  c1 clob
);

create or replace trigger trig
after insert on anbob.t
for each row
begin
  insert into t2@DL_SELF 
    values ( :new.c1 );
end;
/

# 11g
SQL> create or replace trigger trig
after insert on anbob.t
for each row
begin
  insert into t2@DL_SELF
    values ( :new.c1 );
end;
/ 
Warning: Trigger created with compilation errors.

SQL> show error
Errors for TRIGGER TRIG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PL/SQL: SQL Statement ignored
3/15     PL/SQL: ORA-22992: cannot use LOB locators selected from remote
         tables

# 19c
create or replace trigger trig
after insert on anbob.t
for each row
begin
  insert into t2@DL_SELF
    values ( :new.c1 );
end;
 /
Trigger created.

SQL> insert into anbob.t values (to_clob('anbob'));
1 row created.

SQL> select * from anbob.t2;
C1
--------------------------------------------------------------------------------
anbob

不过测试出一个有意思的事情，用一套数据库，创建dblink指向自己，做update验证时，出现自己堵自己的分布式事务.

# 11g
SQL> create database link dl_self connect to anbob identified by anbob using '172.20.23.83/rac11g';
Database link created.

SQL> select sysdate from dual@dl_self;
SYSDATE
-------------------
2024-05-28 09:05:55


SQL> select id,dbms_lob.getlength(photo) from sys.test5@dl_self;
select id,dbms_lob.getlength(photo) from sys.test5@dl_self
                             *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

SQL> create synonym s_test5 for sys.test5@dl_self;
Synonym created.

SQL> select id from s_test5;

        ID
----------
         1

# sid 164
update test6 set photo=(select photo from s_test5 where s_test5.id=test6.id) where exists(select 1 from s_test5 where s_test5.id=test6.id);
-- waiting

# check wait
SQL> @ase

                                                                                   last_call
USERNAME          SID EVENT                MACHINE    MODULE               STATUS         et       SEQ# SQL_ID          WAI_SECINW ROW_WAIT_OBJ# SQLTEXT                        BS          CH# OSUSER     HEX
---------- ---------- -------------------- ---------- -------------------- ------- --------- ---------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
SYS               164 DFS lock handle      11g-node2  sqlplus              ACTIVE          5         58 48xgcb9vmp3yr   0:5                   -1  update test6 set photo=(selec 2:50          0 oracle       2000000
ANBOB              50 SQL*Net more data to 11g-node2  oracle               ACTIVE          5        241                 0:5               100765                                :               oracle

Note：
164是当前台会话，50是dblink 本地会话，BS 是v$session.blocking_session 可见自己堵塞了自己。 但是实际跨多个数据库是没有这现象。博文How to handle Oracle BLOB columns via Database Link记录在oracle 9i时支持使用动态SQL做BLOB更新，如

对于ORA-22992的错误

You can move LOBs across a database link as long as you are moving from table to table, not merely selecting. This avoids the use of temporary LOB locators, which is what the restriction is on.

简单的解决方法是将数据从远程服务器传输到本地服务器，然后查询本地表。可以CTAS，也可以创建GTT（global temporary table ）, INSERT INTO GTT SELECT  XX FROM  XX@DBLINK; 操作GTT (only  CLOB is < 4KB or BLOB < 2KB?  for 11G不确认)。

打赏

微信扫一扫，打赏作者吧～