A Common Exception in Oracle
|
|
|
Question / Problem
|
If an exception occures when you try to initialize a package level variable, it will be propogated unhandled out of package, even if the initilization section contans a Exception section. In this situation the PL/SQL engine registers the package as initialized and allows you to continue to reference varibale and subprograms in package.
|
Solution
|
Example
PACKAGE err
IS
FUNCTION little_name RETURN VARCHAR2;
END err;
PACKAGE BODY err
IS
name :="ABC";
FUNCTION little_name RETURN VARCHAR2
IS
BEGIN
return name;
END little_name;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE("TRAPPED THE ERROR");
RAISE;
END err;
Now if we try to run err.little_name function , the exception goes unhandled. But if we call this function no error is raised as the package has been marked as intialized and PL/SQL engine doesn't try to initialize it again.
But if you re-connect your schema the excpetion will be raised again.
Solution to this Problem:
Dont assign default values to variables in declaration section. Instead create an initializtion procedure to intialize all the default values.
PROCEDURE process_data
IS
name VARCHAR2(10);
PROCEDURE initialize
IS
BEGIN
name="ABCD";
END initialize;
BEGIN
intialize;
DBMS_OUTPUT.PUT_LINE(name);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE("TRAPPED THE ERROR");
RAISE;
END process_data;
|
Applies to |
|
Oracle 9i Sql
|
Rank It |
|
