Oracle Illustrated

Feb 21 2010   6:47AM GMT

Migrating from 9i to 11g – Compile time warnings

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Oracle warnings available from Oracle 10g. To avoid problems at run times and to have a clean code we can turn on checking some compile time warnings.

Syntax-
PLSQL_WARNINGS = ‘value_clause’ [, ‘value_clause’ ] …
value_clause::=
{ ENABLE | DISABLE | ERROR }:
{ ALL
| SEVERE
| INFORMATIONAL
| PERFORMANCE
| { integer
| (integer [, integer ] …)}}
If the value is set to ERROR then, it will not be compiled successfully. If the value is set to ENABLE then, it will be compiled but with compilation errors.
To re-compile an existing procedure

ALTER PROCEDURE COMPILE PLSQL_WARNINGS=’ENABLE:ALL’
ALTER SESSION / SYSTEM SET PLSQL_WARNINGS=’ENABLE:ALL';
ALTER SESSION / SYSTEM SET PLSQL_WARNINGS=’DISABLE:ALL';
ALTER SESSION / SYSTEM SET PLSQL_WARNINGS=’ENABLE:PERFORMANCE';
ALTER SESSION / SYSTEM SET PLSQL_WARNINGS=’DISABLE:PERFORMANCE';
ALTER SESSION /SYSTEM SET PLSQL_WARNINGS=’ERROR:ALL';
ALTER SESSION / SYSTEM SET PLSQL_WARNINGS=’ENABLE:SEVERE’, ‘DISABLE:PERFORMANCE’,

ALTER SESSION SET PLSQL_WARNINGS=’ERROR:ALL';

CREATE OR REPLACE PROCEDURE DUMMY
AS
v_count varchar2(10);
BEGIN
v_count := 5;
END;
/
Warning: Procedure created with compilation errors.
OUTPUT

SELECT STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME = ‘DUMMY’

INVALID

LINE/COL ERROR
——– —————————————————————–
5/5 PLW-07206: analysis suggests that the assignment to ‘V_COUNT’ may
be unnecessary

ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL';

SP2-0804: Procedure created with compilation warnings

OUTPUT

SELECT STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME = ‘DUMMY’

VALID
ALTER SESSION SET PLSQL_WARNINGS=’DISABLE:ALL';

CREATE OR REPLACE PROCEDURE DUMMY
AS
v_count varchar2(10);
BEGIN
v_count := 5;
END;
/

ALTER PROCEDURE dummy COMPILE PLSQL_WARNINGS=’ENABLE:ALL’

SELECT STATUS FROM ALL_OBJECTS WHERE OBJECT_NAME = ‘DUMMY’

VALID

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: