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

From: bohica <martin.j.evans_at_gmail.com>
Date: Tue, 22 Sep 2009 06:09:46 -0700 (PDT)
Message-ID: <d6fe70c2-e752-4387-8abc-e44c51afd4ef_at_v2g2000vbb.googlegroups.com>



On 22 Sep, 13:35, ddf <orat..._at_msn.com> wrote:
> 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

Thanks David. Your suggestion works fine so I guess I'll just have to try running them both to prove which works out better.

Martin Received on Tue Sep 22 2009 - 08:09:46 CDT

Original text of this message