| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: dynamic-sql question
A copy of this was sent to Marc Eilens <eilensm_at_fh-trier.de>
(if that email address didn't require changing)
On Wed, 04 Nov 1998 10:39:45 +0100, you wrote:
>Hi,
>
>can anybody tell me when to use the bind_variable-procedure and when to
>use the concatenation-operator (double pipe ||).
>
>I tried the following which works,
>
>procedure dynamic(table_name IN VARCHAR2)
>...
>dbms_sql.parse(cursor_id, 'select a_column from ' || table_name ,
>dbms_sql.native);
>...
>
>But when I want to use the table-name in a where-clause, I have to use
>the bind_variable-procedure
>in order to get the procedure to work.
>
>procedure dynamic(table_name IN VARCHAR2)
>...
>dbms_sql.parse(cursor_id, 'select a_column from a_table where
>some_column = :x', dbms_sql.native);
>dbms_sql.bind_variable(cursor_id, ':x', table_name);
>
>This works fine, too.
>The question now is, why do I have to use the double-pipe in the
>procedure dynamic and why doesn't this work in procedure dynamic2?
>
Well, the || will *always* work. You might have to quote the variable you are putting into the query. For example, if SOME_COLUMN in A_TABLE was a character string, you would have had to of coded:
dbms_sql.parse( 'select a_column from a_table where some_column = ''' ||
table_name || '''' );
Lets say the value of table_name was "FOO" in the above, that would then generate the query:
select a_column from a_table where some_column = 'FOO'
It is *more efficient* to use the bind variable -- so whenever you can, use the bind variable. You see the query:
select a_column from a_table where some_column = :x
can be parsed and optimized once by the kernel and will be reused. If you put the values 'FOO' and 'BAR' directly in the query via concatenation -- then each would be a unique query and each would have to be parsed and optimized, reducing the value of the shared sql area.
As to why sometimes you cannot use bind variables, like when you replaced the table name, its because bind variables can ONLY be used where you could be a character string literal. For example, you cannot:
select * from 'EMP'
where emp is a constant string, you must rather:
select * from emp
If an IDENTIFIER (column name, table name, etc) is expected -- you cannot use a bind variable, bind variables can only be used where a constant literal could be.
>Please cc me as I don't always have the possibilty to read this
>newsgroup.
>
>Thanks a lot,
>Marc
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Nov 08 1998 - 13:40:55 CST
 |
 |