Oracle 10/11g INVALID objects issue

From: Michael D O'Shea <woodwardinformatics_at_strychnine.co.uk>
Date: Thu, 28 Jan 2016 09:52:26 -0000
Message-ID: <b61b6e5973fc51cda71425b061a34c7c.squirrel_at_squirrelmail.strychnine.co.uk>

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_at_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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 28 2016 - 10:52:26 CET