using bind variables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: using bind variables

  1. #1
    Join Date
    Feb 2001
    Posts
    34

    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, P_DATE is a bind variable for the select statement. So nothing needs to be changed here.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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.

  6. #6
    Join Date
    Apr 2001
    Posts
    118
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width