PLSQL_WARNINGS

5 Sep

There are Three Types of Warning Options:

ENABLE – Allows you to enable warnings. You can enable based on the type of warning (all, severe, informational, or performance) or based on a numeric value for the warning messages.
DISABLE – Allows you to disable warnings. You can disable based on the type of warning (all, severe, informational, or performance) or based on a numeric value for the warning messages.
ERROR – Allows you to define specific warnings as errors instead, which will halt compiles. You can define warnings that should be treated as errors based on the type of warning (all, severe, informational, or performance) or based on a numeric value for the warning messages.

There are Three Types of Warning Levels:

Severe

Informational

Performance

PL/SQL Warnings can be set at system level or session level on SQL prompt (It works in Oracle 10g and 11g):

SQL> ALTER SESSION SET PLSQL_WARNINGS=’enable:severe’;

SQL> ALTER SESSION SET PLSQL_WARNINGS=’enable:(6000, 6010)’;

SQL> ALTER SYSTEM SET PLSQL_WARNINGS=’enable:performance‘;

SQL> ALTER SYSTEM SET PLSQL_WARNINGS=’enable: informational’ ;

SQL>:ALTER SESSION SET PLSQL_WARNINGS=’DISABLE:ALL’;

Oracle has classified the warning message ranges (code PLW) as follows:

• PLW-5000 to 5999 are severe warnings.
• PLW-6000 to 6249 are informational warnings.
• PLW-7000 to 7249 are performance-related warnings.

Enhancements in Oracle 11g PL/SQL

The scale of change in PL/SQL seems to have slowed down. Previous versions of Oracle (major version I mean) normally had a huge amount of new functionality. That was sort of a hallmark of the upgrade. 11g seems a little lame in that (at least as far as PL/SQL goes). The goal seems to be to improve PL/SQL performance invisibly. That they do. They also add a little bit of chewy goodness just not as much as I have been used to.

1. DML triggers are up to 25% faster. This especially impacts row level triggers doing updates against other tables (think Audit trigger).

2. Fine Grained Depenancy Tracking (FGDT?). This means that when you add a column to a table, or a cursor to a package spec, you don’t invalidate objects that are dependant on them. Sweet!

3. Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library.

4. New Data Type: simple_integer. Always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER.

 

5. Intra-unit inlining. In C, you can write a macro that gets inlined when called. Now any stored procedure is eligible for inlining if Oracle thinks it will improve performance. No change to your code is required. Now you have no reason for not making everything a subroutine!

6. SQL and PL/SQL result caching., you read that right. Now there is a result cache that can store actual results from queries and stored procedures. Later calls are almost instaneous.

7. Compound triggers. How would you like a trigger that is a before, after, row and statement all in one? And it maintains its statements between each? Now you have it.

8. Dynamic SQL. DBMS_SQL is here to stay. It’s faster and is being enhanced. DBMS_SQL and NDS (Native Dynamic SQL) can now accept CLOBs (no more 32k limit on NDS). A ref cursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supprts user defined types and bulk operations.

9. FGAC for UTL_SMTP, UTL_TCP and UTL_HTTP. You can define security on ports and URLs.

10. Support for SUPER references in Oracle Object Type methods.

11. Read only tables.

12. Specify Trigger firing order.

13. Compiler warning for a when others with no raise.

14. Still no identity column!

15. Continue statement is added. Personally, I find the continue to be too much like a goto.

16. You can now used named notation when calling a stored procedure from SQL.

source : http://www.oracle.check-spot.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: