Переменные Oracle Bind и Dynamic PL/SQL с пакетом
Я новичок в использовании динамического SQL в пакетах и столкнулся с проблемой при использовании переменной связывания в левой части предложения WHERE во фрагменте кода ниже, в частности, "WHERE TRUNC(:parm_rec.SRC_DATE_COLUMN)":
'WHERE TRUNC( :parm_rec.SRC_DATE_COLUMN ) < ADD_MONTHS( ' ||
'ADD_MONTHS ( TRUNC ( NVL ( TO_DATE ( :parm_rec.SYS_OFFSET ) , SYSDATE) - ( :parm_rec.DAY_OFFSET )), ' ||
Я пытаюсь передать имя столбца в качестве переменной связывания для даты создания, для которой имя столбца может варьироваться в зависимости от CREATEDATE, CREATE_DATE, LOAD_DATE и т. Д.
Динамический SQL выполняется без проблем, если предложение WHERE имеет фактическое имя столбца, жестко запрограммированное (в данном конкретном случае это CREATEDATE), и все другие переменные связывания, по-видимому, связываются с их соответствующими идентификаторами. Тип данных для parm_rec.SRC_DATE_COLUMN переменная является VARCHAR2.
Когда выполнение не удается, я не получаю конкретного сообщения об ошибке. Только строка, в которой произошел сбой кода:
EXECUTE IMMEDIATE arc_sql USING seq_val, parm_rec.SRC_DATE_COLUMN, parm_rec.SYS_OFFSET, parm_rec.DAY_OFFSET, parm_rec.MON_OFFSET, kNEGATIVE, parm_rec.YR_OFFSET, kANNUM;
Я исчерпал свои возможности исследования, пытаясь найти причину, по которой это не работает. Я предполагаю, что это что-то, что я делаю неправильно, но я не смог понять, что это такое.
Я включил всю ПРОЦЕДУРУ ниже:
PROCEDURE ArchiveDynamic
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ArchiveDynamic - ENTER');
FOR i in tbl_cur
LOOP
/*** DYNAMIC SQL DECLARATIONS ***/
arc_sql := 'DECLARE ' ||
/*** DYNAMIC %ROWTYPE SELECT ***/
'CURSOR arc_cur IS ' ||
'SELECT * '||
'FROM ' || i.ARC_TABLE_NAME || '; '|| --obtain ARCHIVE ARC_SCHEMA_NAME.ARC_TABLE_NAME
'TYPE arc_cur_type IS TABLE OF arc_cur%ROWTYPE; ' || -- dynamically set archive record cursor %ROWTYPE for BULK COLECT as table collection
'arc_rec arc_cur_type; ' || -- define archive record as TABLE OF cursor.%ROWTYPE
/*** ARCHIVE PARAMETERS CURSOR ***/
'CURSOR parm_cur IS '||
'SELECT :seq_val AS ARCHIVE_ID, '||
'A.*, ' ||
'SYSDATE AS ARCHIVE_DATE ' ||
'FROM ' || srcSchemaTable || ' A ' || -- archive SRC_SCHEMA_NAME.SRC_TABLE_NAME (source table not archive table)
'WHERE TRUNC( :parm_rec.SRC_DATE_COLUMN ) < ADD_MONTHS( ' ||
'ADD_MONTHS ( TRUNC ( NVL ( TO_DATE ( :parm_rec.SYS_OFFSET ) , SYSDATE) - ( :parm_rec.DAY_OFFSET )), ' ||
'( :parm_rec.MON_OFFSET * :kNEGATIVE ) ), ' ||
'( :parm_rec.YR_OFFSET * ( :kANNUM * :kNEGATIVE ) ) ); ' ||
/*** DYNAMIC SQL STATEMENT BODY ***/
'BEGIN '||
'IF parm_cur%ISOPEN THEN CLOSE parm_cur; ' ||
'END IF; ' ||
'OPEN parm_cur; ' ||
'LOOP ' ||
'FETCH parm_cur ' ||
'BULK COLLECT INTO arc_rec LIMIT 500; ' ||
'EXIT WHEN arc_rec.COUNT = 0; ' ||
'FORALL i IN 1..arc_rec.COUNT ' ||
'INSERT INTO ' || arcTable ||
' VALUES arc_rec( i );' ||
'DBMS_OUTPUT.PUT_LINE( ''ARC_REC_COUNT: '' || arc_rec.COUNT ); ' ||
'END LOOP; ' ||
'CLOSE parm_cur; ' ||
'dbms_output.put_line(''SUCCESS...''); '||
'END; ';
DBMS_OUTPUT.PUT_LINE('ArchiveDynamic - INSIDE LOOP: ' || arc_sql );
EXECUTE IMMEDIATE arc_sql USING seq_val, parm_rec.SRC_DATE_COLUMN, parm_rec.SYS_OFFSET, parm_rec.DAY_OFFSET, parm_rec.MON_OFFSET, kNEGATIVE, parm_rec.YR_OFFSET, kANNUM;
END LOOP;
END ArchiveDynamic
Любая помощь, которую вы можете предоставить, будет принята с благодарностью.
Спасибо!
1 ответ
Изменил следующий код, чтобы использовать буквальную конкатенацию, а не переменную связывания, как предложил Каушик Наяк:
'WHERE TRUNC( ' || parm_rec.SRC_DATE_COLUMN || ' ) < ADD_MONTHS( ' || 'ADD_MONTHS ( TRUNC ( NVL ( TO_DATE ( :parm_rec.SYS_OFFSET ) , SYSDATE) - ( :parm_rec.DAY_OFFSET )), ' || '( :parm_rec.MON_OFFSET * :kNEGATIVE ) ), ' || '( :parm_rec.YR_OFFSET * ( :kANNUM * :kNEGATIVE ) ) ); ' ||
Полный код ПРОЦЕДУРЫ здесь:
PROCEDURE ArchiveDynamic
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ArchiveDynamic - ENTER');
FOR i in tbl_cur
LOOP
/*** DYNAMIC SQL DECLARATIONS ***/
arc_sql := 'DECLARE ' ||
/*** DYNAMIC %ROWTYPE SELECT ***/
'CURSOR arc_cur IS ' ||
'SELECT * '||
'FROM ' || i.ARC_TABLE_NAME || '; '|| --obtain ARCHIVE ARC_SCHEMA_NAME.ARC_TABLE_NAME
'TYPE arc_cur_type IS TABLE OF arc_cur%ROWTYPE; ' || -- dynamically set archive record cursor %ROWTYPE for BULK COLECT as table collection
'arc_rec arc_cur_type; ' || -- define archive record as TABLE OF cursor.%ROWTYPE
/*** ARCHIVE PARAMETERS CURSOR ***/
'CURSOR parm_cur IS '||
'SELECT :seq_val AS ARCHIVE_ID, '||
'A.*, ' ||
'SYSDATE AS ARCHIVE_DATE ' ||
'FROM ' || srcSchemaTable || ' A ' || -- archive SRC_SCHEMA_NAME.SRC_TABLE_NAME (source table not archive table)
'WHERE TRUNC( ' || parm_rec.SRC_DATE_COLUMN || ' ) < ADD_MONTHS( ' ||
'ADD_MONTHS ( TRUNC ( NVL ( TO_DATE ( :parm_rec.SYS_OFFSET ) , SYSDATE) - ( :parm_rec.DAY_OFFSET )), ' ||
'( :parm_rec.MON_OFFSET * :kNEGATIVE ) ), ' ||
'( :parm_rec.YR_OFFSET * ( :kANNUM * :kNEGATIVE ) ) ); ' ||
/*** DYNAMIC SQL STATEMENT BODY ***/
'BEGIN '||
'IF parm_cur%ISOPEN THEN CLOSE parm_cur; ' ||
'END IF; ' ||
'OPEN parm_cur; ' ||
'LOOP ' ||
'FETCH parm_cur ' ||
'BULK COLLECT INTO arc_rec LIMIT 500; ' ||
'EXIT WHEN arc_rec.COUNT = 0; ' ||
'FORALL i IN 1..arc_rec.COUNT ' ||
'INSERT INTO ' || arcTable ||
' VALUES arc_rec( i );' ||
'DBMS_OUTPUT.PUT_LINE( ''ARC_REC_COUNT: '' || arc_rec.COUNT ); ' ||
'END LOOP; ' ||
'CLOSE parm_cur; ' ||
'dbms_output.put_line(''SUCCESS...''); '||
'END; ';
DBMS_OUTPUT.PUT_LINE('ArchiveDynamic - INSIDE LOOP: ' || arc_sql );
EXECUTE IMMEDIATE arc_sql USING seq_val, parm_rec.SYS_OFFSET, parm_rec.DAY_OFFSET, parm_rec.MON_OFFSET, kNEGATIVE, parm_rec.YR_OFFSET, kANNUM;
END LOOP;
END ArchiveDynamic
Спасибо за помощь!