Solving ORA-22992 in one line of SQL (and a global temporary table)

Right now, I’m working on an application that migrates a lot of data between two Oracle schemas. Until now, these schemas lived in the same database. To support migrations between several testing environments (living on different databases) we thought it would be a good idea to migrate the data over a database link (the most compelling reason to do so was to reduce configuration management work as we would have only one schema of source data and migration software to maintain). And so we did.

The code initially looked like this:

create table clob_table (
  id number
, name varchar2(100)
, contents clob
);

declare
  r_clob clob_table%rowtype;
begin
  r_clob.name := 'Testing';
  r_clob.contents := ''; -- fill it with a clob
  insert into clob_table values r_clob;
end;
/

Please note that we don’t fill the ID column, as it is filled by a trigger on clob_table. As soon as clob_table lives in another database, we encountered ORA-22992, “cannot use LOB locators selected from remote tables”. Damn, that might spoil the whole party!

The Oracle documentation states that you can only use LOBs across database links as long as it is the only column in the DML-statement, or you use inserts of the form “insert into … select … from …”.

Our first idea was to use a RETURNING clause on the statement and update the CLOB value afterwards with a separate UPDATE statement. Unfortunately, RETURNING is also not supported across database links (ORA-22816: unsupported feature with RETURNING clause). To get that working, we would need even more code to retrieve the ID (making the trigger useless). Because we also didn’t know how an INSERT plus an UPDATE across a database link might hurt performance (instead of just an INSERT), we decided to abandon this route.

While thinking about the other supported form (“insert into … select … from …”) we finally arrived at a solution we’re quite happy with. First we created a global temporary table:

create global temporary table tmp_clob_table as
  select *
  from clob_table
  where 1=0
;

Secondly, we modified our code to first insert into this temporary table, then insert into the remote table:

declare
  r_clob clob_table%rowtype;
begin
  r_clob.name := 'Testing';
  r_clob.contents := ''; -- fill it with a clob
  insert into tmp_clob_table values r_clob;
  insert into clob_table select * from tmp_clob_table;
end;
/

Right now, we are quite happy with this solution. It had almost no performance impact and it doesn’t clutter up the code.

Ratings:

VN:F [1.9.22_1171]

please wait...

Rating: 5.0/5 (1 vote cast)

Tags: blob, clob, database link, global temporary table, ora-22992, sql

• 

 Print This PDF Version