is there such a thing as a reference cursor with parameters?
Date: Mon, 21 Sep 2009 08:07:40 -0700 (PDT)
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
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
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