plsql_warnings: ensure quality and performance in plsql code

What are the things you can do before putting your code on production, apart form developing, testing, optimizing. Is there any thing to ensure more quality and performance. I say yes there is one very easy and simple step which I do and you should also do.

Oracle compiler is beautiful and very powerful. It not only tells you about compile time errors, but also it helps you to make your code optimal by finding potential run-time errors. With this article I am trying to cover most common plsql compiler warnings. Let look at one by one bur first we need to execute following to enable plsql warning.

SQL> alter session set plsql_warnings = 'enable:all';
Session altered.

1) PLW-05018: unit PRC_AUTHID_OMITTED omitted optional AUTHID clause; default value DEFINER used

SQL> create or replace procedure prc_authid_omitted
  2  is
  3  begin
  4     dbms_output.put_line('prc_authid_omitted');
  5  end;
  6  /
SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE PRC_AUTHID_OMITTED:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit PRC_AUTHID_OMITTED omitted optional AUTHID
         clause; default value DEFINER used

The AUTHID clause tells Oracle to execute plsql block with the invoker rights (CURRENT_USER), or with the Owner rights (DEFINER). AUTHID DEFINER is default.


2) PLW-06017: an operation will raise an exception

SQL> create or replace procedure prc_run_time_exception
  2  authid current_user
  3  is
  4     l_run_time_exception varchar(1);
  5  begin
  6     l_run_time_exception := 'Raise run time exception';
  7  end;
  8  /
SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE PRC_RUN_TIME_EXCEPTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4      PLW-06017: an operation will raise an exception

This is a very powerful feature to find any potential runtime exception.


3) PLW-06002: Unreachable code

SQL> create or replace procedure prc_unreachable_code
  2  authid current_user
  3  is
  4  begin
  5     if true then
  6             return;
  7     end if;
  8     dbms_output.put_line('unreachable code');
  9  end;
 10  /
SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE PRC_UNREACHABLE_CODE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/2      PLW-06002: Unreachable code

Very Simple plsql warning to identify Unreachable code.


4) PLW-05005: subprogram F_RETURN_WITHOUT_VALUE returns without value at line 8

SQL> create or replace function f_return_without_value(a number, b number)
  2  return varchar2
  3  authid current_user
  4  as
  5    c number;
  6  begin
  7    c := a+b;
  8  end;
  9  /
SP2-0806: Function created with compilation warnings

SQL> show errors
Errors for FUNCTION F_RETURN_WITHOUT_VALUE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05005: subprogram F_RETURN_WITHOUT_VALUE returns without
         value at line 8

This plsql warning is very useful and may cause the bug in your large function control flow where you returned without any value.


5) PLW-07203: parameter 'A' may benefit from use of the NOCOPY compiler hint

SQL> create or replace procedure prc_parameter_nocopy(a in out dbms_sql.number_table)
  2  authid current_user
  3  is
  4  begin
  5     dbms_output.put_line('Nocopy');
  6  end;
  7  /
SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE PRC_PARAMETER_NOCOPY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/32     PLW-07203: parameter 'A' may benefit from use of the NOCOPY
         compiler hint

As you can see, plsq compiler provides suggestion for optimization also.

If you want to study about all the plsql warning, please check Oracle documentation.


Related Posts:
- Bulk Collect with Limit Clause and %NOTFOUND
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- Being a PL/SQL Developer and Optimizing Code
- Query optimization tips for Oracle
- DBMS_PROFILER: How to analyze pl/sql performance
- How to Get Execution Plan and Statistics of SQL Query
- Why do I prefer COALESCE over NVL
- Append String to CLOB in Optimized way
- PLSQL Tuning: Bind Variables and execute immediate