Re: is there such a thing as a reference cursor with parameters?

From: ddf <oratune_at_msn.com>
Date: Tue, 22 Sep 2009 05:35:42 -0700 (PDT)
Message-ID: <2e129e81-48b7-495f-b9b6-efc48928f5ab_at_l34g2000vba.googlegroups.com>

On Sep 22, 4:22 am, bohica <martin.j.ev..._at_gmail.com> wrote:
> On 21 Sep, 17:48, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Sep 21, 10:07 am, Martin <martin.j.ev..._at_gmail.com> wrote:
>
> > > Hi,
>
> > > Oracle EM indicates I have a lot of duplicate SQL which would be
> > > better parameterized. The DB user I connect as has no read access to
> > > the tables but can call functions in a package which has definer
> > > rights. Most of these functions look like this:
>
> > >   FUNCTION f_xxx(param1 TYPE)
> > >      RETURN SYS_REFCURSOR AS
> > >   l_cursor SYS_REFCURSOR;
> > >   BEGIN
> > >      OPEN l_cursor FOR
> > >        SELECT column1 FROM table where column2 = param1;
> > >      RETURN l_cursor;
> > >   END;
>
> > > I am using Perl DBD::Oracle to call these functions and get the cursor
> > > back - DBD::Oracle supports reference cursors. However, every time I
> > > call f_xxx the oracle EM adds that a duplicate SQL because the
> > > parameter param1 is differerent each time.
>
> > > Is there anyway to define a cursor with parameters and return it as a
> > > reference cursor e.g.,
>
> > > cursor cur_xxx(param1 TYPE) is select column1 from table where column2
> > > = param1;
> > > open cur_xxx(parameter)
>
> > > now return this as a reference cursor. Can you have a reference cursor
> > > that takes parameters? Any other alternatives?
>
> > > Thanks
>
> > Sort of:
>
> > FUNCTION f_xxx(param1 TYPE)
> >       RETURN SYS_REFCURSOR AS
> >    l_cursor SYS_REFCURSOR;
> >    BEGIN
> >       OPEN l_cursor FOR
> >         'SELECT column1 FROM table where column2 = :b1' using param1;
> >       RETURN l_cursor;
> >    END;
> > /
>
> > That may reduce your unique SQL statements to one regardless of how
> > many times this function is called.  The syntax does work as expected:
>
> > SQL> create or replace FUNCTION getCUSTOMER (p_custid in number)
> >   2  RETURN SYS_REFCURSOR AS
> >   3          cur   SYS_REFCURSOR;
> >   4    BEGIN
> >   5
> >   6
> >   7     OPEN cur FOR 'SELECT * FROM CUSTOMER WHERE CUST_ID = :b1'
> > using p_custid;
> >   8
> >   9
> >  10     RETURN cur;
> >  11    END;
> >  12  /
>
> > Function created.
>
> > SQL>
> > SQL> show errors
> > No errors.
> > SQL>
> > SQL> variable mycur refcursor
> > SQL>
> > SQL> begin
> >   2          select getcustomer(45)
> >   3          into :mycur
> >   4          from dual;
> >   5
> >   6  end;
> >   7  /
>
> > PL/SQL procedure successfully completed.
>
> > SQL>
> > SQL> print mycur
>
> >    CUST_ID CUST_NAME    CUST_ADDR1             CUST_ADDR2
> > CUST_PST_C CUST_CNTCT         CUST_EMAIL
> > ---------- ------------ ---------------------- ---------------------
> > ---------- ------------------ --------------------------
> >         45 Sample45     45 Avenue 45           Suite 45
> > A45B       Narweegy Sping 45  narweeg..._at_sample45.com
>
> > SQL>
>
> > David Fitzjarrell
>
> This worked fine for me - thanks. However, it uses dynamic SQL. Am I
> replacing one issue Oracle EM raises (duplicate SQL) with another one,
> (dynamic SQL)?
>
> Martin- Hide quoted text -
>
> - Show quoted text -

You're actually doing better using the dynamic SQL with the bind variable as you have one query to soft parse over and over rather than hard parsing every 'static' query you generated with your original example. There is some overhead as the dynamic SQL statement is prepared at each execution, however it isn't hard parsing on literal values. I expect the dynamic string will be more scalable and an overall better performer.

David Fitzjarrell Received on Tue Sep 22 2009 - 07:35:42 CDT