passing table and column name dynamically using bind variables

Question

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?

Solution 1

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.

Solution 2

No you cannot. Changing the table or column names in a query changes the semantics of that query - i.e. it becomes a different query.

Bind variables are all about passing different values to the same query. The optimiser can reuse the query with different values without having to re-parse it and optimise it.

Comments

[Gaurav Soni]

@Adrian: Are you getting what i meant with the question?

[Adriano Carneiro]

Honestly, no. I think you should elaborate.

[Gaurav Soni]

I am concerned with dynamically create a sql query and then execute it .Is there any alternative for that ?

[Justin Cave]

@ Gaurav Soni- What is your concern?

[Gaurav Soni]

i can create my dynamic sql with concatenation of string like v_sql:=select '||column_name||' from dual where 1=1 and then another string like that ,how can we create this more efficiently ,i mean wats the other approach other than this

[Gaurav Soni]

:Is there any more optimised efficient approach other than this?

[Justin Cave]

@Gaurav Soni- Are you worried that string concatenation itself is slow? That you're doing excessive hard parses? That you're opening security holes for SQL injection attacks? Or something else? Most well-designed systems have no need to resort to dynamic SQL so I'm always suspicious that dynamic SQL is being used to cover up a poor design. If you actually need the queries to be dynamic, though, you need to assemble the string dynamically.

[Gaurav Soni]

THanks for the answer, i think you are the best explainator and tutor in this field.Appriciated