Is there a way to pass column and table names dynamically to a query using bind variables? This could be done by using a simple concatenation operator ||
, but I would like a different approach by which this can be achieved.
EDIT
OPEN abc_cur FOR 'Select :column_name
from :table_name'
USING column_name,table_name;
In this example I am passing column_name
as empno,ename
and table_name
as emp
But this approach is not working for me. Is it possible to have a different approach other that the traditional approach of concatenation?
Table and column names cannot be passed as bind variables, no. The whole point of bind variables is that Oracle can generate a query plan once for the statement and then execute it many times with different bind variable values. If the optimizer doesn't know what table is being accessed or what columns are being selected and filtered on, it can't generate a query plan.
If your concern relates to SQL injection attacks, and assuming that dynamic SQL is actually necessary (most of the time, the need to resort to dynamic SQL implies problems with the data model), you can use the DBMS_ASSERT
package to validate that the table names and column names don't contain embedded SQL.