RE: Oracle 10/11g INVALID objects issue
Date: Thu, 28 Jan 2016 04:07:26 -0800
Message-ID: <000001d159c4$7494dcb0$5dbe9610$_at_comcast.net>
This has been the way Oracle works as what the compiler actually does relative to what it errors for versus what it will throw warnings for.
What you are looking for is
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
create or replace procedure y1 as
l1 constant varchar2(2) := 'AAA';
begin
dbms_output.put_line (l1);
end;
/
SQL> _at_y.txt
SP2-0804: Procedure created with compilation warnings
SQL> show errors
Errors for PROCEDURE Y1:
LINE/COL ERROR
1/1 PLW-05018: unit Y1 omitted optional AUTHID clause; default value DEFINER used 2/1 PLW-06017: an operation will raise an exception <===========================Your code will error on execution for line 2.. 4/1 PLW-06002: Unreachable code
Counter intuitive that something you compiled will bomb out and the compiler already knows it, but this is the way the compiler works. If you want a better explanation of the compiler then you will have to go deeper into the Dev manuals on binding versus execution and what each step does relative to checking lengths of the actual variable value.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba_at_comcast.net
View Matthew Parker's profile on LinkedIn
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Michael D O'Shea
Sent: Thursday, January 28, 2016 1:52 AM
To: oracle-l_at_freelists.org
Subject: Oracle 10/11g INVALID objects issue
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 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 28 2016 - 13:07:26 CET