1

I have a package in oracle that contains one stored procedure. It takes a date as a input and a number as input, and outputs the data into a ref_cursor. The purpose of the stored procedure is to extract all of the "winning numbers" that correspond to a certain input date. 

CREATE OR REPLACE PACKAGE GETWINNINGNUMBERSBYDATEPKG IS

TYPE lotgwndate_ref_cursor IS REF CURSOR;     
PROCEDURE GetWinningNumbersDATE (lg_wndate IN date, lg_wnwgid IN number, lg_ref OUT 
lotgwndate_ref_cursor); 

END GETWINNINGNUMBERSBYDATEPKG;

This is the package body. Again, the purpose here is to extract all of the "winning numbers" that correspond to a certain date and "gameid" number. Then, I want to pair the "winning numbers" with the "game name" and "drawing date". (This is to extract records from lottery drawings)

  PROCEDURE GetWinningNumbersDATE (lg_wndate IN date, lg_wnwgid IN number, lg_ref OUT    
  lotgwndate_ref_cursor) IS
   BEGIN
    OPEN lg_ref FOR
      SELECT a.GAMENAME, b.DRAWINGDATE, c.BALLNUMBER
        FROM GAMEDETAILS a
          INNER JOIN WINNINGRECORDS b
            on a.GAMEDETAILSID = b.GAMEDETAILSID 
              INNER JOIN WINNINGBALLS c
                on b.WINNINGRECORDSID = c.WINNINGRECORDSID


      WHERE b.DRAWINGDATE = lg_wndate
      AND a.GAMEDETAILSID = lg_wnwgid;

END GetWinningNumbersDATE;

END GETWINNINGNUMBERSBYDATEPKG;

Here is the call procedure. 

SET SERVEROUTPUT ON size 100000
DECLARE 
  v_cursor               LOTTERYGAMEPKG.lotgwndate_ref_cursor;
  v_gamename             GAMEDETAILS.gamename%type;
  v_drawingdate          WINNINGRECORDS.drawingdate%type;
  v_ballnumber           WINNINGBALLS.ballnumber%type;
BEGIN
  GETWINNINGNUMBERSBYDATEPKG.GetWinningNumbersDATE(lg_wndate =>    
TO_DATE('06/08/2014','dd/mm/yyyy'), lg_wnwgid => '4', /*date/gamedetailsid #(1-4)*/
                          lg_ref => v_cursor);
LOOP                             
  FETCH v_cursor
    INTO  v_gamename, v_drawingdate, v_ballnumber;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE
    (v_gamename || ',' || v_drawingdate || ',' || v_ballnumber);
END LOOP;
 END;

This works, and returns:

NorthstarCash,06-AUG-14,10
NorthstarCash,06-AUG-14,11
NorthstarCash,06-AUG-14,15
NorthstarCash,06-AUG-14,16
NorthstarCash,06-AUG-14,21

However, I want it to return 

NorthstarCash,06-AUG-14,10,11,15,16,21

Are there any ways to do this? I have looked at many other questions but none are addressing the same issue as mine. ORACLE 11g

javascript Jenkins
  • 113
  • 1
  • 4
  • 10
  • I believe the question you posted does not address the call procedure part of my question. It only addresses the select statement. I am trying to figure out how to put the agg string result into my call procedure statement. – javascript Jenkins Oct 14 '14 at 17:18
  • unfortunately, the `FETCH` you expect, actually has to be written by you to append the numbers and by using temp variables... Oracle has nothing to do with it. – Maheswaran Ravisankar Oct 14 '14 at 17:22
  • Thank you. I am not aware of the syntax to accomplish this. Perhaps you could direct me to a resource? – javascript Jenkins Oct 14 '14 at 17:27

1 Answers1

0

If you use Oracle 11g, just modify your SELECT query using LISTAGG

PROCEDURE GetWinningNumbersDATE (lg_wndate IN date, lg_wnwgid IN number, lg_ref OUT    
  lotgwndate_ref_cursor) IS
   BEGIN
    OPEN lg_ref FOR
      SELECT a.GAMENAME, b.DRAWINGDATE, LISTAGG(c.BALLNUMBER,',') WITHIN GROUP (ORDER BY c.BALLNUMBER) AS BALLNUMBER
        FROM GAMEDETAILS a
          INNER JOIN WINNINGRECORDS b
            on a.GAMEDETAILSID = b.GAMEDETAILSID 
              INNER JOIN WINNINGBALLS c
                on b.WINNINGRECORDSID = c.WINNINGRECORDSID  
        WHERE b.DRAWINGDATE = lg_wndate
      AND a.GAMEDETAILSID = lg_wnwgid
     GROUP BY a.GAMENAME, b.DRAWINGDATE;

END GetWinningNumbersDATE;

And the caller, has to be modified like below.

v_ballnumber           VARCHAR2(4000);
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69