Oracle Bind Variables and Dynamic PL/SQL with Package

Question

I am new to using dynamic SQL in packages and I have run into an issue when using a bind variable on the left-hand side of the WHERE clause in the code snippet below, specifically the '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 )), ' ||

I am attempting to pass the COLUMN NAME as a bind varibale for the create date, for which the column name can vary between CREATEDATE, CREATE_DATE, LOAD_DATE, etc.

The dynamic SQL executes without issue if the WHERE clause has the actual column name hard coded (in this specific case it is CREATEDATE), and all other bind variables appear to bind to their appropriate identifiers. The data type for the parm_rec.SRC_DATE_COLUMN variable is a VARCHAR2.

When the execution fails I do not get a specific error message. Only the line where the code failed which is the following:

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;

I've exhausted my research options trying to find a reason why this does not work. I'm assuming it is something that I am doing wrong, but I have not been able to figure out what it is.

I have included the entire PROCEDURE below:

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

Any help that you can provide would be greatly appreciated.

Thanks!

Dynamic does not mean you have to wrap all your code in quotes and execute at once.That just makes it much harder to debug the error messages, because you won't know them until run time. Write a simple static procedure first and then try to integrate the dynamic variables at the end. It may require you to spend some time initially, but will save a lot of laborious troubleshooting work for you and your successors in the company. — Kaushik Nayak, Feb 12 '18 at 15:40
Thanks for your response Kaushik Nayak. I have made certain that this sql works. The only issue I have encountered was adding the last bind variable - parm_rec.SRC_DATE_COLUMN. I know that this variable is the issue. I can replace it with the hard-coded column name and the procedure will execute without error. This is why I was curious if it is an issue with using bind variabels on the left side of the WHERE or perhaps a datatype issue? Thank you for your suggestion. — TheSchnitz, Feb 12 '18 at 15:47
You cannot use bind variables on schema objects like table_name, column_name etc. — Kaushik Nayak, Feb 12 '18 at 15:52
Hmmm, okay, so I am passing the column name as a VARCHAR2 into the WHERE. I think you are sying this does not work becuase the column name is a schema object and not simply a string, is that correct? — TheSchnitz, Feb 12 '18 at 16:04
Okay cahnged it and of course that works. Perhaps this question is out of scope, but is sql injection even an issue for the concatenated variable on the "left-side" of the operater in the WHERE clause? I would think not, but I thought I'd pose the question. Thank you. — TheSchnitz, Feb 12 '18 at 18:15

score 0 · Answer 1 · answered Feb 12 '18 at 18:38

Altered the following code to use a literal concatenation rather than a bind variable as Kaushik Nayak suggested:

'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 ) ) ); ' ||

Full PROCEDURE code here:

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

Thank you for your help!

Oracle Bind Variables and Dynamic PL/SQL with Package

1 Answers1