Home » SQL & PL/SQL » SQL & PL/SQL » This 9i code is throwing error "ORA-06503: Function returned without value" when executed
icon4.gif  This 9i code is throwing error "ORA-06503: Function returned without value" when executed [message #378660] Wed, 31 December 2008 04:30 Go to next message
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 Sad .
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: This 9i code is throwing error "ORA-06503: Function returned without value" when execu [message #378664 is a reply to message #378660] Wed, 31 December 2008 04:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that cursor returns nothing, so RETURN returns nothing; instead of useless WHEN OTHERS you have used, RETURN ('exception') would - at least - return *something*.
Re: This 9i code is throwing error "ORA-06503: Function returned without value" when execu [message #378676 is a reply to message #378664] Wed, 31 December 2008 05:05 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

In 10g, if i set the optimizer_features_enable parameter from 10.2 to 9.2 using the below query, the above dml statement will work fine.

alter system set optimizer_features_enable="9.2.0";
is there any alternative solution for this??

[Updated on: Wed, 31 December 2008 07:29]

Report message to a moderator

Re: This 9i code is throwing error "ORA-06503: Function returned without value" when execu [message #378702 is a reply to message #378676] Wed, 31 December 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove that stupid when others.

Regards
Michel
icon4.gif  is there any alternative solution for this?? [message #379130 is a reply to message #378660] Mon, 05 January 2009 00:47 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Quote:
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




In the above insert statement, the cursor returns nothing.
But this statement is executing successfully in the 9i environment. Razz

If we try to execute the same code in 10g,
Quote:

The error "ORA-06503: PL/SQL: Function returned without value" pointing to the function call "get_list" is thrown.
Sad

Our DBA Shocked suggested to set the optimizer_features_enable parameter from 10.2 to 9.2 using the below query,
Quote:
alter system set optimizer_features_enable="9.2.0";

I hav the following questions.


  1. why do this error occurs in 10g alone but not in 9i?
  2. is this one, the right solution for this problem or
  3. is there any alternative solution other than this??

Re: is there any alternative solution for this?? [message #379131 is a reply to message #379130] Mon, 05 January 2009 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Post the code of get_list
2. No, this is the last one to consider
3. It depends on your code

Regards
Michel
Re: is there any alternative solution for this?? [message #379132 is a reply to message #379130] Mon, 05 January 2009 01:03 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

ORA-06503: PL/SQL: Function returned without value 
Cause: A call to PL/SQL function completed, but no RETURN statement was executed. 
Action: Rewrite PL/SQL function, making sure that it always returns a value of a proper type.  
Re: is there any alternative solution for this?? [message #379133 is a reply to message #379130] Mon, 05 January 2009 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I see you already posted this so I merged the 2 topics and you have your answer:

Michel Cadot wrote on Wed, 31 December 2008 15:55
Remove that stupid when others.

Regards
Michel

[Updated on: Mon, 05 January 2009 01:04]

Report message to a moderator

Re: is there any alternative solution for this?? [message #379134 is a reply to message #379133] Mon, 05 January 2009 01:08 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Quote:
EXCEPTION
   --WHEN OTHERS
   --THEN
   RETURN ('exception');



is not working??

pls advice
Re: is there any alternative solution for this?? [message #379135 is a reply to message #379134] Mon, 05 January 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"is not working" is NOT an Oracle message.
And you did not remove the stupid clause.
If there is nothing in exception, remove the whole exception.

Regards
Michel

[Updated on: Mon, 05 January 2009 01:10]

Report message to a moderator

Re: is there any alternative solution for this?? [message #379143 is a reply to message #379135] Mon, 05 January 2009 02:06 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member


ORA-00904 error occurred.


./fa/5540/0/

  • Attachment: 1.jpg
    (Size: 160.41KB, Downloaded 382 times)
Re: is there any alternative solution for this?? [message #379155 is a reply to message #379143] Mon, 05 January 2009 03:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, the error is in GET_LIST.

Next time, please just paste the message as text.
Re: is there any alternative solution for this?? [message #379166 is a reply to message #379155] Mon, 05 January 2009 03:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 8635
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.
Previous Topic: SQL problem
Next Topic: PL/SQL: ORA-00904: "DICTIONARY_OBJ_NAME": invalid identifier (3 threads merged by bb)
Goto Forum:
  


Current Time: Thu Dec 08 22:35:37 CST 2016

Total time taken to generate the page: 0.07247 seconds