Home » SQL & PL/SQL » SQL & PL/SQL » Functions and exceptions
Functions and exceptions [message #244395] Tue, 12 June 2007 10:53 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
Since functions have to return a value looks like most functions are returning 0 as default.

Now my question is when we call these functions do exceptions work ? Because it is returning 0, are the exception handling bypassed in this case (or will it still work) ?

DECLARE
   lSY_ID      IN LETT_DELETE.SY_ID%TYPE      := NULL,
   lXR_DEST_ID IN LETT_DELETE.XR_DEST_ID%TYPE := NULL,
   lLT_SEQ_NO  IN LETT_DELETE.LT_SEQ_NO%TYPE  := NULL
   lnRetCd       DATATYPE_PKG.INT%TYPE;

begin
lnRetCd = LETT_DELETE(
           lSY_ID      ,
           lXR_DEST_ID ,
           lLT_SEQ_NO 
           )

exception
  when others then
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
end;


CREATE OR REPLACE function LETT_DELETE
(
/*************************************************
** Declare Parameters                           **
*************************************************/

   pSY_ID      IN LETT_DELETE.SY_ID%TYPE      := NULL,
   pXR_DEST_ID IN LETT_DELETE.XR_DEST_ID%TYPE := NULL,
   pLT_SEQ_NO  IN LETT_DELETE.LT_SEQ_NO%TYPE  := NULL
)
return number
AS

/*************************************************
** Declare Local Variables                      **
*************************************************/

 lnRetCd       DATATYPE_PKG.INT%TYPE;

/*************************************************
** Begin Procedure Code                        **
*************************************************/
BEGIN

 lnRetCd := 0;

 /*****************************************
 ** Update the Base Table                **
 *****************************************/

 IF lnRetCd = 0 THEN
    DELETE FROM
       LETT_DELETE
    WHERE
       SY_ID      = pSY_ID      AND
       XR_DEST_ID = pXR_DEST_ID AND
       LT_SEQ_NO  = pLT_SEQ_NO;
 END IF;


 RETURN lnRetCd;

END LETT_DELETE;
/
Re: Functions and exceptions [message #244397 is a reply to message #244395] Tue, 12 June 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If there is an exception there is no return value.
I don't understand your question.
Your function does not handle any exception.

Or better, just try it.

Regards
Michel
Re: Functions and exceptions [message #244410 is a reply to message #244395] Tue, 12 June 2007 12:53 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Where did you get the idea that 0 is returned?
Because the function raises an exception, the calling block/function/procedure never gets to the assignment of the return value, whence the return value is simply not defined.
The caller falls through to its exception block, if present, or will reraise the exception if no exception block is present.
Previous Topic: Use of Oracle Cursors?
Next Topic: Finding 15th of Prior Month
Goto Forum:
  


Current Time: Sun Dec 04 10:40:36 CST 2016

Total time taken to generate the page: 0.04541 seconds