ORA-01461 (with > 4k varchar2) error Only in merge statement. Insert or update works fine

Question

Here is my clue... I'm on oracle 11g. Searched a lot, but nothing found.

I need to execute DML operations, which can contain data > 4k characters.

If i use a sql block, directly in oracle, like the next one, everything works fine

declare 
  txtV varchar2(32000);
BEGIN 
  txtV:= 'MORE THAN 4k CHARS, here only few for readability' ; 
  Update FD_FILTERDEF 
     set SQLFILTER = txtV 
   where id='blabla';  
END;

BUT!!! if i use merge statement, it gives me error ORA-01461

declare 
  txtV varchar2(32000);
BEGIN 
  txtV:= '' ; 
  MERGE INTO FD_FILTERDEF A 
        USING ( select  txtV C0 
                  from dual) ST 
           ON (A.CODE = 'bla bla') 
  WHEN MATCHED THEN 
    Update set A.SQLFILTER = st.C0  
  WHEN NOT MATCHED THEN 
    insert (CODE  ,SQLFILTER ) 
      values ('bla bla'  , ST.C0  );  
END; 

If have some hint would be appreciated :)

Answer 1

Use this:

create table fd_filterdef
( code varchar2(10) primary key
, sqlfilter clob );

declare
    txtv varchar2(32000);
begin
    txtv := rpad('select statement, really really long', 5000, ' etc');

    merge into fd_filterdef a
    using (select 'bla bla' as code from dual) st
    on (a.code = st.code)
    when matched then
        update set a.sqlfilter = txtv
    when not matched then
        insert (code, sqlfilter)
        values (st.code,txtv);
end;
/

select code, length(sqlfilter) from fd_filterdef;

CODE       LENGTH(SQLFILTER)
---------- -----------------
bla bla                 5000

Selecting your long variable from dual implicitly casts it to a SQL varchar2 which prior to 12c only holds up to 4000 bytes.

Answer 2

you can't select varchar2 more than 4k https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm

see your code

 select  txtV C0 from dual

but in oracle 12c you can https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020

Answer 3

@William great hint, this you posted works.

But i'm quite sure i have tested a syntax very similar, in which the only difference was in the select statement, The one you provided works fine... the following one will raise the error:

declare 
  txtV varchar2(32000);
BEGIN 
  txtV:= '5000 chars ....';
  MERGE INTO FD_FILTERDEF A 
        USING ( select  'not used' Code from dual) ST 
           ON (A.CODE = 'TESTCODE') 
  WHEN MATCHED THEN 
    Update set A.SQLFILTER = txtV   --<<<< LOOK HERE I USE DIRECTLY THE VARIABLE DELCARED, NOT THE ONE FROM SELECT STMT 
  WHEN NOT MATCHED THEN 
    insert (CODE  ,SQLFILTER ) 
      values (st.code  , txtV  );  
END;

Answer 4

@William ... ok, maybe i've made a bit of confusion in writing down the scripts. I was surprised about the "pk error" cause in my mind i have the script done as follows. I intended to no use at all the "select" statement, just passing the code inside insert and update (as follows), cause i build the query programmatically and replace values with placeholders.... In this way, there is no pk error at all. In you examples, of course, cause in insert was used the code from the query, but in update was used the value "TESTCODE" ... so it was searching (and updating) for a code, but inserting another :P Sorry for my mistake :)

declare 
  txtV varchar2(32000);
BEGIN 
  txtV:= '5000 chars ....';
  MERGE INTO FD_FILTERDEF A 
        USING ( select  'not used' Code from dual) ST 
           ON (A.CODE = 'TESTCODE') 
  WHEN MATCHED THEN 
    Update set A.SQLFILTER = txtV   --<<<< LOOK HERE I USE DIRECTLY THE VARIABLE DELCARED, NOT THE ONE FROM SELECT STMT 
  WHEN NOT MATCHED THEN 
    insert (CODE  ,SQLFILTER ) 
      values ('TESTCODE'  , txtV  );  
END;