[Keith Fetterman]

In MyEclipse 4.1.1, I see that I can run stored precedures in an Oracle DB via the Database Browser. I have stored procedures that use “ref cursors” as parameters. Are these supported? If so, how do I make them work?

Thanks,
Keith

[Riyad Kalla]

Keith,
I’ll check on this for you.

[Haris Peco]

Keith,

Can you send us example, please ?

Thanks

[Keith Fetterman]

Here is an example of an Oracle Ref Cursor.

In the code section below, I have created an example stored procedure that uses a ref cursor in
the argument list.

CREATE OR REPLACE PACKAGE reports AS

  TYPE ref_cursor_type IS REF CURSOR;

  PROCEDURE get_sales_order_summary(in_out_cur IN OUT REF_CURSOR_TYPE,
                                    from_date IN DATE,
                                    to_date IN DATE);

END reports;
/

CREATE OR REPLACE PACKAGE BODY reports AS

  PROCEDURE get_sales_order_summary(in_out_cur IN OUT REF_CURSOR_TYPE,
                                    from_date IN DATE,
                                    to_date IN DATE) IS

    sql_str VARCHAR2(2000);

  BEGIN

      sql_str :=
    'SELECT 
         o.order_no, 
         SUM(oi.cost * oi.quantity) AS order_cost, 
         SUM(oi.cost * oi.quantityfilled) AS order_cost_filled
           FROM orderitem oi, ordertab o
           WHERE 
         o.order_no = oi.order_no AND
             o.status NOT IN (0,5,6,8,11,13) AND
         o.dateordered >= TRUNC(:1) AND
         o.dateordered < TRUNC(:2) + 1
           GROUP BY o.order_no
    ';


    OPEN in_out_cur FOR sql_str USING 
      from_date,
      to_date;

  END get_sales_order_summary;

END reports;
/

The code section below demonstrates how to execute the stored procedure in sqlplus and output the results from the oracle ref cursor.

SQL> var output refcursor;
SQL> begin
  2  reports.get_sales_order_summary(:output, '18-APR-06', '25-APR-06');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> print output;
 
ORDER_NO             ORDER_COST ORDER_COST_FILLED
-------------------- ---------- -----------------
99027                        13                 0
99028                     282.1                 0
99039                     564.2                 0
99041                      18.3                 0
99042                      18.6                 0

5 rows selected.
 
SQL>

Hope this helps.

[Haris Peco]

Keith,

Your example is fine.I wll see if we will support this in future releases

Thanks

[cwatson76]

I would like to know this, too. We use Ref Cursors extensively and would like to look into using MyEclipse as a tool for our PL/SQL developers as well as our Java developers.

[Author]

Posts