Home » SQL & PL/SQL » SQL & PL/SQL » returning multiple records as a parameter
returning multiple records as a parameter [message #237457] Mon, 14 May 2007 09:53 Go to next message
aadebayo
Messages: 35
Registered: August 2005
Member
Hello

Please can any one point me to an explanation on how I can return multiple rows in a table as an out parameter

This is what I have done so far



Ade

[Updated on: Mon, 14 May 2007 10:31]

Report message to a moderator

Re: returning multiple records as a parameter [message #237459 is a reply to message #237457] Mon, 14 May 2007 09:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check the docs for "pipe row"
Re: returning multiple records as a parameter [message #237467 is a reply to message #237459] Mon, 14 May 2007 10:30 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
Thanks for your response. I have changed the code to the one below, but I am now getting the error ORA-01422: exact fetch returns more than requested number of rows


  FUNCTION  XX_BK_SP_CALENDAR_RECORD RETURN  CALENDARTYPE PIPELINED
    IS
    -- declare variable
    x_Calendar_row  XX_BK_CALENDAR%ROWTYPE;
    x_value         VARCHAR2(50);
    x_slot          VARCHAR2(10);
    -- declare cursor
    CURSOR  c_CalendarList_Cursor--(x_slot)
    IS
       SELECT *  
       FROM XX_BK_CALENDAR;
   --    WHERE CalendarCode = p_CalendarCode;  

     
    
    
    BEGIN
    
    DBMS_OUTPUT.PUT_LINE('Before opening the cursor');
    OPEN c_CalendarList_Cursor;
     
    LOOP
        FETCH c_CalendarList_Cursor INTO x_Calendar_row;
  --      DBMS_OUTPUT.PUT_LINE(x_Calendar_row.calendarcode|| 'before exiting '||x_Calendar_row.calendarid);
     
        EXIT WHEN c_CalendarList_Cursor%notfound;  
  --       DBMS_OUTPUT.PUT_LINE(x_Calendar_row.calendarcode|| 'after exit '||x_Calendar_row.calendarid);
    END LOOP;
    CLOSE c_CalendarList_Cursor;
   DBMS_OUTPUT.PUT_LINE('Closing the cursor');
    SELECT fnd_profile.value('XX_BK_DOG')
    INTO x_Value
    FROM dual;
    
    
    DBMS_OUTPUT.PUT_LINE('Slot Value is '||x_Value);
    
    IF (x_Value = 'FULL') THEN
       SELECT *   
       INTO x_calendar_row 
       FROM xx_bk_calendar ;    
    END IF;
    RETURN ;
    END XX_BK_SP_CALENDAR_RECORD;



I encounter this problem when I try to query the function. below is my query

declare
v_result cursor;

					    

begin

 --  v_result := XX_LGS_BK_CALENDAR_ADMIN_PKG.XX_BK_SP_CALENDAR_RECORD ;
 --   SELECT XX_LGS_BK_CALENDAR_ADMIN_PKG.XX_BK_SP_CALENDAR_RECORD('Pests') INTO v_result
 --   FROM DUAL;
 

SELECT * FROM TABLE(XX_LGS_BK_CALENDAR_ADMIN_PKG.XX_BK_SP_CALENDAR_RECORD) ;




end;

/

[Updated on: Mon, 14 May 2007 10:33]

Report message to a moderator

Re: returning multiple records as a parameter [message #237469 is a reply to message #237467] Mon, 14 May 2007 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora-01422: exact fetch returns more than requested number of rows
 *Cause: The number specified in exact fetch is less than the rows returned.
 *Action: Rewrite the query or change number of rows requested


In addition, why your last select is in a PL/SQL block?

And please don't erase what you posted, we can't follow the topic.

Regards
Michel

[Updated on: Mon, 14 May 2007 10:37]

Report message to a moderator

Re: returning multiple records as a parameter [message #237474 is a reply to message #237469] Mon, 14 May 2007 10:45 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
Thanks.

What I want to do is to return multiple records using the out parameter. Is there something I am doing wrong, in the datatype that I have used.
Re: returning multiple records as a parameter [message #237486 is a reply to message #237474] Mon, 14 May 2007 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe in your query.
Does "SELECT * INTO x_calendar_row FROM xx_bk_calendar ;" always return one row?

Regards
Michel

Re: returning multiple records as a parameter [message #237487 is a reply to message #237486] Mon, 14 May 2007 11:28 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
No, the number of rows that it returns, depends on the value passed in. Sometimes it returns more than one row.
Re: returning multiple records as a parameter [message #237488 is a reply to message #237487] Mon, 14 May 2007 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this is the origin of the error message.

Regards
Michel
Re: returning multiple records as a parameter [message #237606 is a reply to message #237488] Tue, 15 May 2007 03:02 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
thanks, I have spotted some error in my code.
Re: returning multiple records as a parameter [message #237723 is a reply to message #237457] Tue, 15 May 2007 09:28 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
I have finished writing the code, but it does not return any records when I test it. The table concerned has records. Please can some one help

Package

package test_pkg is
    type calendar_tab is table of  xx_bk_calendars ;
    function get_num_row return calendar_tab pipelined; 
end;


Package Body
package body test_pkg as

 function get_num_row return calendar_tab PIPELINED is
  
  x_Profile   VARCHAR(20);
  x_calendar_tab   calendar_tab;
  
    i_calendarid        xx_bk_calendar.CALENDARID%TYPE;             
    i_externalsystemid  xx_bk_calendar.EXTERNALSYSTEMID%TYPE;       
    i_calendarcode      xx_bk_calendar.CALENDARCODE%TYPE;           
    i_description       xx_bk_calendar.DESCRIPTION%TYPE;            
    i_maxdays           xx_bk_calendar.MAXDAYS%TYPE;                
    i_mindays           xx_bk_calendar.MINDAYS%TYPE;                
    i_mintime           xx_bk_calendar.MINTIME%TYPE;                
    i_last_creationdate xx_bk_calendar.LASTCREATIONDATE%TYPE;       
    i_historydays       xx_bk_calendar.HISTORYDAYS%TYPE;            

   
  begin
  x_Profile := 'FULL';
  
  if (x_Profile = 'FULL') THEN
  DBMS_OUTPUT.PUT_LINE('Before performing the selection');                        
  
     select xx_bk_calendars(i_calendarid             
                    ,i_externalsystemid        
                    ,i_calendarcode                 
                    ,i_description                  
                    ,i_maxdays                         
                    ,i_mindays                           
                    ,i_mintime                          
                    ,i_last_creationdate        
                    ,i_historydays)bulk collect 
    into  x_calendar_tab
    from  xx_bk_calendar;
  end if;      
  DBMS_OUTPUT.PUT_LINE('After performing the selection CalendarID is ');                        
  return ;
  end get_num_row;
  end;


Test Script
 select test_pkg.get_num_row from dual;
Re: returning multiple records as a parameter [message #237885 is a reply to message #237723] Wed, 16 May 2007 01:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I see you followed my advise and read about pipe row...
and decided you would have no need for it in your pipelined function
Re: returning multiple records as a parameter [message #237976 is a reply to message #237885] Wed, 16 May 2007 04:17 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
Thanks very much for all your help. This is the final code and it all now works

CREATE OR REPLACE type xx_bk_calendars as object (
    CALENDARID                                NUMBER
    ,EXTERNALSYSTEMID                          NUMBER
    ,CALENDARCODE                              VARCHAR2(12)
    ,DESCRIPTION                               VARCHAR2(30)
    ,MAXDAYS                                   NUMBER
    ,MINDAYS                                   NUMBER
    ,MINTIME                                   DATE
    ,LASTCREATIONDATE                          DATE
    ,HISTORYDAYS                               NUMBER
    );


CREATE OR REPLACE package  test_pkg AS
        
    TYPE calendar_tab IS table OF  xx_bk_calendars ;
    FUNCTION prodFunc  RETURN calendar_tab  PIPELINED;
end;



CREATE OR REPLACE PACKAGE BODY      test AS

 FUNCTION prodFunc RETURN calendar_tab  PIPELINED IS
    i_calendarid        xx_bk_calendar.CALENDARID%TYPE;             
    i_externalsystemid  xx_bk_calendar.EXTERNALSYSTEMID%TYPE;       
    i_calendarcode      xx_bk_calendar.CALENDARCODE%TYPE;           
    i_description       xx_bk_calendar.DESCRIPTION%TYPE;            
    i_maxdays           xx_bk_calendar.MAXDAYS%TYPE;                
    i_mindays           xx_bk_calendar.MINDAYS%TYPE;                
    i_mintime           xx_bk_calendar.MINTIME%TYPE;                
    i_last_creationdate xx_bk_calendar.LASTCREATIONDATE%TYPE;       
    i_historydays       xx_bk_calendar.HISTORYDAYS%TYPE;   
    cursor c1 is 
    select calendarid             
                    ,externalsystemid        
                    ,calendarcode                 
                    ,description                  
                    ,maxdays                         
                    ,mindays                           
                    ,mintime                          
                    ,lastcreationdate        
                    ,historydays
    from xx_bk_calendar;
    
    BEGIN
    FOR cal_rec in c1
    LOOP
      PIPE ROW (xx_bk_calendars(cal_rec.calendarid             
                    ,cal_rec.externalsystemid        
                    ,cal_rec.calendarcode                 
                    ,cal_rec.description                  
                    ,cal_rec.maxdays                         
                    ,cal_rec.mindays                           
                    ,cal_rec.mintime                          
                    ,cal_rec.lastcreationdate        
                    ,cal_rec.historydays));
    END LOOP;
    RETURN;
  END;
  END;
Re: returning multiple records as a parameter [message #242094 is a reply to message #237976] Thu, 31 May 2007 10:46 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
Please can someone tell me how to change this to a stored procedure?

CREATE OR REPLACE PACKAGE BODY      test AS

 FUNCTION prodFunc RETURN calendar_tab  PIPELINED IS
    i_calendarid        xx_bk_calendar.CALENDARID%TYPE;             
    i_externalsystemid  xx_bk_calendar.EXTERNALSYSTEMID%TYPE;       
    i_calendarcode      xx_bk_calendar.CALENDARCODE%TYPE;           
    i_description       xx_bk_calendar.DESCRIPTION%TYPE;            
    i_maxdays           xx_bk_calendar.MAXDAYS%TYPE;                
    i_mindays           xx_bk_calendar.MINDAYS%TYPE;                
    i_mintime           xx_bk_calendar.MINTIME%TYPE;                
    i_last_creationdate xx_bk_calendar.LASTCREATIONDATE%TYPE;       
    i_historydays       xx_bk_calendar.HISTORYDAYS%TYPE;   
    cursor c1 is 
    select calendarid             
                    ,externalsystemid        
                    ,calendarcode                 
                    ,description                  
                    ,maxdays                         
                    ,mindays                           
                    ,mintime                          
                    ,lastcreationdate        
                    ,historydays
    from xx_bk_calendar;
    
    BEGIN
    FOR cal_rec in c1
    LOOP
      PIPE ROW (xx_bk_calendars(cal_rec.calendarid             
                    ,cal_rec.externalsystemid        
                    ,cal_rec.calendarcode                 
                    ,cal_rec.description                  
                    ,cal_rec.maxdays                         
                    ,cal_rec.mindays                           
                    ,cal_rec.mintime                          
                    ,cal_rec.lastcreationdate        
                    ,cal_rec.historydays));
    END LOOP;
    RETURN;
  END;
  END;
Re: returning multiple records as a parameter [message #242103 is a reply to message #242094] Thu, 31 May 2007 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use REF CURSOR instead of table type.

Regards
Michel
Re: returning multiple records as a parameter [message #242251 is a reply to message #242103] Fri, 01 June 2007 05:43 Go to previous message
aadebayo
Messages: 35
Registered: August 2005
Member
Thanks Michael. I am now using a ref cursor, but the performance is too slow. Is there a better way of me doing it. Here is the code




ref cursor type created within a package called XX_LGS_BK_CALENDAR_ADMIN_PKG

 TYPE CAL_CURSOR IS REF CURSOR;





procedure within package body called XX_LGS_BK_CALENDAR_ADMIN_PKG

  PROCEDURE XX_BK_SP_LIST_CALENDARS(p_ProfileType IN VARCHAR2, cal_lists OUT CAL_CURSOR,   cals_cursor  OUT xx_bk_calendar%ROWTYPE) 
   IS
    
-- declare variables to hold the values from the cursor
    lc_Profile_Value   VARCHAR2(18);
    lc_fnd_profile     VARCHAR2(18);
    lc_open_quote      VARCHAR2(100);
    TYPE cal_cursor IS REF CURSOR;


   BEGIN
    
    IF (p_ProfileType = 'ALL') THEN

    	
	open cal_lists for     
		   SELECT calendarid             
                    ,externalsystemid        
                    ,calendarcode                 
                    ,description                  
                    ,maxdays                         
                    ,mindays                           
                    ,mintime                          
                    ,lastcreationdate        
                    ,historydays
                   FROM xx_bk_calendar;
    	LOOP
            fetch cal_lists into cals_cursor;
            exit when cal_lists%NOTFOUND;
            dbms_output.put_line(cals_cursor.calendarid || ' - ' || cals_cursor.description);
    	END LOOP;
        close cal_lists;
    ELSE IF (p_ProfileType = 'SLOT') THEN
        open cal_lists for
                  SELECT calendarid             
                    ,externalsystemid        
                    ,calendarcode                 
                    ,description                  
                    ,maxdays                         
                    ,mindays                           
                    ,mintime                          
                    ,lastcreationdate        
                    ,historydays
                   FROM xx_bk_calendar;
     	    LOOP
	      lc_Profile_Value := concat('XX_BK_',cals_cursor.calendarcode);
              lc_Profile_Value := rtrim(lc_Profile_Value);
              SELECT fnd_profile.value (lc_Profile_Value) INTO lc_fnd_profile FROM dual;
              lc_open_quote := 'SELECT ( fnd_profile.value rtrim('''||lc_Profile_Value||''')) INTO lc_fnd_profile FROM dual';
	      IF (lc_fnd_profile = 'SLOT') THEN
                  fetch cal_lists into cals_cursor;
                  exit when cal_lists%NOTFOUND;
                  dbms_output.put_line(cals_cursor.calendarid || ' - ' || cals_cursor.description);
    	    END IF;
            END LOOP;
        close cal_lists;
    ELSE
        open cal_lists for
                  SELECT calendarid             
                    ,externalsystemid        
                    ,calendarcode                 
                    ,description                  
                    ,maxdays                         
                    ,mindays                           
                    ,mintime                          
                    ,lastcreationdate        
                    ,historydays
                   FROM xx_bk_calendar;
     	    LOOP
	      lc_Profile_Value := concat('XX_BK_',cals_cursor.calendarcode);
              lc_Profile_Value := rtrim(lc_Profile_Value);
              SELECT fnd_profile.value (lc_Profile_Value) INTO lc_fnd_profile FROM dual;
              lc_open_quote := 'SELECT ( fnd_profile.value rtrim('''||lc_Profile_Value||''')) INTO lc_fnd_profile FROM dual';
	      IF (lc_fnd_profile = 'SLOT') OR (lc_fnd_profile = 'FULL') THEN
                  fetch cal_lists into cals_cursor;
                  dbms_output.put_line(cals_cursor.calendarid || ' - ' || cals_cursor.description);
                  exit when cal_lists%notfound;
              END IF;
            END LOOP;
        close cal_lists;
    END IF; 
    END IF;
   RETURN;
  END XX_BK_SP_LIST_CALENDARS;

[Updated on: Fri, 01 June 2007 06:38]

Report message to a moderator

Previous Topic: LIKE condition
Next Topic: to make entry in log table
Goto Forum:
  


Current Time: Thu Dec 08 10:35:10 CST 2016

Total time taken to generate the page: 0.11933 seconds