Invalid table name error while using Execute Immediate statement with bind variables

Question

I'm trying to get this dynamic SQL running ( using EXECUTE IMMEDIATE)

M_SQL_STATEMENT := 'SELECT MAX(:m_var1)+1 from :m_var2 RETURNING MAX(:m_var1)+1 INTO :m_var3';

EXECUTE IMMEDIATE M_SQL_STATEMENT
   USING M_COLUMN_NAME, UPPER(P_TABLE_NAME), M_COLUMN_NAME
   RETURNING INTO M_SEQ_NUMBER;

However, when trying to run this, I keep running into

ORA-00903: Invalid table

P_TABLE_NAME is a table name which is accepted as an input. I have confirmed that the table name & the column name are valid. I can't figure out why Oracle is throwing the error.

FWIW Altering the SQL statement to

M_SQL_STATEMENT := 'SELECT MAX(:m_var1)+1 SEQ from :m_var2 RETURNING SEQ INTO :m_var3';

still results in the same error.

Standoff

Related: Why cannot I use bind variables in DDL/SCL statements in dynamic SQL? – Sherronsherry

Answer 1

You need to put the table name and column name into the dynamic SQL, so something like

M_SQL_STATEMENT := 'SELECT MAX(' || M_COLUMN_NAME || ')+1 from ' 
|| P_TABLE_NAME';

EXECUTE IMMEDIATE M_SQL_STATEMENT INTO M_SEQ_NUMBER;

Regatta

Isn't it possible to use/pass table & column names as bind variables? – Standoff

No it's not, that's kind of the whole reason to use dynamic SQL in the first place :-) – Regatta

Thanks, @GregReynolds for some reason I thought that was possible – Standoff

BTW, @GregReynolds - got this working. Couple of mistakes though, - the SQL should be 'SELECT MAX(' || M_COLUMN_NAME || ')+1 from ' || P_TABLE_NAME; and the execute immediate should not have "returning" - like so --> EXECUTE IMMEDIATE M_SQL_STATEMENT INTO M_SEQ_NUMBER; – Standoff

Great news. I will change the answer – Regatta