using bind variables

Hi,
I'm planning to change some of the code used in one project to use bind variables. But when I was looking at the code I got ssome doubts regarding the bind variables.
If we have a function or procedure some thing like this :

create or replace procedure TEST (p_date date)
as
v_date date;
begin

select input_date INTO v_date where beg_date = p_date;
end;

So , is the p_date (input variable) is considered as a bind variable ? If not how can I use bind variables in the above select statments.

Thanks.

Yes, P_DATE is a bind variable for the select statement. So nothing needs to be changed here.

Bind variables are good for DML type commands

...
mystr := 'UPDATE mytable SET col1 = :1 WHERE col1 = :2';
EXECUTE IMMEDIATE mystr USING 'A', 'B';
...

IMHO, In using a select into, your biggest worries are that you are not going to get a value, or that you get too many values thus causing a run time error. You should handle this through a function that has error handling and knows what to send back if there are either no results returned or too many results returned. This function should be used anywhere where you need that business logic.

But the SELECT INTO command that you specify is not going to improve by the use of bind variables.

Quote:

---

Originally posted by gandolf989
Bind variables are good for DML type commands

...
mystr := 'UPDATE mytable SET col1 = :1 WHERE col1 = :2';
EXECUTE IMMEDIATE mystr USING 'A', 'B';
...

But the SELECT INTO command that you specify is not going to improve by the use of bind variables.

---

The "bind varibles are good" mantra is totaly equaly applicable to DMLs as it is to selects! Absolutely no difference! None!

Quote:

---

Originally posted by jmodic
The "bind varibles are good" mantra is totaly equaly applicable to DMLs as it is to selects! Absolutely no difference! None!

---

I put my reply in at the same time you did. I didn't think of the example as a bind variable. I was under the assumption that bind variables were preceded with a colon and were buried in a text string to make the string look generic to Oracle. So I stand corrected on that issue.

Quote:

---

Originally posted by gandolf989
I put my reply in at the same time you did. I didn't think of the example as a bind variable. I was under the assumption that bind variables were preceded with a colon and were buried in a text string to make the string look generic to Oracle. So I stand corrected on that issue.

---

Just some additional information. If you write a static SQL statement like what was in the initial question, it will be compiled to use bind variables. If you look at the statement in the shared pool after it executes, you will see that the variable name was replaced with a bind variable placeholder, i.e. :b1.

Another reason that the static SQL approach is better than the initally suggest approach of using dynamic SQL (execute immediate) is scalability. Whenever you use execute immediate to do SQL, you effectively are opening a cursor, parsing the cursor, executing the SQL and then closing the cursor. All within the context of that single execute immediate statement. If you use static SQL in a database procedure, the PL/SQL engine will manage the cursor for you and will only parse it once per session. Subsequent calls to the procedure will only cause the cursor to be executed again. Excessive parsing can negatively impact your scalability.