ORA-01461 (с> 4k varchar2) ошибка Только в операторе слияния. Вставить или обновить работает отлично

Question

Вот моя подсказка... Я на оракуле 11g. Много искал, но ничего не нашел.

Мне нужно выполнить операции DML, которые могут содержать данные> 4k символов.

Если я использую блок SQL, непосредственно в Oracle, как и следующий, все работает нормально

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;

НО!!! если я использую оператор слияния, это дает мне ошибку 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; 

Если бы какой-то намек был бы оценен:)

Answer 1

Использовать этот:

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

Выбор длинной переменной из двойной неявно приводит ее к SQL varchar2 который до 12c содержит до 4000 байтов.

Answer 2

Вы не можете выбрать varchar2 более 4k https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm

увидеть твой код

 select  txtV C0 from dual

но в oracle 12c вы можете https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm

Answer 3

@ Уильям отличный намек, это вы опубликовали работы.

Но я совершенно уверен, что я протестировал очень похожий синтаксис, в котором единственное отличие заключалось в утверждении select: тот, который вы предоставили, работает нормально... следующий вызов вызовет ошибку:

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

@ Уильям... хорошо, может быть, я немного запутался в написании сценариев. Я был удивлен "pk error", потому что, по-моему, я сделал скрипт следующим образом. Я намеревался вообще не использовать оператор "select", просто передавая код внутри вставки и обновления (как показано ниже), потому что я строю запрос программно и заменяю значения заполнителями.... Таким образом, нет ошибки pk совсем. В ваших примерах, конечно, причина в вставке использовался код из запроса, но в обновлении использовалось значение "TESTCODE" ... поэтому он искал (и обновлял) код, но вставлял другой:P Извините за виноват:)

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;