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