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

From: Martin <martin.j.evans_at_gmail.com>
Date: Mon, 21 Sep 2009 08:07:40 -0700 (PDT)
Message-ID: <1da5ba6b-1182-4891-a001-b029da8754d4_at_o13g2000vbl.googlegroups.com>

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 Received on Mon Sep 21 2009 - 10:07:40 CDT