PLSQL Warning Messages in Oracle SQL Developer

If you have problems in your PL/SQL program that prevent compilation and execution of said program, then that is reported back as an ‘error.’

It could be as simple as missing a semicolon –

CREATE OR REPLACE
FUNCTION "COUNTRIES_ROWS_BACK" 
   RETURN sys_refcursor
IS
   l_curvar   sys_refcursor;
BEGIN
   OPEN l_curvar FOR 'SELECT * FROM countries'
 
   RETURN l_curvar;
END COUNTRIES_ROWS_bACK;

When you run this through SQL Developer, an error message is returned a la

Well that’s an easy fix. We’re all done! Or are we?

Enter the world of PL/SQL compile-time warnings:

To make your programs more robust and avoid problems at run time, you can turn on checking for certain warning conditions. These conditions are not serious enough to produce an error and keep you from compiling a subprogram. They might point out something in the subprogram that produces an undefined result or might create a performance problem.

To enable those in SQL Developer, go to Tools-Preferences-Database-PL/SQL Compiler.

You can enable the warnings, and you can even bump the warnings up to the level of an error. When you do this, you force the compilation to ‘break.’ Fun, right? Maybe you could employe this ‘tip’ for next year’s April Fool’s Day if you have a very good working relationship with your co-workers – but just remember you didn’t get that idea from me 🙂

Let’s take a look at how my ‘perfectly normal’ program is evaluated with warnings enabled.

Did you catch the yellow squiggly lines? Warnings show up in yellow as opposed to red for errors.

Quick BONUS Tip: Configure the Yellow & Red Wavy Lines

Can’t make out the yellow warning squiggles? Turn ’em orange, or make them dotted lines or heavy underlines. It’s all in the preferences.

Let’s take a quick break. If you’ve got your earphones on and you don’t mind a bit of profanity, then it’s time to harken back to 1998 before the entire software world had seen Office Space. ‘PC Load letter?!?’

Instead of cursing ‘PC Load Letter,’ you might be having similar thoughts regarding
PLW-05018 – what am I doing wrong?

Notice the ‘W‘ on the error message – it’s actually a ‘warning.’ Maybe it’s bad, maybe it’s not. You need to investigate.

In this case Oracle is warning us that the scope of the program will execute as the program’s owner, not as the current user. AUTHID CURRENT_USER allows you to let the account who is executing the program define what schema the reference objects in the code will act upon. This is not a blog about AUTHID, but there are many, many out there.

Remember, you can turn these warnings on and off, and you can also escalate them up to the ERROR level. In a development environment I think I’d at least want to see the warnings.

Note: you can define how to treat individual warnings. If you agree with a particular set, or believe so strongly in avoiding it, you can set at an individual level for it to be treated as an error.

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE',
                                 'ERROR:06002';

You would need to add this to your logon script defined in the SQL Developer preferences as the current options are an all or nothing proposition.