oracleplsqloracle11gmergeora-01461

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


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 :)


Solution

  • 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.