Sunday, September 18, 2011

Laying down the law over warnings

Tim commented on yesterday's post by saying "I would not be overly disturbed if Oracle made this clause [DEFINER/INVOKER RIGHTS] mandatory in future."


With such a broad range of software that runs against Oracle, some of it from third party vendors, I don't see Oracle making changes that would break existing applications.


But they could and have allowed for you to make it mandatory yourself. Perhaps they even make it so in their internal development.


Start with a simple procedure:



create or replace procedure do_it is
begin
   dbms_output.put_line('Humbug');
end do_it;
/


If we compile it with all the warnings enabled, we get the "Missing AUTHID clause" warning.



XE> alter session set plsql_warnings='ENABLE:ALL';
XE> alter procedure do_it compile;

SP2-0805: Procedure altered with compilation warnings

XE> select text, attribute from user_errors where name = 'DO_IT';

TEXT                                                                             ATTRIBUTE
-------------------------------------------------------------------------------- ---------
PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used WARNING
XE> exec do_it
Humbug

But we can use the PLSQL_WARNINGS setting to make enforcement more "Judge Dredd".

XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','ERROR:05018';
Warning: Procedure altered with compilation errors.

XE> select text, attribute from user_errors where name = 'DO_IT';
TEXT                                                                             ATTRIBUTE
-------------------------------------------------------------------------------- ---------
PLS-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used ERROR

XE> exec do_it
BEGIN do_it; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object GARY.DO_IT is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Rather than just enabling the warning, we can treat it as an error, effectively making the clause mandatory in our environment. Obviously it doesn't make sense for this particular warning to be set at the program unit level. It might make sense for other warnings to be set as errors at the schema or database level, and then individual overrides can be set for exceptional program units.



Saturday, September 17, 2011

WARNING, WARNING, DANGER, DANGER !

Yes, the catch-cry from the "Lost in Space" Robot. But I'm more interested in PL/SQL Warnings.


My new contract is working with an Oracle 11gR2 database. [Aside: It's great using up-to-date versions and with the new 11.2.0.2 version of XE, it's almost like Christmas.]


With new versions come new features and several new PL/SQL compiler warnings have been added to 11gR2. One of those is "PLW-05018: unit <program unit> omitted optional AUTHID clause; default value DEFINER used". This message was described by Steven Feuerstein as "one of those "nuisance" warnings that will pop up on almost every one of our compiles, because so few developers use AUTHID (for invoker rights) and legacy code certainly does not include this clause"


This description was in the answer to one of the Championship playoff questions which asked players to look at some code and give the number of warnings that it would generate. Most players underestimated the answer, partly because the number of warnings is going up with each version.


For most developers this warning is pretty much superfluous. It is pretty rare to be developing DEFINER rights code for applications. However utilities and some of the built-in schemas (eg MDSYS etc) might have elevated privileges and exposing that power through a procedure is something that needs careful consideration.



It is possible to enable and disable specific warnings. Personally I use the fine-grained option available on the command line through ALTER SESSION. 

ALTER SESSION SET PLSQL_WARNINGS=  
    'ENABLE:ALL','DISABLE:07203','DISABLE:05018','DISABLE:06009';

The DBAs can set these using ALTER SYSTEM, or might set up LOGON triggers on specific schemas to set appropriate values. You may also want a different level of warnings in your development databases that your testing and production databases.


It may also be possible to set your preferred options in your IDE. However looking at "SQL Developer", the options in the GUI to enable/disable these warnings are pretty coarse-grained, so I wouldn't recommend this solution.


SQL Developer options for setting PL/SQL Warnings

Another location for defining the PLSQL_WARNINGS level is on the object itself. 



XE> create or replace procedure do_it (i_text IN OUT VARCHAR) is
  2  begin
  3    dbms_output.put_line(i_text);
  4  end do_it;
  5  /


XE> alter session set plsql_warnings='ENABLE:ALL';
Session altered.
XE> alter procedure do_it compile;
SP2-0805: Procedure altered with compilation warnings
XE> select text from user_errors where name = 'DO_IT';
TEXT
-------------------------------------------------------------------------------
PLW-07203: parameter 'I_TEXT' may benefit from use of the NOCOPY compiler hint
PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used





This simple procedure gives two errors when all warnings are enabled. If I then recompile it with those warnings disabled, it comes out clean.


XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','DISABLE:07203','DISABLE:05018';
Procedure altered.
XE> select text from user_errors where name = 'DO_IT';
no rows selected



If I do a straight compile on that procedure afterwards, it will use the current session settings (which will be inherited from the system settings if not set specifically).


XE> alter procedure do_it compile;
SP2-0805: Procedure altered with compilation warnings
XE> select text from user_errors where name = 'DO_IT';
TEXT
--------------------------------------------------------------------------------
PLW-07203: parameter 'I_TEXT' may benefit from use of the NOCOPY compiler hint
PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used

However you can specify 'REUSE SETTINGS' so that the explicitly define settings are retained. 


XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','DISABLE:07203','DISABLE:05018';
Procedure altered.
XE> alter procedure do_it compile reuse settings;
Procedure altered.
XE> select text from user_errors where name = 'DO_IT';
no rows selected


My recommendations would be for the development environment to have at least some warnings enabled, either at the system level or for specific schemas. Then, if individual modules need differing settings, these can be specified and retained (assuming your IDE is up to the job).


For testing environments, having warnings enabled might be a useful step as this may be the first 'independent' compilation of the code. A second set of eyes reviewing the warnings could be a basic 'code review'. 


I don't see a benefit in warnings in production environments. But I don't see a real downside either.


Friday, September 02, 2011

Oracle 11gR2 Express Edition is FREE

I've just wiped my Oracle XE 10g Edition on my home PC and replaced it with a fresh clean 11g Express Edition.

For those who haven't met XE before, it is the free edition of Oracle.

It allows for up to 11Gb of data (up from 4Gb in the old XE) and will use 1 CPU and 1 GB of RAM. The server can have more power, but it won't get used by the database. 

You can use it for free (developing, testing, running production applications and even give training using it).

You don't get some of the snazzy Enterprise Edition features. No FLASHBACK DATABASE or FLASHBACK TABLE. No PL/SQL Function Result Cache. No Diagnostic or Tuning packs. Or table compression.

The big 'Standard Edition' feature I can see that is missing is Java in the database.

It comes with Apex 4.0 pre-installed.