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.


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


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

2 answers

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.



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.



All Articles