Re: Q: How to use bind variables in stored procedures, packages, etc.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 22 Dec 1998 02:53:28 GMT
Message-ID: <368008e9.2169749@192.86.155.100>

A copy of this was sent to ptsurmr_at_my-dejanews.com (if that email address didn't require changing) On Mon, 21 Dec 1998 22:46:42 GMT, you wrote:

>Quick (and hopefully simple) question:
>
>How do you declare/use bind variables in stored (server side) plsql
>packages, functions, procedures, etc.?
>
>In sqlplus I can do something like (trivial example):
>
>variable n number
>begin
> select max(key) into :n from xxx;
> dbms_output.put_line('Number = ' || :n);
>end;
>/
>
>where table xxx created as...
>create table xxx (
>key number primary key);
>
>insert into xxx (key) values (1);
>insert into xxx (key) values (2);
>insert into xxx (key) values (3);
>
>How to do the same with a procedure (function, package, etc)?
>

it is really easy in pl/sql -- anytime you reference a pl/sql variable in a sql statement -- pl/sql will turn it into a bind variable. Consider for example:

SQL> alter session set sql_trace=true;

Session altered.

SQL> declare
  2 y date;
  3 begin
  4 insert into t values ( y );
  5 end;
  6 /

PL/SQL procedure successfully completed.

If you look at the tkprof report for this, we see:

declare

y date;
begin

insert into t values ( y );
end;

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          1           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          1           1

********************************************************************************

INSERT INTO T
VALUES
( :b1 )

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          1           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          1           1

-----------------------------------------------------------------

so, pl/sql rewrites your sql, changing pl/sql variable references into bind variables for you. bind variables in pl/sql are simply any pl/sql variable...

>Thank you,
>John
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

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 Mon Dec 21 1998 - 20:53:28 CST