Home » SQL & PL/SQL » SQL & PL/SQL » returning multiple records as a parameter
|
|
|
|
| Re: returning multiple records as a parameter [message #237467 is a reply to message #237459] |
Mon, 14 May 2007 10:30   |
aadebayo
Messages: 38
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 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #237723 is a reply to message #237457] |
Tue, 15 May 2007 09:28 |
aadebayo
Messages: 38
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 #237976 is a reply to message #237885] |
Wed, 16 May 2007 04:17 |
aadebayo
Messages: 38
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 |
aadebayo
Messages: 38
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 #242251 is a reply to message #242103] |
Fri, 01 June 2007 05:43 |
aadebayo
Messages: 38
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 |
|
|
|
Goto Forum:
Current Time: Sat Feb 15 15:09:40 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
