ORACLE - Динамический Sql с использованием CLOB

Question

У меня есть таблица, как описано ниже

SQL> desc tab_script
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SRLNO                                              NUMBER
 INSERT_SCRIPT                                      CLOB

insert_script заполняется оператором вставки. Я хочу выполнить скрипт вставки динамически. Я написал следующий код, но не работает.

DECLARE
  ln_type_id NUMBER:= 1;
  lcl_sql    clob;
BEGIN
 FOR rec IN ( SELECT * FROM tab_script )
 LOOP
   lcl_sql:= rec.insert_script;
   EXECUTE IMMEDIATE lcl_sql ;
 END LOOP;
 COMMIT;
END;

Моя оракул версия Oracle Database 11g Enterprise Edition, выпуск 11.2.0.4.0 - 64-разрядная версия

Answer 1

1. Всегда рекомендуется печатать SQL-запрос, используя dbms_output.put_line и проверить SQL,

2. В случае, если у вас есть несколько insert заявления, то вы можете использовать BEGIN а также END

DECLARE
v_sql CLOB;
BEGIN
For i in (select * from tab_script)
LOOP
v_sql:= i.insert_script ;
-- Use below line ony if you have multiple insert statements seperated with semi-column
--v_sql := ' BEGIN ' || v_sql || ' END; ' ; 
 dbms_output.put_line('SQL STMNT - ' || v_sql); -- This is to check SQL 
execute immediate  v_sql ;
commit;
END LOOP;
EXCEPTION 
WHEN OTHERS 
THEN 
--dbms_output.put_line('' ); 
--Calling procedure to log/insert the exceptions, if any
LOG_PROC_ERRORS('Error with statement :  ' || v_sql ||
 ' ' || ' Error message : ' || ' ' || sqlerrm || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 
END;

Выход:

SQL STMNT - insert into emp(ID,NAME) values(7,'KLM');

Answer 2

Я полагаю, вы сохранили несколько вставок в столбце clob, верно? Но EXECUTE IMMEDIATE работает только с одной операцией за цикл:

CREATE TABLE TEST(ID INTEGER, NAME VARCHAR2(50));

BEGIN
   EXECUTE IMMEDIATE 'insert into test (id, name) values (1, ''first'')'; 
   EXECUTE IMMEDIATE 'insert into test (id, name) values (2, ''second'')'; 
--   EXECUTE IMMEDIATE 'insert into test (id, name) values (3, ''third'');insert into test (id, name) values (4, ''fourth'');'; 
   COMMIT;
END;
/

SELECT * FROM TEST;

DROP TABLE TEST;

В приведенном выше примере все работает отлично, и результат, как и ожидалось:

ID  NAME    
1   first
2   second

Но если вы раскомментируете третий EXECUTE-IMMEDIATE, выдается ошибка.

Если это ваша проблема, вам придется разделить свои INPUT-операторы в точке с запятой и выполнять каждое из них.