(17) On Triggers

Triggering

Introduction

DDL Triggers

DML Triggers

Compound Triggers

INSTEAD-OF Triggers

System or Database Event Triggers

Exception Handling in Triggers

Design Guidelines

Trigger Restrictions

Triggers and Data Transfer Utilities

Data Dictionary Views on Triggers

Introduction

• Named PL/SQL unit. Can be enabled or disabled, but cannot be explicitly invoked.
• Trigger is created on or defined on the item (to which it will be "attached"): table, view, schema or database.
• Firing criteria is based on a triggering event (DML, DDL, System) and on a timing specification (before, after, instead of). A conditional clause (WHEN) may also be used to further specify the triggering rules.
• Triggers do not accept arguments.
• Triggers can be written in PL/SQL or JAVA.
• Starting on Oracle 11g, triggers can now be created in the disabled state.

Triggers: what for?
Customization of database management; centralization of some business or validation rules; logging and audit.

• Overcome the mutating-table error.
• Maintain referential integrity between parent and child.
• Generate calculated column values
• Log events (connections, user actions, table updates, etc)
• Gather statistics on table access
• Modify table data when DML statements are issued against views
• Enforce referential integrity when child and parent tables are on different nodes of a distributed database
• Publish information about database events, user events, and SQL statements to subscribing applications
• Enforce complex security authorizations: (i.e. prevent DML operations on a table after regular business hours)
• Prevent invalid transactions
• Enforce complex business or referential integrity rules that you cannot define with constraints
• Control the behavior of DDL statements, as by altering, creating, or renaming objects
  
when they change data in a view>
• Audit information of system access and behavior by creating transparent logs

(but, however, nonetheless, take heed:),

• if the trigger code turns out to be very(?) long, you will more likely have better performance using a stored procedure instead. In fact, a trigger cannot be larger than 32Kb (because stored on LONG column). If you need to write something longer, use a stored procedure instead.
• You can’t control the sequence of or synchronize calls to triggers, and this can present problems if you rely too heavily on triggers
• A trigger can call a SQL statement that in turn fires another trigger: The number of cascading triggers is limited to 32, after which an exception is thrown. (11g and earlier)

Triggers: How much is too much?

• DML statements on tables with DML Triggers are likely to have decreased perform.
• You may choose to disable triggers before loading data. Of course the cost to this is the work you'll have to perform latter what the disabled triggers did not do.
• If the task is complex, you may spread it across multiple triggers. However, this will make it maintenance more difficult, since it is likely to make the entire process harder to follow later.
• Triggers may get disabled by accident: For example, DDLs on objects touched by a trigger may render it unusable. If you don't catch this, you may end up with missing/corrupt data.

