Home » SQL & PL/SQL » SQL & PL/SQL » Exception handling for Package Variables
Exception handling for Package Variables [message #214559] Wed, 17 January 2007 01:16 Go to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
If there is an error in declaring a [package] variable in the Package Specification where shall we handle this error either in PACKAGE BODY or in PACKAGE SPECIFICATION.

Thanks
cgk
Re: Exception handling for Package Variables [message #214560 is a reply to message #214559] Wed, 17 January 2007 01:28 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

what error ?. may be this should help.
CREATE OR REPLACE PACKAGE TEST_PKG1 IS
PROCEDURE TEST_PROC1;
END;
/
CREATE OR REPLACE PACKAGE BODY TEST_PKG1 IS
VAR NUMBER;
PROCEDURE TEST_PROC1 IS
BEGIN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(VAR));
END;
BEGIN
VAR :=1/0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('DIVIDING BY ZERO');
END;

OR
CREATE OR REPLACE PACKAGE TEST_PKG1 IS
PROCEDURE TEST_PROC1;
ERR_ZERO_DIVIDE EXCEPTION;
PRAGMA EXCEPTION_INIT(ERR_ZERO_DIVIDE,-1476);
END;
/
CREATE OR REPLACE PACKAGE BODY TEST_PKG1 IS
VAR NUMBER;
PROCEDURE TEST_PROC1 IS
BEGIN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(VAR));
END;
BEGIN
VAR :=1/0;
EXCEPTION
WHEN ERR_ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('DIVIDING BY ZERO');
END;



regards,

[Updated on: Wed, 17 January 2007 01:32]

Report message to a moderator

Re: Exception handling for Package Variables [message #214561 is a reply to message #214560] Wed, 17 January 2007 01:35 Go to previous messageGo to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
CREATE OR REPLACE PACKAGE bad_constant
IS
c_number CONSTANT NUMBER NOT NULL := 'abc';
END bad_constant;

If this is the spec and i accessed the variable like this

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.put_line (
3 NVL ( TO_CHAR ( bad_constant.c_number ), 'NULL!?' ));
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HR.BAD_CONSTANT", line 3
ORA-06512: at line 2


So where to handle this error
Re: Exception handling for Package Variables [message #214577 is a reply to message #214559] Wed, 17 January 2007 03:02 Go to previous messageGo to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
Any Body Please help me in this
Re: Exception handling for Package Variables [message #214612 is a reply to message #214577] Wed, 17 January 2007 04:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, sorry for not droppping everything and getting back to you in the two hours since you posted this thread, but I've actually got a job to do, and this is just a hobby.

There is nowhere in the package spec (or body) of the package bad_constant that this exception can be caught - it will always raise in the program unit that references bad_constant.
Re: Exception handling for Package Variables [message #214622 is a reply to message #214561] Wed, 17 January 2007 05:24 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Unfortunately Oracle allows you to declare a character value for a numeric constant. So, there is no error until you try to access it. Therefore, you would have to handle the exception from the anonymous pl/sql block or whatever you are accessing the constant from, as shown below. In reality you would provide a more meaningful message. The one below is just intended to briefly show where it is handled.


SCOTT@10gXE> 
SCOTT@10gXE> CREATE OR REPLACE PACKAGE bad_constant
  2  IS
  3    c_number CONSTANT NUMBER NOT NULL := 'abc';
  4  END bad_constant;
  5  /

Package created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> SET SERVEROUTPUT ON
SCOTT@10gXE> BEGIN
  2    DBMS_OUTPUT.put_line
  3  	 (NVL ( TO_CHAR ( bad_constant.c_number ), 'NULL!?' ));
  4  EXCEPTION
  5    WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20001, 'Bad constant!');
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-20001: Bad constant!
ORA-06512: at line 5


SCOTT@10gXE> 
 



Previous Topic: date format related query
Next Topic: Find the Last Friday of the current month (PL/SQL)
Goto Forum:
  


Current Time: Thu Dec 08 19:59:18 CST 2016

Total time taken to generate the page: 0.07250 seconds