Oracle-L: My Proc may not be using bind vars.

From: Ross Collado <Ross.Collado_at_techpac.com>
Date: Tue, 14 Oct 2003 01:49:24 -0800
Message-ID: <F001.005D2F81.20031014014924@fatcity.com>

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: Ross.Collado_at_techpac.com

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: ListGuru_at_fatcity.com (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). Received on Tue Oct 14 2003 - 04:49:24 CDT