Oddly enough, the trigger views in the data dictionary do not display whether or not a trigger is in a valid state. If a trigger is created with invalid PL/SQL, it is saved in the database but marked as INVALID. You can query the USER_OBJECTS or ALL_OBJECTS views to determine this status, as shown here:
SQL> CREATE OR REPLACE TRIGGER invalid_trigger 2 AFTER DDL ON SCHEMA 3 BEGIN 4 NULL 5 END; 6 / Warning: Trigger created with compilation errors. SQL> SELECT object_name, 2 object_type, 3 status 4 FROM user_objects 5 WHERE object_name = 'INVALID_TRIGGER'; OBJECT_NAME OBJECT TYPE STATUS------------- ----------- -------INVALID_TRIGGER TRIGGER INVALIDChapter 19. Managing PL/SQL Applications
Writing the code for an application is just one step toward putting that application into production and then maintaining the code base. It is not possible within the scope of this book to fully address the entire lifecycle of application design, development, and deployment. We do have room, however, to offer some ideas and advice about the following topics:
Managing and analyzing code in the database
When you compile PL/SQL programs, the source code is loaded into the data dictionary in a variety of forms (the text of the code, dependency relationships, parameter information, etc.). You can therefore use SQL to query these dictionaries to help you manage your code base.
Protecting stored code
Oracle offers a way to "wrap" source code so that confidential and proprietary information can be hidden from prying eyes. This utility is most useful to vendors who sell applications based on PL/SQL stored code.
Using native compilation
Beginning with Oracle9i, PL/SQL source code may optionally be compiled into native object code that is linked into Oracle. Native compilation can result in significant improvement in overall application performance (its impact is felt in compute-intensive programs, but does not affect SQL performance).
Testing PL/SQL programs
This chapter offers suggestions for PL/SQL program testing based on the open source unit testing framework, utPLSQL.
Debugging PL/SQL programs
Many development tools now offer graphical debuggers based on Oracle's DBMS_DEBUG API. These provide the most powerful way to debug programs, but they are still just a small part of the overall debugging process. This chapter will also explore some of the techniques and (dare I say) philosophical approaches you should utilize to debug effectively.
Tuning PL/SQL programs
It is not within the scope of this book to offer comprehensive tuning recommendations for PL/SQL code. However, I will offer a roundup of some of the more useful and generally applicable tuning tips, along with instructions for how you can analyze your program's execution with built-in profiling and tracing utilities.
