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?

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

Gaurav Soni
– Gaurav Soni

2012-03-15 16:32:53 +00:00
Commented Mar 15, 2012 at 16:32 — Gaurav Soni
– Gaurav Soni, Commented Mar 15, 2012 at 16:32
Honestly, no. I think you should elaborate.

Adriano Carneiro
– Adriano Carneiro

2012-03-15 16:33:49 +00:00
Commented Mar 15, 2012 at 16:33 — Adriano Carneiro
– Adriano Carneiro, Commented Mar 15, 2012 at 16:33

Justin Cave · Accepted Answer · 2012-03-15 16:35:07Z

18

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.

answered Mar 15, 2012 at 16:35

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Gaurav Soni Over a year ago

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

Justin Cave Over a year ago

@ Gaurav Soni- What is your concern?

Gaurav Soni Over a year ago

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 Over a year ago

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

Justin Cave Over a year ago

@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.

|

Tony Andrews · Accepted Answer · 2012-03-15 16:35:14Z

6

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.

answered Mar 15, 2012 at 16:35

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Collectives™ on Stack Overflow

passing table and column name dynamically using bind variables

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related