Five types of Triggers
DDL Triggers	On CREATE, ALTER, DROP Useful to control or monitor DDL statements. An instead-of create table trigger allow for: Ensuring that table creation meets development standards (i.e. proper storage or partitioning clauses. Monitor poor programming practices (i.e. programs create and drop temporary tables rather than use Oracle collections. Temporary tables can fragmentdisk space and degrade database performance over time.
DML Triggers	Statement-level or row-level Audit, check, save, and replace values before they are changed. Automatic numbering of numeric primary keys (through row-level trigger).
Compound Triggers	Act as statement- and row-level triggers. Lets you capture information at four timing points: (a) before the firing statement; (b) before each row change from the firing statement; (c) after each row change from the firing statement; and (d) after the firing statement. Audit, check, save, and replace values before they are changed when you need to take action at both the statement and row event levels.
Instead-of Triggers	Enable you to stop performance of a DML statement and redirect the DML statement. Often used to manage how you write to non-updatable views: They apply business rules and directly insert,update, or delete rows in tables that define updatable views. Alternatively, they insert, update, or delete rows in tables unrelated to the view.
System Database event Triggers	Fire when a system activity occurs in the database (i.e. logon and logoff). Useful for auditing information of system access. (You can track system events and map them to users).

Constraints	Apply to OLD and NEW data. Easier to write and less error-prone.
Triggers	Apply only to NEW data. Can enforce complex business rules. Enforce ref integrity on distributed databases.

Using DML triggers

Using Compound triggers (11g only)

Using triggers on object tables

You can create triggers on object tables. In this case, the trigger can reference the pseudocolumn OBJECT_VALUE. Check an example here.

Using INSTEAD OF triggers

INSTEAD OF triggers are created on views. This allow DML statements to be issued against non-updatable views. Check an example here.

Privileges required to use Triggers
- CREATE TRIGGER: For your own objects. - CREATE ANY TRIGGER + ALTER ANY TABLE. - EXECUTE:to fire triggers on other schemas

Trigger Design Guidelines

• Do not create triggers that duplicate database features. For example, do not create a trigger to reject invalid data if you can do the same with constraints.
• Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).
  
  For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in a BEFORE statement trigger.
• If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETE statement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package.
• Do not create recursive triggers. The trigger fires recursively until it runs out of memory.
  
• If you create a trigger that includes a statement that accesses a remote database, then put the exception handler for that statement in a stored subprogram and invoke the subprogram from the trigger.
• Use DATABASE triggers judiciously. They fire every time any database user initiates a triggering event.
  
• Only committed triggers fire. A trigger is committed, implicitly, after the CREATE TRIGGER statement that creates it succeeds.

Trigger Restrictions

• Maximum Trigger Size
• Max 32Kb. If needed, you can move code into functions, procedures or packages. In this case, the code could also be reused. stored modules can also be wrapped.
• If the logic for your trigger requires much more than 60 lines of PL/SQL source text, then put most of the source text in a stored subprogram and invoke the subprogram from the trigger
• DCL and DDL Restrictions
• Only an autonomous trigger can run TCL or DDL statements
• Nonsystem trigger bodies can’t contain DDL statements. They also can’t contain Transaction Control Language (TCL) commands, like ROLLBACK, SAVEPOINT,or COMMIT.
• A trigger cannot invoke a subprogram that runs transaction control statements, because the subprogram runs in the context of the trigger body.
• If you declare a trigger as autonomous, nonsystem trigger bodies can contain Data Control Language commands because they don’t alter the transaction scope.
• To enable a trigger to work outside the scope of a triggering statement you use include in its DECLARE block: PRAGMA AUTONOMOUS_TRANSACTION;
• A larger problem with SQL statements exists with remote transactions. If you call a remote
  schema-level function or procedure from a trigger body, it is possible that you may encounter a
  timestamp or signature mismatch. A mismatch invalidates the trigger and causes the triggering
  SQL statement to fail.
• LONG and LONG RAW Datatypes
• The LONG and LONG RAW datatypes are legacy components. Migrate out of them.
• A trigger cannot declare a variable of the LONG or LONG RAW data type.
• You may, however, insert into a LONG or LONG RAW column when the value can be converted CHAR or VARCHAR2.
• Row-level triggers cannot use a :new,:old or parent with a LONG or LONG RAW column.
• Triggers will fail if try to access a mutating table.
• Oracle 11g has relaxed some mutating table restrictions

Triggers and data transfers
These utilities may fire triggers: SQL*LOoader (sqlldr), Data Pump Import (impdp) and Original import (imp)
SQL*Loader (sqlldr): - During a SQL*Loader conventional load, INSERT triggers fire. - Before a SQL*Loader direct load, triggers are disabled.
Data Pump Import (impdp): - If a table to be imported does not exist on the target database, or if you specify TABLE_EXISTS_ACTION=REPLACE, then impdp creates and loads the table before creating any triggers, so no triggers fire. - If a table to be imported exists on the target database, and you specify either TABLE_EXISTS_ACTION=APPEND or TABLE_EXISTS_ACTION=TRUNCATE, then impdp loads rows into the existing table, and INSERT triggers created on the table fire.
Original Import (imp): - If a table to be imported does not exist on the target database, then imp creates and loads the table before creating any triggers, so no triggers fire. - If a table to be imported exists on the target database, then the Import IGNORE parameter determines whether triggers fire during import operations: - If IGNORE=n (default), then imp does not change the table and no triggers fire. - If IGNORE=y, then imp loads rows into the existing table, and INSERT triggers created on the table fire.

(ref) On Data Types

[ Oracle PL/SQL Data Types ]

(a) Scalar data types

Can have subtypes. A data type and its subtypes comprise a data type family.

PL/SQL scalar data types are: The SQL data types BOOLEAN PLS_INTEGER BINARY_INTEGER REF CURSOR User-defined subtypes More information on Oracle and SQL Data types...

• The PL/SQL data types include the SQL data types. Some have larger max sizes:
• CHAR, NCHAR, RAW, VARCHAR2, NVARCHAR2, LONG, LONG RAW, BLOB, CLOB, NCLOB
• PL/SQL define subtypes for BINARY_FLOAT: SIMPLE_FLOAT and BINARY_DOUBLE: SIMPLE_DOUBLE

CHAR vs. VARCHAR2

Predefined Subtypes	CHAR: CHARACTER (PL/SQL and SQL) VARCHAR2: VARCHAR (PL/SQL and SQL), STRING (PL/SQL alone)
Memory allocation	CHAR(MaxSize): During compile MaxSize is allocated in memory. VARCHAR2(MaxSize): If MaxSize < 4k: full allocation at compile time. (for performance) VARCHAR2(MaxSize): If MaxSize >= 4k: allocation enough for the actual value during run time. (for efficient memory use)
Blank Padding	CHAR will do blank-pads to the maximum size. Information loss. VARCHAR2 will NOT do blank-pads to the maximum size. No information loss.
Value Comparisons	If one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2, nonpadded comparison semantics apply; otherwise, blank-padded semantics apply

LONG and LONG RAW
Supported for backward compatibility only.

• Instead of LONG: use VARCHAR2(32760), BLOB, CLOB, NCLOB
• Instead of LONG RAW: use BLOB
• From LONG variable => LONG column: ANY Value.
• From LONG RAW variable => LONG RAW column: ANY Value.
• From LONG (or LONG RAW) column => LONG (LONG RAW) variable: 32Kb maximum.
• TRIGGER restrictions: (a) Cannot declare LONG or LONG RAW. (b)Cannot use correlation name NEW or PARENT with LONG or LONG RAW. (c) Stmt can reference LONG or LONG RAW column ONLY if the column data can be converted to CHAR or VARCHAR2.

ROWID and UROWID variables (more on ROWIDs here)

• ROWIDTOCHAR and CHARTOROWID: Conversion functions.
• UROWID: more versatile. Compatible with logical, physical and foreign rowids.
• DBMS_ROWID package:
  The DBMS_ROWID package lets you create ROWIDs and obtain information about ROWIDs from PL/SQL programs and SQL statements. You can find the data block number, the object number, and other ROWID components.
  DBMS_ROWID is intended for upgrading from Oracle 7 to 8.X.

About Rowids
rowids are used in the construction of indexes. In addition to this,

• Rowids are the fastest means of accessing particular rows.
• Rowids provide the ability to see how a table is organized.
• Rowids are unique identifiers for rows in a given table.

ROWID Pseudocolumn

• Every Oracle table has a pseudocolumn named ROWID. It value, however, is not actually stored in the table.
• You can select from pseudocolumns, but you cannot insert, update, or delete their values.
• Values of the ROWID pseudocolumn are strings representing the address of each row.

-- Query to show the extended rowid
SQL> select rowid from hr.employees where employee_id=100;

ROWID
------------------
AAAC9EAAEAAAABXAAA 

• ROWID is not physically stored in the database.It is inferred from the file and block address of the data.
• An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row.

• Extended rowid: four-piece format.
• AAAC9E: The data object number identifies the segment. A data object number is assigned to every database segment.
• AAE: The data file number: Tablespace-relative. Identifies the file that contains the row.
• AAAABX: The data block number Identifies the block that contains the row. Relative to the datafile.
• AAA: The row number: identifies the row in the block.

When does a ROWID change?

• If row movement is enabled: ROWID can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.
• If row movement is disabled: ROWID can change if the row is exported and imported using Oracle Database utilities

• You can create table column using the ROWID data type. However, storing a rowid with the intent of using it latter, for example, as an audit trail record, may not be a good idea:
• rowid may change as the result of an alter table or after partition movement (for partitioned tables).

(more on ROWIDs here)

Boolean

• Valid values: {TRUE | FALSE | NULL}
• SQL has no equivalent to BOOLEAN. Thus you cannot:
• Assign a BOOLEAN value to a database table column
• Select or fetch the value of a database table column into a BOOLEAN variable
• Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL function invoked from a SQL statement
• Cannot pass a BOOLEAN value to the DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUTLINE subprogram.

PLS_INTEGER and BINARY_INTEGER
User-Defined PL/SQL Subtypes

(2) Introduction to PL/SQL

Overview of PL/SQL

Advantages	Main features	Architecture
Tight Integration with SQL	Error handling	PL/SQL Engine
High Performance	Blocks	PL/SQL Units
High Productivity	vars and consts	Compilation Parameters
Portability	subprograms
Scalability	packages
Manegeability	triggers
Support OO Programming	I/O
Suppport Dev Web Apps	Data Abstraction
Suppport Dev Server Pages	Control Stmts
	Conditional compilation
	Process query result one row at a time

Advantages
Integration with SQL
- You can use all SQL data manipulation, cursor control, transaction control stmts
- You can use all SQL functions, operators, and pseudocolumns.
- PL/SQL fully supports SQL data types.(i.e. VARCHAR2 <--> VARCHAR2)
- Attributes: %TYPE and %ROWTYPE (define a var/record in PL/SQL with the type of a column/row)
- run a query and process the resultset one row at a time
- supports static SQL and dynamic SQL.

High Performance
- Reduced network traffic: Blocks of SQL stmts can be send at once and individual stmts processed by the SQL Engine in the database.
- SQL statement reuse: SQL stmts can be reused by the DB (less parsing takes place) each time the same code runs. (PL/SQL compiler turns the variables in the WHERE and VALUES clauses into bind arguments)
- Subprograms stored in executable form: Cached and shared among users. Reduced parsing/compiling. One network call might lead to many SQL stmts in the server (less network traffic).
- Optimizer: rearrange code for better performance.

Scalability
- If you can expand server power, concentrate code on the server to reduce network and use shared memory.
- Oracle Connection Manager can multiplex network connections.

Manageability
- One central copy of the program, rather than one in each client.

Support for OO Programing
- Through the use of Abstract data types.

Support for Web Applications
- can generate HTML/XML from the DB with PL/SQL
- Using PL/SQL instead of CGI Perl Scripts you can use DML statements, dynamic SQL, and cursors, and you also eliminate the overhead of forking a new CGI process to handle each HTTP request.

PL/SQL Gateway

enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener. mod_plsql is one implementation of the PL/SQL Gateway. It is a plug-in of Oracle HTTP Server. It enables Web browsers to send HTTP requests in the form of URLs that include parameter values to be passed to a stored subprogram. mod_plsql mod_plsql (a) translates the URL; (b) invokes the subprogram and (c) returns the output (html) to the client. Advantages over the embedded PL/SQL gateway (DBMS_EPG): Can run in an Oracle HTTP Server in front of a firewall while the database remains behind the firewall. The embedded gateway (DBMS_EPG) cannot take this configuration. In which situations could this configuration be preferred? The embedded gateway does not support mod_plsql features such as dynamic HTML caching, system monitoring, and logging in the Common Log Format. Embedded PL/SQL Gateway Runs in the XML DB HTTP Listener in the DB. Provides core features of mod_plsql. Configured with the DBMS_EPG package in the PL/SQL Web Toolkit. Does not require the HTTP Server powered by Apache. For a browser to have access to a PL/SQL app: (1) a Database Access Descriptor (DAD) must be created and (2) mapped to a virtual path. DAD: set of configuration values used for database access. DAD: represented as a servlet in XML DB HTTP Server. Virtual path mapping: makes the application accessible under a virtual path of the XML DB HTTP Server.

SQL Web Toolkit

PL/SQL Web Toolkit is a set of PL/SQL packages that provides a generic interface to use stored subprograms invoked by mod_plsql at run time. Web Toolkit API enables stored subprograms to perform actions such as: Obtain information about an HTTP request Generate HTTP headers such as content-type and mime-type Set browser cookies Generate HTML pages

Support for the Development of Server Pages

PL/SQL Server Pages (PSP) are server-side scripts that include dynamic content, including the results of SQL queries, inside web pages. You can author the web pages in an HTML authoring tool and insert blocks of PL/SQL code.

Simple Server Page. Example extracted from the Oracle manual.

To compile and load the server page, use the loadpsp utility:

Why use PSP?

• Easier way to create web pages that include DB-generated content. Use your preferred tool to develop web pages and embed PL/SQL.
• Writing out HTML content line by line: PSP is more convenient than HTP and HTF packages.
• Client does not need to process any special script tags besides plain HTML. All processing is done on the DB server. Supports all browsers.
• Less network traffic. Less round-trips to the DB.
• Follow a rapid, iterative development process. Central control of the software. Client needs only a browser.

Main Features

Blocks, Subprograms and other Constructs
Oracle supports two types of PL/SQL Blocks: named and anonymous.

named	Can be stored in the DB (procedures, funtions, packages) May be dynamically created, allocated a name and executed, but not stored in the DB.
anonymous	Can be dynamically created and executed, but cannot be stored in the database. Are treated as single SQL statements Are compiled each time they are loaded into memory. Compilation stages: Syntax checking (syntax check, parse tree gen) => Semantic check (Type checking; parse tree refined) => Code generation

(a) Categories of constructs that can be created at the server side:

• Anonymous blocks – PL/SQL blocks without a name. Not stored in the DB. Run and gone.
• Stored Procedures and Functions – Named PL/SQL blocks also known as subprograms
• Stored Packages – PL/SQL constructs that are used to group related objects
• Database Triggers – PL/SQL blocks associated with a table, a view, a schema, or the database
• Object Types - (Abstract Data Types - ADTs) – User-Defined composite data types

<< label >> (optional)
DECLARE -- Declarative part (optional)
-- Declare local types, vars, & subprograms

BEGIN -- Executable part (required)
-- At least one statement
-- You may use a NULL stmt: (BEGIN NULL; END;)

[EXCEPTION -- Exception-handling part (optional)
-- handlers for exceptions raised in exec part]
END;

A block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. It can have a label.

(b) In the client side (Forms, Developer, etc), you can create constructs:

• Anonymous blocks
• Application Procedures and Functions
• Application Packages
• Application Triggers
• Object Types (Abstract Data Types - ADTs)

named block

CREATE OR REPLACE <block type> <block name> IS | AS -- Mandatory . . . -- Declarative section -- (Type, Name[P|F], Parameters, RETURN) BEGIN . . . -- Executable section EXCEPTION . . . –- Exception-handling section (optional) END; -- Marks the end of the block

<block type> can be: Procedure, Function, Package, Trigger

Why use Subprograms? (Benefits)

Performance

Integrity/Security

Maintenance

Clarity

(a) Improved Performance

• Multiple statements sent to the Server in a group.=> Reduced network traffic.
• Subprgrams (named) are compiled once (when created).: Avoid re-parsing at execution time.
• Compiled code placed into the shared pool, thus shared by multiple sessions.

(b) Data Security and Integrity

• Inherit the privileges of the owner by default. (Control indirect access to objects)
• Atomicity: Either all statements complete, or none will.

(c) Easy to Maintain

• Subprograms are easily shared by multiple apps.
• change in the DB Objects require change on the subprogram only.

(d) Increased code clarity

• More readability: each section of the subprogram block is defined separately.
  (i.e., all exceptions are handled in the exception-handling section).

Input/Output
Besides the use of SQL Statements, PL/SQL has also a number of I/O-processing packages:

DBMS_OUTPUT	Lets PL/SQL blocks, subprograms, packages, and triggers display output. Especially useful for displaying PL/SQL debugging information
HTF	Has hypertext functions that generate HTML tags (i.e., HTF.ANCHOR => anchor tag ).
HTP	Has hypertext procedures that generate HTML tags.
DBMS_PIPE	Lets two or more sessions in the same instance communicate.
UTL_FILE	Lets PL/SQL programs read and write OS files.
UTL_HTTP	Lets PL/SQL programs make HTTP callouts, and access data on the Internet over HTTP.
UTL_SMTP	Sends electronic mails (emails) over Simple Mail Transfer Protocol (SMTP) as specified by RFC821.

Data Abstraction
Allows you to access the essential property of data without getting too involved with details.
Abstraction mechanisms include:

Cursors, Composite Variables, %ROWTYPE attribute, %TYPE attribute, Abstract Data Types

Cursors
Pointer to a private SQL Area that keeps information about a specific SQL stmt or PL/SQL SELECT INTO statement.

Composite Variables
Have internal components, which can be access individually. Can be passed as parameters.
Two kinds of composite variables: collections and records.

Collections	The internal components are always of the same data type, and are called elements. You access each element by its unique index. Lists and arrays are classic examples of collections.
Records	Here the internal components can be of different data types, and are called fields. You access each field by its name. A record variable can hold a table row, or some columns from a table row. Struct (in C); RECORD (in Pascal)

%ROWTYPE attribute and %TYPE attribute

The %ROWTYPE attribute is used to declare a record that represents a FULL row of a database table.

The %ROWTYPE can also be used to represent a PARTIAL row (using a Cursor). The record fields do not inherit the constraints or initial values of the corresponding columns

With the %TYPE attribute you can declare a data item of the same data type as a previously declared variable or column (without knowing what that type is). If the declaration of the referenced item changes, then the declaration of the referencing item changes accordingly. referencing item inherits from the referenced item: (a) datatype and size; (b) constraints (unless the referenced item is a column) Referencing item does not inherits from the referenced item: (a) the initial value of the referenced item.

Abstract Data Types (ADTs) - Creating OO Classes

• Composed of data structure (attributes) + subprograms that manipulate the data (methods).
• Stored in the database. Instances of ADTs can be stored in tables and used as PL/SQL variables.
• Reduce complexity: separate a large system into logical, reusable, components.

Check the static dictionary views: DBA_OBJECTS: OBJECT_TYPE='TYPE' DBA_TYPES: TYPECODE='OBJECT'

Control Statements

• Conditional selection: IF THEN [ELSE || ELSIF]; CASE
• Loop: LOOP, FOR, Cursor FOR, WHILE [EXIT || EXIT WHEN; CONTINUE || CONTINUE WHEN]
• Sequential control: GOTO, NULL

Conditional Compilation
Customize functionality of a PL/SQL application. Compiler recognizes db versions and enable/disable features properly.
Recognize development/production environment: activate debugging/tracing properly.

Processing a Query Result Set One Row at a Time
You can issue a SQL query and process the rows of the result set one at a time.

You can use a basic loop, or you can control the process precisely by using a cursor to run the query, retrieve the results, and finish processing.

Architecture
PL/SQL Engine
Compilation and run-time system that is present in the Server and/or in an app dev tool.

PL/SQL Units

Anonymous Block Function Procedure Trigger

Library Package Package Body Type Type Body

PL/SQL compilation parameters
Category of DB Init parameters.
The compile-time values of the parameters are stored with the metadata of each stored PL/SQL unit, which means that you can reuse those values when you explicitly recompile the unit.
To explicitly recompile a stored PL/SQL unit and reuse its parameter values, you must use an ALTER statement with both the COMPILE clause and the REUSE SETTINGS clause

PLSCOPE_SETTINGS	Controls compile-time collection, cross-reference, and storage of PL/SQL source text identifier data. PLSCOPE_SETTINGS = IDENTIFIERS:{ NONE | ALL }
PLSQL_CCFLAGS	Enables you to control conditional compilation of each PL/SQL unit independently.
PLQL_CODE_TYPE	Defines compilation mode for PL/SQL libary units. PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }
PLSQL_OPTIMIZE_LEVEL	The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units. Range: {0,3}
PLSQL_WARNINGS	enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors.
NLS_LENGTH_SEMANTICS