Switching table and column name dynamically using bind variables
Is there a way to dynamically pass column and table names into a query using bind variables? This can be done using a simple concatenation operator ||
, but I need 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
how empno,ename
and table_name
howemp
But this approach doesn't work for me. Is it possible to have a different approach, different than the traditional concatenation approach?
source to share
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 a statement and then execute it many times with different bind variable values. If the optimizer does not know which table is being accessed or which columns are being fetched and filtered, it cannot generate a query plan.
If your problem is related to SQL injection attacks and it is assumed that dynamic SQL is really necessary (in most cases, the need to use dynamic SQL implies problems with the data model), you can use DBMS_ASSERT
to check that table names and column names do not contain embedded SQL.
source to share
No, you cannot. Changing the names of tables or columns in a query changes the semantics of that query - i.e. It becomes a different request.
Bound variables are passing different values into the same query . The optimizer can reuse a query with different values without the need for re-analysis and optimization.
source to share