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> @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@xxxxxxxxxxx
View Matthew Parker's profile on LinkedIn
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael D O'Shea
Sent: Thursday, January 28, 2016 1:52 AM
To: oracle-l@xxxxxxxxxxxxx
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@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
--
//www.freelists.org/webpage/oracle-l
