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

On Sep 21, 10:07*am, Martin <martin.j.ev... (AT) gmail (DOT) com> wrote:
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 (DOT) com

SQL>


David Fitzjarrell

On Sep 21, 5:48*pm, ddf <orat... (AT) msn (DOT) com> wrote:
Thanks David, I'll give that a try today.

Martin

On 21 Sep, 17:48, ddf <orat... (AT) msn (DOT) com> wrote:
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

On Sep 22, 4:22*am, bohica <martin.j.ev... (AT) gmail (DOT) com> wrote:
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

On 22 Sep, 13:35, ddf <orat... (AT) msn (DOT) com> wrote:
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