Home » SQL & PL/SQL » SQL & PL/SQL » query using ref cursor (oracle 10g)
query using ref cursor [message #415187] Sun, 26 July 2009 01:45 Go to next message
Suwarna
Messages: 10
Registered: August 2008
Location: Mumbai
Junior Member
Dear All,
I have a query in the below stored proc. I need to iterate the values of generic id of cursor c1 and pass them to the main query. I have tried the code below, but it is passing only one value and final recordset returns values only of 9992285 related data. I need the out_recordset to be displayed in the crystal report with sum of data for the parentkey i.e 9992285.
Please guide me. Thanks in advance.
CREATE OR REPLACE PROCEDURE Sp_rpt_npl_test 
     (out_recordset  OUT SYS_REFCURSOR) 
IS 
  CURSOR c1 IS 
    /*get all parent ids*/ 
    SELECT genericid 
    FROM   company_generic cg 
    WHERE  genericid IS NOT NULL; /*9992285,9992284*/ 
BEGIN 
  FOR var IN c1 LOOP 
    OPEN out_recordset FOR 
      SELECT   cg.description, 
               Sum(Fn_get_dues(ibh.loan_no,Get_date(2000),'BOS')) sum_dues 
      FROM     company_generic cg, 
               interface_balances_hist ibh 
      WHERE    cg.parentkey = var.genericid 
               AND ibh.npfstatus = 'Y' 
      GROUP BY cg.description 
      UNION ALL 
      SELECT cg.description, 
             (SELECT Sum(Fn_get_dues(ibh.loan_no,Get_date(2000),'BOS')) 
              FROM   company_generic cg, 
                     interface_balances_hist ibh 
              WHERE  cg.parentkey = var.genericid 
                     AND ibh.npfstatus = 'Y') sum_dues 
      FROM   company_generic cg 
      WHERE  cg.genericid = var.genericidEND LOOP; 
END sp_rpt_npl_test;


[EDITED by LF: applied [code] tags]

[Updated on: Sun, 26 July 2009 16:08] by Moderator

Report message to a moderator

Re: query using ref cursor [message #415202 is a reply to message #415187] Sun, 26 July 2009 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Thu, 06 November 2008 08:24
...
To format, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: query using ref cursor [message #415236 is a reply to message #415187] Sun, 26 July 2009 10:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I was really really bored, so I even took a look at your unformatted code.
I found two bugs at first glance:
1) There should be a semicolon just before the END LOOP to terminate the OPEN ref-cursor statement
2) A logical bug: you open the ref-cursor in a loop. That makes no sense, as you can only return a single instance.

And maybe:
3) Logically this would be a function, not a procedure. You want to return a single item (the ref-cursor), so why not use a function?
Re: query using ref cursor [message #415239 is a reply to message #415236] Sun, 26 July 2009 11:58 Go to previous messageGo to next message
Suwarna
Messages: 10
Registered: August 2008
Location: Mumbai
Junior Member
Yes, Calling a ref-cursor in a loop is not returning me the desired result.
I know this can be handled thru a function, but I wanted to use it as a stored proc to be mapped as a datasource for a crystal report.

Sorry for the unformatted post.. I pasted the code formatted in PL/SQL developer available at my work place.


Thanks for your replies.
Re: query using ref cursor [message #415241 is a reply to message #415239] Sun, 26 July 2009 13:14 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I pasted the code formatted in PL/SQL developer

If you read forum guide, you'd know how to format in the forum.

Regards
Michel
Previous Topic: procedure / function / package header declaration
Next Topic: open / close cursor
Goto Forum:
  


Current Time: Sat Dec 10 16:35:50 CST 2016

Total time taken to generate the page: 0.12810 seconds