Minimal Refcursors PL/SQL

From: Tim Smith <timasmith_at_hotmail.com>
Date: 27 Jul 2004 19:06:03 -0700
Message-ID: <a7234bb1.0407271806.1e1c800a@posting.google.com>

I have a stored procedure which returns several ref cursors. I have a few requirements which I am trying to achieve.

1. Ensure SQL is easy to validate by inspection
2. Execute minimum number of SQL
3. Ensure all refCursors are valid

Sample Code

PROCEDURE GetStuff (key varchar2, ctable1 OUT refCursor, ctable2 OUT refCursor,

                      ctable3 OUT refCursor)

IS
BEGIN
    OPEN ctable1 FOR
    select * from table1 where primarykey = key;

    OPEN ctable2 FOR
    select * from table2 where key = (

           select pkey from table1 where primarykey = key);

    OPEN ctable2 FOR
    select * from table3 where key in (

           select field from table2 where key = 
              (select pkey from table3 where primarykey = key));

EXCEPTION
   when no_data_found then
   null;
END GetStuff;
Problems

1. SQL is easy to validate by using nested queries (often exists) but is it efficient?
2. I dont know if there is something else I can set the refcursor to. It seems poor practise to do something like IF (missing(pkey)) THEN select * from table3 where rownum<1; END IF;I would need to do an extra select to get the pkey of course which may be wasteful
3. I tried wrapping IF statements around child tables but unless the refcursor is valid (i.e. executed I guess) the client code errors - 0 rows are fine but not executing the select causes issues.

Any ideas? Received on Tue Jul 27 2004 - 21:06:03 CDT