Home » SQL & PL/SQL » SQL & PL/SQL » Ref cursor use (Oracle 11g)
Ref cursor use [message #584733] Fri, 17 May 2013 05:23 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

What is the use of declaring out parameter as a ref cursor.

In which scenario we use this.

In my application in the below code ref cursor has been used.

CREATE OR REPLACE PROCEDURE getivr_proc (
   numOfInstances       IN     NUMBER,
   instanceID           IN     NUMBER,
   IOVOrdersForLookUp      OUT sys_refcursor
)
AS
BEGIN
   OPEN IOVOrdersForLookUp FOR
        SELECT   STG.buid,
                 STG.order_num,
                 ONL.BLACKBOX_ID,
                 STG.CUSTOMER_NUM,
                 ONL.IP_ADDRESS,
                 IS_DPIDExists
          FROM      staging_order_data STG
                 INNER JOIN
                    staging_online_data ONL
                 ON STG.order_num = ONL.order_num AND STG.buid = ONL.buid --,ONL.BLACKBOX_ID
         -- Production defect- to avoid picking up same order by multiple instances
         WHERE   NOT EXISTS
                    (SELECT   1
                       FROM   iov_ctd_msg ctd
                      WHERE   ctd.order_num = STG.order_num
                              AND STG.buid = ctd.buid)
                 AND STG.online_queried_flag = 'Y'
                 AND STG.ADDRESS_TYPE = 'S'
                 AND STG.ORDER_STAGING_STATUS_CODE = 'READY_FOR_IOVATION'
                 AND MOD (TO_CHAR (STG.ORDER_DATE, 'ss'), numOfInstances) =
                       instanceID - 1
      ORDER BY   STG.CUSTOMER_NUM, ONL.IP_ADDRESS, STG.modified_date ASC;
END;
/

I am not able to understand what exactly will it do.

The out parameter will hold the result of the query (or)
will it hold the query.

Please help me.

Thanks in advance.

Re: Ref cursor use [message #584734 is a reply to message #584733] Fri, 17 May 2013 05:36 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: REPLACE not replacing
Next Topic: Need help on SQL join.
Goto Forum:
  


Current Time: Sun Dec 28 00:47:03 CST 2014

Total time taken to generate the page: 0.10719 seconds