Home » SQL & PL/SQL » SQL & PL/SQL » function returned without value error
function returned without value error [message #223260] Thu, 08 March 2007 03:40 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i have the following function


FUNCTION Get (
    n_use       IN  number,
    n_int   IN  number,
    o_down OUT varchar2
    )
RETURN INTEGER
AS
    l_Prg VARCHAR2(30) := 'GetUserMappingID';
BEGIN
    -- Verify for required in parameters
    IF  n_use IS NULL OR n_int  IS NULL  THEN
        Raise p_missing_parameters ;
    END IF;

    
    SELECT DOWNS INTO o_down FROM mytable
    WHERE USER=n_use  AND INTE=n_int;

	
		return 0;
	
 
EXCEPTION

    WHEN no_data_found THEN
	RAISE_APPLICATION_ERROR(-20002, 'No Data Found  in '||n_use||' and '||n_int);
        
    WHEN p_missing_parameters THEN
        RAISE_APPLICATION_ERROR(-20001, 'Missing essential parameters in '||l_Prg);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM||' IN '||l_Prg);
END Get;




when i am executing it,

it says function returned without value....

but i am specifying return 0....

then why ths error?

but if i provide data which is not there, i am able to go into
no_data_found


Re: function returned without value error [message #223261 is a reply to message #223260] Thu, 08 March 2007 03:43 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
the p_missing_parameters is declared in specification
Re: function returned without value error [message #223273 is a reply to message #223260] Thu, 08 March 2007 04:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post the actual error stack that you are getting back.
Re: function returned without value error [message #223274 is a reply to message #223260] Thu, 08 March 2007 04:19 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "PP1.packagename", line 148
ORA-06512: at line 1


when i say

exec :g_add:=packagename.functionname(2,1,:g_dwn);

Re: function returned without value error [message #223276 is a reply to message #223260] Thu, 08 March 2007 04:24 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
this is not a standalone function, so the p_missing_parameters
is already defined in the package spec
Re: function returned without value error [message #223279 is a reply to message #223276] Thu, 08 March 2007 04:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And what is on line 148 of the package?
Re: function returned without value error [message #223285 is a reply to message #223260] Thu, 08 March 2007 05:18 Go to previous message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
resoloved !

i have just included another anonymous block after the select,
and handling the exception

BEGIN
SELECT DOWNST INTO o_down FROM tablename
WHERE USERID=n_use AND INTID=n_inte;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE hh_no_data_found;
END;


now its no problem, thanks jrowbottom for the concern

the matter is resolved
Previous Topic: Urgent Help required on Oracle Date Formats
Next Topic: Difference between Function and procedure
Goto Forum:
  


Current Time: Sun Dec 04 06:13:19 CST 2016

Total time taken to generate the page: 0.12879 seconds