Can a bind variable be concatenated to a dynamic SQL WHERE clause in order to add an AND statement?

Question

I am attempting to add an AND statement to a dynamic WHERE clause using a bind variable and I am receiving the following Oracle error:

ORA-01830: date format picture ends before converting entire input string bind variable

Here is the offending code:

FUNCTION WhereClause RETURN VARCHAR2
IS
BEGIN
    where_sql := 'WHERE TRUNC( ' || parm_rec.SRC_DATE_COLUMN || ' ) < ADD_MONTHS( ' ||
                                  'ADD_MONTHS ( TRUNC (  NVL ( :SYS_OFFSET, SYSDATE ) - ( :DAY_OFFSET )), ' ||
                                  '( :MON_OFFSET * :kNEGATIVE ) ), ' ||
                                  '( :YR_OFFSET * ( :kANNUM * :kNEGATIVE ) ) ) ';

    RETURN where_sql;
END WhereClause;



PROCEDURE ArchiveSrcDateFilter
IS     
BEGIN
DBMS_OUTPUT.PUT_LINE('ArchiveDynamic - ENTER');
IF parm_rec.SRC_DATE_COLUMN IS NULL THEN parm_rec.SRC_DATE_COLUMN := 'NULL';
END IF;
    FOR i in tbl_cur
    LOOP
    where_sql := WhereClause; -- defines the WHERE clause (where_sql) via function, Spec will not return variable to body?
    /*** 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 - SRC_CREATE_DATE IS NOT NULL***/
                        '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_sql || ' || :ADD_FILTER ' || ' ; ' ||

               /*** 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, parm_rec.ADD_FILTER;

    END LOOP;

END ArchiveSrcDateFilter;

This is the specific piece of code in the ArchiveSrcFilter procedure where the :ADD_FILTER bind variable is located (Note: I have attempted different concatenation iterations for the bind variable without success this was simply my last attempt before posting the issue here) :

'CURSOR parm_cur IS '||
'SELECT :seq_val AS ARCHIVE_ID, '||
'A.*, ' ||
'SYSDATE AS ARCHIVE_DATE ' ||
'FROM ' || srcSchemaTable || ' A ' ||
where_sql || ' || :ADD_FILTER ' || ' ; ' ||

And the EXECUTE IMMEDIATE USING with the last parameter as the bind:

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, parm_rec.ADD_FILTER;

the parm_rec.ADD_FILTER = AND STATUS = 1062

Is it possible to do what I am attempting by concatenating the bind to the where?

I don't understand the odd error message I am receiving given that the code executes without exception if I concatenate the parm_rec.ADD_FILTER object variable or hard code the AND STATUS = 1062.

I can concatenate the parm_rec.ADD_FILTER in place of the bind variable and the code executes without exception, but I have been unsuccessful in attempts to get the bind variable to work.

I'm grateful for any suggestions and/or insight.

Thanks!

Answer 1

No. Column names (i.e. STATUS) and operators (i.e. AND, =) can never be resolved from bind variables.