This 9i code is throwing error "ORA-06503: Function returned without value" when executed [message #378660] |
Wed, 31 December 2008 04:30  |
rtnataraj
Messages: 102 Registered: December 2008
|
Senior Member |

|
|
INSERT INTO gtt_facility
SELECT get_list
(CURSOR (SELECT f.datarange
FROM facility f
WHERE f.credit_id =
af.credit_id
AND f.facility_id = af.facility_id )
) AS datarange
FROM authorized_facility af
WHERE af.credit_id = :p_cred_app_id
The error "ORA-06503: PL/SQL: Function returned without value" is pointing at the function call "get_list" ,
please refer the function definition below .
CREATE OR REPLACE FUNCTION get_list (p_cursor IN sys_refcursor)
RETURN VARCHAR2
IS
l_text_return VARCHAR2 (10000);
l_text VARCHAR2 (10000);
BEGIN
LOOP
FETCH p_cursor
INTO l_text;
EXIT WHEN p_cursor%NOTFOUND;
l_text_return := l_text_return || l_text;
END LOOP;
RETURN l_text_return;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('exception');
END get_list; [EDITED by LF: applied [code] tags]
[Updated on: Wed, 31 December 2008 04:36] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: is there any alternative solution for this?? [message #379166 is a reply to message #379155] |
Mon, 05 January 2009 03:50   |
rtnataraj
Messages: 102 Registered: December 2008
|
Senior Member |

|
|
ok Frank.
CREATE OR REPLACE FUNCTION Get_List
(
p_cursor IN sys_refcursor
)
RETURN VARCHAR2
IS
l_sep VARCHAR2(4);
l_text VARCHAR2(10000);
l_text_return VARCHAR2(10000);
BEGIN
LOOP
FETCH p_cursor INTO l_text;
EXIT WHEN p_cursor%NOTFOUND;
l_text_return := l_text_return || l_sep || l_text;
l_sep := CHR(10);
END LOOP;
CLOSE p_cursor;
RETURN l_text_return;
END Get_List;
/
this is the actual function definition of the get_list function.
please give me a solution for this.
[Updated on: Mon, 05 January 2009 04:01] Report message to a moderator
|
|
|
Re: is there any alternative solution for this?? [message #379177 is a reply to message #379166] |
Mon, 05 January 2009 04:56   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Based on the error message that you posted before, the error is happening when you try to fetch from the cursor.
The obvious inference from this is that the cursoe that you're passing in has an error in it.
Can you run the code for this cursor independently?
|
|
|
Re: is there any alternative solution for this?? [message #379256 is a reply to message #379143] |
Mon, 05 January 2009 12:36  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You have posted different code with different error messages and left out or obscured the relevant parts. In your latest error message it is saying that "A5.FACILITY_SYSTEM_ID" is in invalid identifier, so that is what you need to focus on. You need to make sure that it is spelled correctly, it exists, you have privileges to it, and it is within scope. Perhaps it should have been AF or AS instead of A5.
|
|
|