Good morning everyone.
My question, or better put, a statement: I cannot rely on
ALL_OBJECTS.STATUS to determine whether a database package is an invalid
state or not, and I cannot rely on successful package method execution to
behave as expected.
I demonstrate the behaviour below. The 'real' production system logic is
of course much more complex.
Can anyone suggest a reliable approach to determine whether database
packages are in this combination valid/invalid state?
Regards
Mike
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE test_pkg AS
2 PROCEDURE doStuff0;
3 PROCEDURE doStuff10;
4 PROCEDURE doStuff11;
5 END test_pkg;
6 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg AS
2 x11 CONSTANT VARCHAR2(10) := 'abcdefghijk'; --11 characters
3 x10 CONSTANT VARCHAR2(10) := 'ABCDEFGHIJ'; --10 characters
4 PROCEDURE doStuff0 IS
5 BEGIN
6 NULL;
7 END;
8 PROCEDURE doStuff10 IS
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE(x10);
11 END;
12 PROCEDURE doStuff11 IS
13 BEGIN
14 DBMS_OUTPUT.PUT_LINE(x11);
15 END;
16 END;
17 /
Package body created.
SQL>
SQL> SHOW ERRORS
No errors.
SQL> SELECT object_type, status
2 FROM all_objects
3 WHERE object_name = 'TEST_PKG';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL>
SQL> EXEC dbms_output.enable(100000);
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC dbms_output.put_line('test')
test
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> EXEC test_pkg.doStuff0;
BEGIN test_pkg.doStuff0; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "XXX.TEST_PKG", line 2
ORA-06512: at line 1
SQL> EXEC test_pkg.doStuff0;
PL/SQL procedure successfully completed.
SQL> EXEC test_pkg.doStuff0;
PL/SQL procedure successfully completed.
SQL> SELECT object_type, status
2 FROM all_objects
3 WHERE object_name = 'TEST_PKG';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL>
SQL> ALTER PACKAGE test_pkg COMPILE;
Package altered.
SQL>
SQL> SELECT object_type, status
2 FROM all_objects
3 WHERE object_name = 'TEST_PKG';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL>
SQL> EXEC test_pkg.doStuff0;
BEGIN test_pkg.doStuff0; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "XXX.TEST_PKG", line 2
ORA-06512: at line 1
SQL>
SQL> EXEC test_pkg.doStuff0;
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC test_pkg.doStuff10;
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC test_pkg.doStuff11;
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
2 FROM nls_database_parameters
3 WHERE parameter LIKE '%CHARACTERSET%';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL> SELECT *
2 FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>CONN xxx/xxx@xxx
Connected.
SQL> -- another DB instance, different version, same behaviour
SQL>
SQL> SELECT *
2 FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
--
//www.freelists.org/webpage/oracle-l
