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.

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;