Home » SQL & PL/SQL » SQL & PL/SQL » User defined Exception (Oracle 9i)
User defined Exception [message #343989] Thu, 28 August 2008 09:42 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

Can you suggest me how to raise a userdefined exception for an no_data_found exception..?

I have done a non predefined exception for no_data_found
DECLARE
   v_ename       emp.ename%TYPE;
   v_character   VARCHAR2 (100);
   DATA          EXCEPTION;
   PRAGMA EXCEPTION_INIT (DATA, 100);
BEGIN
   SELECT ename
     INTO v_ename
     FROM emp
    WHERE empno = 10000;--there is no emp record with empno=10000
    IF (SQL%NOTFOUND)
   THEN
      RAISE DATA;
   END IF;
EXCEPTION
   WHEN DATA
   THEN
      DBMS_OUTPUT.put_line ('no data found exception');
END;




Can we do the same implementation using an user defined exception

Regards,
Pointers.
Re: User defined Exception [message #343995 is a reply to message #343989] Thu, 28 August 2008 09:50 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
* Remove DATA EXCEPTION
* Remove PRAGMA EXCEPTION_INIT (DATA, 100)
* REMOVE RAISE exceptioon


 EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('no data found exception');
   END;



Regards,
Oli
Re: User defined Exception [message #344007 is a reply to message #343995] Thu, 28 August 2008 10:16 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

U have to explicitly define the expection

such as

EXCEPTION 
     When SQL%NOTFOUND 
     THEN
DBMS_OUTPUT.PUT_LINE('YOUR COMMENT'(wat ever u want to display in the output));

END;


Re: User defined Exception [message #344010 is a reply to message #343989] Thu, 28 August 2008 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use RAISE_APPLICATION_ERROR, see: Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR

Regards
Michel
Re: User defined Exception [message #344011 is a reply to message #343989] Thu, 28 August 2008 10:23 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sorry for providing wrong answers. Given example of predefined exception instead of "user defined exception" (what was asked)

Regards,
Oli
Re: User defined Exception [message #344023 is a reply to message #344011] Thu, 28 August 2008 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Summary of Predefined PL/SQL Exceptions
Is this that you want?

Regards
Michel
Re: User defined Exception [message #344024 is a reply to message #343989] Thu, 28 August 2008 11:10 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
From your example if you wanted to raise a custom exception your first step has to be either catching the exception thrown by the SELECT into or or selecting the data in such a way that no exception is thrown when nothing is encountered now. As your code is now the explicit call to raise the DATA exception never happens.

Look at the following example I subbed out your no data found exception for a 'dump string is not valid' (-77) exception. If you run the code you will get a no-data-found exception because the exception is raised before you ever reach your explicit raise of the DATA exception.

DECLARE
   v_ename       VARCHAR2 (10);
   v_character   VARCHAR2 (100);
   DATA          EXCEPTION;
   PRAGMA EXCEPTION_INIT (DATA, -77);
BEGIN
   SELECT dummy
     INTO v_ename
     FROM DUAL
    WHERE dummy = 'Z';                 --there is no emp record with empno=10000

   IF (SQL%NOTFOUND)
   THEN
      RAISE DATA;
   END IF;
EXCEPTION
   WHEN DATA
   THEN
      DBMS_OUTPUT.put_line ('no data found exception');
END;
Re: User defined Exception [message #344028 is a reply to message #344024] Thu, 28 August 2008 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to initialize the exception with a value to use it.
Remove your "PRAGMA EXCEPTION_INIT" and it works as well.

Regards
Michel
Re: User defined Exception [message #344033 is a reply to message #344028] Thu, 28 August 2008 11:53 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Michel Cadot wrote on Thu, 28 August 2008 11:37
You don't need to initialize the exception with a value to use it.
Remove your "PRAGMA EXCEPTION_INIT" and it works as well.



No he is going to get a NO_DATA_FOUND from select statement, removing the pragma just changes the nature of the exception that he never gets to throw....
Re: User defined Exception [message #344035 is a reply to message #344033] Thu, 28 August 2008 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2     v_ename       VARCHAR2 (10);
  3     v_character   VARCHAR2 (100);
  4     DATA          EXCEPTION;
  5     PRAGMA EXCEPTION_INIT (DATA, -77);
  6  BEGIN
  7     SELECT dummy
  8       INTO v_ename
  9       FROM DUAL
 10      WHERE dummy = 'Z';                 --there is no emp record with empno=10000
 11  
 12     IF (SQL%NOTFOUND)
 13     THEN
 14        RAISE DATA;
 15     END IF;
 16  EXCEPTION
 17     WHEN DATA
 18     THEN
 19        DBMS_OUTPUT.put_line ('no data found exception');
 20  END;
 21  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7

Well, I got "no data found".
SQL> DECLARE
  2     v_ename       VARCHAR2 (10);
  3     v_character   VARCHAR2 (100);
  4     DATA          EXCEPTION;
  5  -- PRAGMA EXCEPTION_INIT (DATA, -77);
  6  BEGIN
  7    BEGIN
  8      SELECT dummy INTO v_ename FROM DUAL WHERE dummy = 'Z';   
  9    EXCEPTION
 10      WHEN NO_DATA_FOUND THEN RAISE DATA;
 11    END;
 12  EXCEPTION
 13     WHEN DATA
 14     THEN
 15        DBMS_OUTPUT.put_line ('no data found exception');
 16  END;
 17  /
no data found exception

PL/SQL procedure successfully completed.

There it goes.

Regards
Michel
Re: User defined Exception [message #344194 is a reply to message #344035] Fri, 29 August 2008 01:13 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Isn't using the use of predefined exception will be better for the above pl/sql code rather than making more lines of code?

Regards,
Oli
Re: User defined Exception [message #344205 is a reply to message #344194] Fri, 29 August 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, but I think the purpose here is just how to use.

Regards
Michel
Re: User defined Exception [message #344275 is a reply to message #343989] Fri, 29 August 2008 05:08 Go to previous message
pointers
Messages: 410
Registered: May 2008
Senior Member
Thanks for your time and information....
I got the answer from Michel Cadot reply.....thanks a lot Michael, annagel, Olivia....
Previous Topic: Column Names assigned to a cursor at Runtime
Next Topic: Thinking of using Merge Statement
Goto Forum:
  


Current Time: Sat Dec 10 18:33:14 CST 2016

Total time taken to generate the page: 0.07650 seconds