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

From: ddf <oratune_at_msn.com>
Date: Mon, 21 Sep 2009 09:48:06 -0700 (PDT)
Message-ID: <c2018912-4111-4c84-ba86-e36823b9fbea_at_y20g2000vbk.googlegroups.com>

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  narweegy45_at_sample45.com

SQL> David Fitzjarrell Received on Mon Sep 21 2009 - 11:48:06 CDT