PL SQL Execute Immediate not working when calling it from a stored procedure

Question

I have created the following SP

create or replace PROCEDURE RM_SUPPORT_SCRIPTS_V2 
(
              V_TABLENAME IN USER_TABLES.table_name%type
            , V_SETCOLNAME IN NVARCHAR2 
            , V_SETVALUE IN NVARCHAR2 
            , V_WHERECOLNAME IN NVARCHAR2 
            , V_WHEREKEYVALUE IN NVARCHAR2 
            , USER_ID IN NVARCHAR2 
    ) 
    IS 
    BEGIN
      EXECUTE IMMEDIATE 
        'UPDATE '||DBMS_ASSERT.simple_sql_name(V_TABLENAME) 
            ||' SET :1 = :2, update_by_userid = :3, UPDATE_DATE = SYSDATE'
            ||' WHERE :4 = :5 '
            using V_SETCOLNAME, V_SETVALUE, USER_ID, V_WHERECOLNAME, V_WHEREKEYVALUE; 
    
END RM_SUPPORT_SCRIPTS_V2;

But when I call it from an EXECUTE statement

EXEC RM_SUPPORT_SCRIPTS_V2 (USERS_TABLE, USER_ID,ORTEGALUX,USER_ID,TESTED,FX);

I get the following error

Error report -
ORA-06550: line 1, column 31:
PLS-00357: Table,View Or Sequence reference 'USERS_TABLE' not allowed in this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Does anyone know why this could be happening? I have tried putting simple comma in the call of each table, column but continue failing.

Thank you.

Alex Poole Alex Poole · Accepted Answer · 2020-07-15T20:22:39

The error is coming from the call, not the procedure. You're passing strings so you need to quote them:

EXEC RM_SUPPORT_SCRIPTS_V2 ('USERS_TABLE', 'USER_ID','ORTEGALUX','USER_ID','TESTED','FX');

But you can't use bind variables for column names, you need to concatenate those in, as you are with the table name.

      EXECUTE IMMEDIATE 
        'UPDATE '||DBMS_ASSERT.simple_sql_name(V_TABLENAME) 
            ||' SET ' || V_SETCOLNAME || ' = :1, update_by_userid = :2, UPDATE_DATE = SYSDATE'
            ||' WHERE ' || V_WHERECOLNAME || ' = :3 '
            using V_SETVALUE, USER_ID, V_WHEREKEYVALUE;

though you may want to add assertion checks for those too.

PL SQL Execute Immediate not working when calling it from a stored procedure

1 Answers