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 02:22:26 -0700 (PDT)
Message-ID: <7c374150-8dff-41de-9696-796de456a3e7_at_j19g2000vbp.googlegroups.com>
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
Date: Tue, 22 Sep 2009 02:22:26 -0700 (PDT)
Message-ID: <7c374150-8dff-41de-9696-796de456a3e7_at_j19g2000vbp.googlegroups.com>
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 Received on Tue Sep 22 2009 - 04:22:26 CDT