Home » SQL & PL/SQL » SQL & PL/SQL » Using Bind with IN
Using Bind with IN [message #193084] Thu, 14 September 2006 19:40 Go to next message
sam2000
Messages: 5
Registered: September 2006
Junior Member
Which is better using the dynamic query with Execute Immediate or using PL/SQL variable binding?

INSERT INTO EMP_TEST (SELECT * FROM EMP WHERE empid IN (vEmpId);

EXECUTE IMMEDIATE 'INSERT INTO EMP_TEST (SELECT * FROM EMP WHERE empid IN (:1)' USING vEmpId;

Re: Using Bind with IN [message #193085 is a reply to message #193084] Thu, 14 September 2006 19:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
neither of your statements are valid.
so the performance of them are equivalent.
Why would you take the word of strangers as opposed to running benchmarks of your own where the results would be conclusive?
Re: Using Bind with IN [message #193106 is a reply to message #193084] Thu, 14 September 2006 22:25 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
as a general rule, only use dynamic sql when the statement, as opposed to the bind variable, is dynamic. use exec immed when the TABLE_NAME can change. when a variable in the where clause can change, there is no reason for dynamic sql.
Re: Using Bind with IN [message #194828 is a reply to message #193106] Mon, 25 September 2006 10:10 Go to previous messageGo to next message
sam2000
Messages: 5
Registered: September 2006
Junior Member
Shoblock thank you for your reply.

But I am still not clear about when to use the bind variables!!

I cannot use bind variables if the table_name changes in the SQL. I have to concatenate the PL/SQL varaible in the EXECUTE IMMEDIATE.

In the second case, if I have a Select or an Insert query in which the variable in the where clause changes I can again use the PL/SQL variable instead of using the bind variable.

For example:
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (' || vEmpId || '))';

You mentioned that dynamic sql should be used only when the statement is dynamic as opposed to the variable.
Is this a vald case for using the dynamic SQL?

PROCEDURE row_selected (
table_name IN VARCHAR2,
condition IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE
'SELECT * FROM ' || table_name || ' WHERE ' || condition;
END;

Re: Using Bind with IN [message #194830 is a reply to message #194828] Mon, 25 September 2006 10:57 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
"In the second case... the variable in the where clause changes I can again use the PL/SQL variable instead of using the bind variable."

your example:
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (' || vEmpId || '))';

you could, BUT DON'T. THAT IS BAD.
if vempid contains "5", then you would write:
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM EMP WHERE empid IN (:p1)' USING vEmpId;

assuming that vempid contains "1,2,3", then your example is valid (although there are ways to write the sql to still use bind variables and avoid building vempid into the dynamic sql). this is because you want to dynamically build an IN LIST which will be parsed as multiple arguments instead of a single argument that contains some commas.

the only reason you need to introduce dynamic sql in the above examples is because the table name for the insert is unknown until run time. if you were always inserting into table X, then you wouldn't need dynamic sql, and could write:
INSERT INTO X (SELECT * FROM EMP WHERE empid IN (vEmpId);

unless (as mentioned above) vempid is a string that contains multiple arguments to build an IN-LIST, each separated by commas and quoted as needed. but again, dynamic sql can still be avoided, by using INSTR (just one, simple alternative).

certain parts of the sql statement are allowed to be passed as bind variables (e.g., where col = VAR1; decode(VAR1,1,VAR2,VAR3); to_char(sysdate,VAR_FORMAT_MASK).

certain parts CANNOT be BIND VARIABLES because they must be defined before the sql statement is parsed. this includes the TABLE NAMES in the WHERE CLAUSE, the words ORDER BY (optional order by clauses can be done with decodes instead of using dynamic sql), and COLUMN NAMES (this can also be worked around by using decodes - decode(var1,1,COL1,2,COL2,3,COL3)=...). if you need to make one of these things dynamic, then use EXEC IMMED. if the only thing undefined when you write the code is the value of a variable, then you don't need dynamic sql. and even if you do use dynamic sql, you should use BIND VARIABLES whenever possible.

now, here's why you should use bind variables and not do it as your example shows.
given:
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (' || vEmpId || '))';

what if I passed vempid with the value:
1) or (1 = 1

then the final sql stmt would be (assuming table_name is X):
INSERT INTO X (SELECT * FROM EMP WHERE empid IN (1) or (1 = 1));

what happens now. I just hacked you system (I believe it's called SQL INJECTION). I just avoided all the other code you wrote and forced every emp record into X. clearly not what you intended. it's also a very simple method to steal data via poorly written web sites.

so, assuming you want to allow vempid to contain the string "1,2,3", then:

EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM EMP WHERE INSTR( :p1, ','||empid||',' ) <> 0'
USING ','||vEmpId||',';

this becomes:
INSERT INTO X (SELECT * FROM EMP
WHERE INSTR(',1,2,3,' , ','||empid||',' ) <> 0

I added extra commas to the beginning and and end of everything because otherwise the value of 4 would be found in the list "3,45".
Re: Using Bind with IN [message #195290 is a reply to message #194830] Wed, 27 September 2006 16:21 Go to previous messageGo to next message
sam2000
Messages: 5
Registered: September 2006
Junior Member
Got it!!! but I have a question though( after reading your response.)
You mentioned about the using the vEmpId variable if the passed string is "1,2,3"
..will be parsed as multiple arguments instead of a single argument that contains some commas.

EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (:num)' USING vEmpId;

When the above statement is executed and the passed in value for vEmpId is "1,2,3" won't the statement at run time be read as

EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (1,2,3)' ;

Why would it be parsed as multiple arguments??

[Updated on: Wed, 27 September 2006 16:22]

Report message to a moderator

Re: Using Bind with IN [message #195301 is a reply to message #195290] Wed, 27 September 2006 18:51 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No, that bind value will mean the statement would be executed as:

WHERE empid IN ('1,2,3')
Re: Using Bind with IN [message #195303 is a reply to message #193106] Wed, 27 September 2006 19:02 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
you know, I started off writing a reply to this, then I deleted it. Basically because I can't explain it anywhere near as well as Tom Kyte can. Go to http://AskTom.Oracle.com and do a search on Bind Variables (and I mean shoblock too, as you seem to have a general misunderstanding of what constitutes dynamic SQL) This will give you all the info on Bind variables that you will ever need (hopefully Wink )

Long story short - use bind variables when the where clause changes.
Jim
Re: Using Bind with IN [message #195321 is a reply to message #195290] Wed, 27 September 2006 23:15 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
no jim, I understand perfectly what dynamic sql is. it's sql that's built "dynamically" at run-time. get it - the word dynamic is pretty much the entire explanation. so what exactly did you find wrong with my statements? and maybe you should check out what a bind variable really is, since you seem to have no clue. do you realize that non-dynamic sql can use bind variables? that's what variables in pl/sql are - bind variables.

sam:
as Todd said, using a BIND variable will NOT make a comma delimited string into multiple arguments. I said (jim, maybe you should try paying attention as well) that if you have a string containing multiple arguments separated by commas, then you COULD (although I don't recommend it) build the variable string directly into the dynamic sql (NOT use a bind variable) in order to achieve the desired results.


jim, I just looked back over what I wrote. was this the line that confused you? "when a variable in the where clause can change, there is no reason for dynamic sql." it seems similar to you thoughts. care to explain yourself.
Re: Using Bind with IN [message #195420 is a reply to message #195321] Thu, 28 September 2006 09:01 Go to previous messageGo to next message
sam2000
Messages: 5
Registered: September 2006
Junior Member
I tried and executed

EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (:num)' USING vEmpId;

using "1,2,3" and as you and Todd mentioned it did not work and gave me the ORA-01722: Invalid Number exception.

And the same statement worked when only 2 was passed.

Does this mean that the bind arguments are always passed in as string and get implicitly converted?

[Updated on: Thu, 28 September 2006 09:12]

Report message to a moderator

Re: Using Bind with IN [message #195431 is a reply to message #195420] Thu, 28 September 2006 10:05 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
go back and READ what I already wrote:
Quote:

if vempid contains "5", then you would write:
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM EMP WHERE empid IN (:p1)' USING vEmpId;

assuming that vempid contains "1,2,3", then your example is valid (although there are ways to write the sql to still use bind variables and avoid building vempid into the dynamic sql). this is because you want to dynamically build an IN LIST which will be parsed as multiple arguments instead of a single argument that contains some commas.


the example with BIND VARIABLES (jim, pay attention as well) specifically stated that it would work if the variable VEMPID contained "5". I never said it would work for a variable containing "1,2,3".

I also said "assuming that vempid contains "1,2,3", then your example is valid". in case you forgot, your example did NOT use bind variables.

I also went on to say that while your example would work, and that bind variables would not work for a variable containing comma separated values, you still SHOULD NOT do it because of something called SQL INJECTION (making it very easy for someone to corrupt/steal your data).

my use of double quotes does not indicate that something is a string as opposed to a number, date or any other data type. I simply use the double quotes to indicate the beginning and end of something that I am specifying. in fact, since double quotes are not used to indicate strings in sql (or pl/sql), you should not have made that assumption.

bind variables are whatever datatype they are. the USING clause of EXECUTE IMMEDIATE indicates the bind variables. these variables must already be declared somewhere, and their declaration will specify their type. so:

procedure ins (table_name in varchar, vempid in NUMBER) is
begin
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (:p1) )' USING vEmpId;
end;

see, bind variable is a number, so the above BINDS a NUMERIC variable and works.

but:
procedure ins (table_name in varchar, vempid in VARCHAR2) is
begin
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (:p1) )' USING vEmpId;
end;

will fail if you pass the string "1,2,3" into the procedure (you already proved this to yourself).

and YOUR example (not mine, pay attention):
procedure ins (table_name in varchar, vempid in VARCHAR2) is
begin
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' (SELECT * FROM EMP WHERE empid IN (:vEmpId))';
end;

will work, BUT IT IS BAD CODE. DO NOT DO IT THAT WAY.

I also already gave a work around that uses DYNAMIC sql for the table name (see jim, that's the dynamic part), but uses BIND VARIABLES for the data (jim, bind variables do not dictate dynamic):
procedure ins (table_name in varchar, vempid in VARCHAR2) is
begin
EXECUTE IMMEDIATE 'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM EMP WHERE INSTR( :p1, ','||empid||',' ) <> 0'
USING ','||vEmpId||',';
end;
Re: Using Bind with IN [message #195448 is a reply to message #195431] Thu, 28 September 2006 13:57 Go to previous message
sam2000
Messages: 5
Registered: September 2006
Junior Member
Ohhh!! I am sorry for my last query

"Does this mean that the bind arguments are always passed in as string and get implicitly converted? ""

I thought I was dealing with the numbers ("1,2,3") rather than the string ("1,2,3").

Anyway, thank you for your explanation. The use of bind variables is now clear to me!!!
Previous Topic: Invalid objects across database links, called by Java app
Next Topic: Spooling my files so sql is not included in output file
Goto Forum:
  


Current Time: Sat Dec 10 10:35:26 CST 2016

Total time taken to generate the page: 0.09978 seconds