For a better understanding of cursors and bind variables, check out
this paper by Bjorn Engsig: http://www.miracleas.dk/tools/Miracle_2_cursor.pdf
Jared
| Ross Collado <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 10/14/2003 02:49 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: My Proc may not be using bind vars. |
Hello,
I thought of giving this a go first before bouncing it to the gurus in the
list.
Basically, I wanted this procedure to be called from another 3rd party
application with the parameter of a 'customer_key'. I was under the
impression that I could force the select statement to use a 'bind' variable
from within the procedure. I then called this procedure to try it out (in
SQLPlus using exec by_key('customer_key') ) 30 times in succession using
different customer_key each time. I was expecting v$sqlarea to report stats
like parse=1 and executions=30. However, what I got were 30 of the "begin
by_key('zzz'); end;" with 1 parse and 1 execution each. (zzz had different
values every time). I'm still in the prototyping stage but the basic gist
of what I want is what I already have in the procedure.
Was what I got from v$sqlarea the expected behavior/stat result?
What can I change so that the sql statement will only parse once and execute
many times in other words make it more efficient?
thanks in advance.
Ross
create or replace procedure by_key (v_customer_key
sales_order_delivery.customer_key%type)
is
type addr_rec_type is record (
addr1 sales_order_delivery.address_line_1%type,
addr2 sales_order_delivery.address_line_2%type,
addr3 sales_order_delivery.address_line_3%type,
sub_dist sales_order_delivery.suburb_district%type,
pcode sales_order_delivery.postal_code%type);
addr_rec addr_rec_type;
type refcurtyp is ref cursor;
cv refcurtyp;
stmt varchar2(500) := 'select
address_line_1,address_line_2,address_line_3,suburb_district,postal_code
from '||
'sales_order_delivery where customer_key = :ckey';
begin
-- dbms_output.put_line('debug:'||stmt||'('||v_customer_key||')');
open cv for stmt using v_customer_key;
loop
fetch cv into addr_rec;
exit when cv%notfound;
--
dbms_output.put_line('debug::'||addr_rec.addr1||'x'||addr_rec.addr2||'x'||ad
dr_rec.addr3||'x'||
-- addr_rec.sub_dist||'x'||addr_rec.pcode);
end loop;
close cv;
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end by_key;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ross Collado
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
