0

I have two problems at the moment. 1- ORA-22992: cannot use LOB locators selected from remote tables Here is the script I am running:

CREATE OR REPLACE PROCEDURE merge_test(year in NUMBER)
IS
BEGIN
MERGE INTO SID_TEST SD
USING(
         SELECT t1.ID, TO_CHAR(t1.val) val, t.val2, TO_CHAR(t.val3) val3,  t.val4, 
                      TO_CHAR(t.val5) val5 FROM tab@dblink t, tab1@dblink t1
         WHERE t.id=t1.id and t.year=2010) R
ON (R.ID=SD.ID)
WHEN MATCHED THEN 
UPDATE SET SD.val = R.val,
           SD.val2 = R.val2,
           SD.val3 = R.val3,
           SD.val4 = R.val4,
           SD.val5 = R.val5

WHEN NOT MATCHED THEN
INSERT(SD.ID,SD.val,SD.val2,SD.val3,SD.val4,SD.val5)
VALUES(R.ID,R.val,R.val2,R.val3,R.val4,R.val5)
;

END;
/

val is a CLOB column and val3 and val5 arent BLOB column.

Belemnogo
  • 33
  • 1
  • 4
  • 2
    You can refer this answer - https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:950029833940 – Ankit Bajpai Jun 16 '21 at 15:19
  • well; 1. selecting two table joins via db link with blob/clob is not the most efficient querying method. 2. not sure about the blob; but clob you can select substr 1 to 32000 as one column and then the next as next column like a1, a2 and so on; then bring them to local db and merge/concatenate them and update. 3. Create type there and here and play this thing in plsql collection – Nizam - Madurai Jun 16 '21 at 20:10
  • I got issu to my problem. Now, i'm coming for other thing. With the same merge statement, i got ORA-00001: unique constraint violated ORA-06512 error. My goal is to recuperate that error and insert it into an error's table and continue to merge. Can i get help please? – Belemnogo Aug 04 '21 at 09:33

0 Answers0