Oracle Naming Conventions

When designing a database it's a good idea to follow some sort of naming convention. This will involve a little thought in the early design stages but will save significant time when maintaining the finished system.

It's less important which exact conventions you choose to follow - but this page has a few suggestions.

The benefits of using a naming convention are more to do with human factors than any system limitations - but this does not make them any less important.

Table Names

Table names are plural, field name is singular

Table names should not contain spaces, words should be split_up_with_underscores.
The table name is limited to 30 bytes which should equal a 30 character name (try a DESC ALL_TABLES and note the size of the Table_Name column)

If the table name contains serveral words, only the last one should be plural:
APPLICATIONS
APPLICATION_FUNCTIONS
APPLICATION_FUNCTION_ROLES

There are pros and cons to adding a prefix or suffix to identify tables-

Pros: If most access will be made via VIEWS then prefixing all the tables with T_ and all the views with V_ keeps things organised neatly, you will never accidentally query the wrong one.

Cons: Suppose, your naming convention is to have the '_TAB' suffix for all tables. According to that naming convention, the APPLICATIONS table would be called APPLICATIONS_TAB. If as time goes by, your application gets a second login, perhaps for auditing, or for security reasons. To avoid code changes, you will then have to create a View or Synonym that points at the original tables and is confusingly called APPLICATIONS_TAB.

Of course if all your code is written against Views in the first place then you will skirt right around this issue.

Field Names

Ideally each field name should be unique within the database schema. This makes it easy to search through a large set of code (or documentation) and find all occurences of the field name.

The convention is to prefix the fieldname with a 2 or 3 character contraction of the table name e.g.

PATIENT_OPTIONS would have a field called po_patient_option
PATIENT_RELATIVES would have a field called pr_relative_name
APPOINTMENTS would have a field called ap_date

In a large schema you will often find two tables having similar names which could result in the same prefix. You can avoid this by thinking carefully about the name you give each table - and documenting the prefixes to be used.
One advantage of this prefix is that you are very unlikely to choose a reserved word by accident.

For very complex systems (thousands of tables) consider alternatives e.g. a prefix/suffix to identify the Application module.
Keeping names short: Oracle places no limit on the number of columns in a GROUP BY clause or the number of sort specifications in an ORDER BY clause. However, the sum of the sizes of all such expressions is limited to the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.

Primary Key Fields - indicate by appending _pk

e.g.
PATIENTS would have a primary key called pa_patient_id_pk

REGIONS would have a primary key called re_region_id_pk
And so on…the name of the primary key field being a singular version of the table name. Other tables containing this as a foreign key would omit the _PK

so
CLINIC_ATTENDANCE might then have a foreign key called ca_patient_id
or alternatively: ca_patient_id_fk

Tables with Compound PK's, use _ck in place of _pk

Notice that where several tables use the same PK as part of a compound foreign key then the only unique part of the FK fieldname will be the table prefix.

View Names

View names are plural, field name is singular

View names should not contain spaces, words should be split_up_with_underscores.
While it is common to prefix (or suffix) all views with V_ or VW_, a strong argument can be made that neither are really needed.
One of the easiest ways to boost the performance of an application is to provide, at an early stage in the design, a carefully tuned set of views, then write all the application code against those Views.
Giving your Views friendly easy names will promote their use by developers and end-users, this in turn will mean fewer badly written queries and more use of 'shared SQL' which will improve the cache hit ratio.
For very large systems, it can make sense to prefix tables/views with the application module name, so a database holding data for both Widget Production and Human Resources data might prefix everything with either HR_ or WP_

Index names

Name the Primary Key index as idx_<TableName>_pk

e.g.
PATIENTS would have a primary key index called idx_patients_pk

Name a Unique Index as idx_<TableName>_uk

e.g.
PATIENTS would have a unique index called idx_patients_uk

Where more indexes are added to the same table, simply append a numeric:

idx_<TableName>_##
Where ## is a simple number

e.g.
PATIENTS would have additional indexes called idx_patients_01, idx_patients_02,…

Note - Conventions that attempt to use the column name as part of the index name become unmanageable as soon as you have multiple columns appearing in multiple indexes.

Constraints

Primary and Unique constraints will be explicitly named.

Name the Primary Key Constraint as pk_<TableName>

e.g.
PATIENTS would have a primary key index called pk_patients

Name a Foreign Key Constraint as fk_<TableName>

e.g.
PATIENTS would have a Foreign Key constraint called fk_patients

Note - in general each constraint should have a similar name to the index used to support the constraint.

Other Fields

Without getting carried away, you can also apply a suffix to non key fields where this is helpful in describing the type of data being stored.
e.g.
A column used to store boolean (Yes/No) values can be given a _yn suffix: retired_yn, superuser _yn, driver_yn

In lookup tables an easy way to identify the main text field is to name it as a singular version of the tablename
e.g.

asset_types.at_asset_type_id_pk   (Primary Key)
asset_types.at_asset_type         (Text field)
asset_types.at_network_yn         (boolean) 

SQL

Type all SQL statements in lowercase, being consistent with capitalisation improves the caching of SQL statements. A common variant is to put only SQL keywords in capitals.

SELECT
   em_employee_id_pk,
   em_employee,
   ab_start_date
FROM
   employees em,
   absences ab
WHERE
   absences.ab_employee_id=employees.em_employee_id_pk;

You already have a unique prefix worked out for every table, so use the same thing when an ALIAS is required - this makes the SQL much easier to read.

Always list tables in the FROM / WHERE clause in desired join order - even with CBO you are giving the Query Optimiser less work to do.

PL/SQL

Prefix scalar variable names with v_
Prefix global variables (including host or bind variables) with g_
Prefix constants with c_
Prefix procedure or function call parameters (including sql*plus substitution parameters) with p_

Prefix record collections with r_ (alternatively suffix with _record)
Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)

Prefix pl/sql tables with t_ (alternatively suffix with _table)
Prefix table types with tt_ (alternatively suffix with _table_type)

Suffix cursors with _cursor
Prefix exceptions with e_

If a pl/sql variable is identical to the name of a column in the table Oracle will interpret the name as a column name.

Packages
Prefix package names with PKG_

Write one package for each table - named PKG_TABLENAME, put all other code that logically belongs to the schema, but not to any particular table in a single Schema package PKG_SCHEMANAME.
If, as is likely, more complex grants are required for different groups of users then create an additional package for each workgroup - these should contain no code just wrappers that call procedures/functions in the other packages.
This gives a level of separation between the basic code and the user security/grants and makes it easier to change one without breaking the other.

A pl/sql function name like PAYROLL.TAX_RATE the word PAYROLL could refer to either a schema or a package name.

Edit Replace

If you apply a naming convention and then decide to rename something it may be possible to use Edit-Replace to update the associated code. But consider these two fieldnames:

area_codes.ac_code 
region_area_codes.rac_code

The columns may be unique but one is a substring of the other!

Instance

Oracle database instance names are limited to eight characters. The first two or three characters of the name should reflect the Application, with the remainder indicating the nature of the instance.
e.g.

Live instance   SSLive
Test instance   SSTest
Train instance  SSTrain
Data Warehouse  SSdw
Staging Area    SSsa

Data Files

Name Data files so that they identify the instance and the tablespace.

Each filename should end with a two digit numeric value starting with 01, that is incremented by 1 for each new datafile added to the tablespace.
Use the extension ".dbf"
e.g.

SSLive_temp01.dbf
SSLive_rbs01.dbf
SSLive_clinical01.dbf
SSLive_clinical02.dbf

Tablespaces

Avoid naming tablespaces according to time periods.
(Oracle never forgets a tablespace and SMON will scan the list of tablespaces in TS$ every 5 minutes) For a partitioned datawarehouse, try to adopt a strategy of recycling the tablespace names.

Redo Logs

The redo log is a separate file (not in the tablespace)
Name Redo Logs so that they identify the instance, group and member number of the log. Use the extension ".log"
e.g.
SSLive_redo_01.log

For more detail on the physical placement of files see Oracle Optimal Flexible Architecture (OFA)

Documentation

Lastly - write and maintain a data dictionary for all data elements - rather than just dumping the Oracle data dictionary into a text document or an Entity relationship diagram - you should also be defining the business meaning of each data item.

Summary

RDBMS naming conventions can become the subject of endless debate - here are a few last things to consider:

Does your naming convention make names longer or shorter?

PURCHASE_ORDER_DATE versus PO_DATE

Will you have novice users writing SQL against the database?
If so will they understand the meaning of things like PO_DATE

Is the naming convention documented somewhere that everyone can find? If you don’t plan to change it very often, drop the text right into a table SELECT * from Naming_Conventions;

PL/SQL SELECT Statement

Retrieve data from one or more tables, views, or snapshots.

Syntax:

   SELECT [hint][DISTINCT] select_list
   INTO {variable1, variable2... | record_name}
   FROM table_list
   [WHERE conditions]
   [GROUP BY group_by_list]
   [HAVING search_conditions]
   [ORDER BY order_list [ASC | DESC] ]
   [FOR UPDATE for_update_options]

key:

select_list
A comma-separated list of table columns (or expressions) eg:

column1, column2, column3 
table.column1, table.column2
table.column1 Col_1_Alias, table.column2 Col_2_Alias
schema.table.column1 Col_1_Alias, schema.table.column2 Col_2_Alias
schema.table.*
*
expr1, expr2
(subquery [WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT constraint]])

In the select_lists above, 'table' may be replaced with view or snapshot.
Using the * expression will return all columns.
If a Column_Alias is specified this will appear at the top of any column headings in the query output.
DISTINCT
Supress duplicate rows - display only the unique values.
Duplicate rows have matching values across every column (or expression) in the select_list.

INTO
A list of previously defined variables - there must be one variable for each item SELECTed - the results of the select statement are stored in these variables.
When SELECTing data INTO variables in this way the query must return ONE row.
FROM table_list
Contains a list of the tables from which the result set data is retrieved.

[schema.]{table | view | snapshot}[@dblink] [t_alias]

When selecting from a table you can also specify Partition and/or Sample clauses e.g.

[schema.]table [PARTITION (partition)] [SAMPLE (sample_percent)]

If the SELECT statement involves more than one table, the FROM clause can also contain join specifications (SQL1992 standard).

WHERE search_conditionsA filter that defines the conditions each row in the source table(s) must meet to qualify for the SELECT. Only rows that meet the conditions will be included in the result set. The WHERE clause can also contain inner and outer join specifications (SQL1989 standard). e.g.

WHERE tableA.column = tableB.column
WHERE tableA.column = tableB.column(+)
WHERE tableA.column(+) = tableB.column

GROUP BY group_by_listThe GROUP BY clause partitions the result set into groups.
The rows in each group having a unique value in group_by_list.
The group_by_list may be one or more columns or expressions and may optionally include the CUBE / ROLLUP keywords for creating crosstab results.
(note the groups will be in random order unless you additionally specify ORDER BY)

For example, the Order_Items table contains:

oi_shipping   oi_value   oi_units
ldn           89.75      2
ny            12.99      1
ldn           55.15      4
edi           23.00      6

A GROUP BY oi_shipping will partition the result set into the three groups: ldn, ny, edi

Heirarchical Queries
Any query that does *not* include a GROUP BY clause may include a CONNECT BY heirarchy clause:

[START WITH condition] CONNECT BY condition

HAVING search_conditions
An additional filter - the HAVING clause acts as an additional filter to the grouped result rows - as opposed to the WHERE clause that applies to individual rows. The HAVING clause is most commonly used in conjunction with a GROUP BY clause.

ORDER BY order_list [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted ascending (1...9 a...z) or descending (9...1 z...a).
You can sort by any column even if that column is not actually in the main SELECT clause. If you do not include an ORDER BY clause then the order of the result set rows will be unpredictable (random or quasi random).

FOR UPDATE options - this locks the selected rows (Oracle will normally wait for a lock unless you specify NOWAIT)

Cursors are often used with a SELECT FROM ... FOR UPDATE [NOWAIT]
Specifying NOWAIT will exit with an error if the rows are already locked by another session.
Because the locks are not released until the end of the transaction you should not 'commit across fetches' from an explicit cursor if FOR UPDATE is used.

FOR UPDATE [OF [ [schema.]{table|view}.] column] [NOWAIT]

DECLARE
   CURSOR ... IS
      SELECT ..FROM...WHERE...
      FOR UPDATE OF ... NOWAIT;

Writing a SELECT statement
The clauses (SELECT ... FROM ... WHERE ... HAVING ... ORDER BY ... ) must be in this order.
The position of commas and semicolons is not forgiving
Each expression must be unambiguous. In other words if the FROM clause includes 2 columns with the same name, then both column names must be prefixed with the tablename (or view name).

    SELECT DISTINCT
        customer_id,
        oi_ship_date
    FROM
        customers
        order_items  
    WHERE
        customers.customer_id = order_items.customer_id
        AND order_items.oi_ship_date > '01-may-2001';

If the table and view names themselves must be qualified with the schema (scott.t_customers.customer_id,) then this can become rather verbose. This syntax can be greatly simplified by assigning a table_alias (sometimes also known as a range variable or correlation name).

The fully qualified table has to be specified only in the FROM clause. All other table or view references can then use the alias name. e.g.

    SELECT DISTINCT
        cst.customer_id, 
        cst.customer_id,
        ord.oi_ship_date
    FROM
        customers cst
        order_items ord
    WHERE
        cst.customer_id = ord.customer_id
        AND ord.oi_ship_date > '01-may-2001';

Ambiguous expressions can also be avoided through the use of an appropriate naming convention.
"Computers are useless. They only give you answers" - Pablo Picasso

EXPLAIN PLAN Statement

Display the execution plan for an SQL statement.

Syntax:

   EXPLAIN PLAN [SET STATEMENT_ID = 'text']
      FOR statement;

   EXPLAIN PLAN [SET STATEMENT_ID = 'text']
      INTO [schema.]table@dblink
         FOR statement;

If you omit the INTO TABLE_NAME clause, Oracle will fill a table named PLAN_TABLE
Example

-- Create an empty plan table (adds a table to the current schema)
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

-- Run explain plan
EXPLAIN PLAN FOR
SELECT s.col1, s.col2, h.col3
FROM huge_table h JOIN small_table s USING (demo_id);

-- Now look at the plan created
SELECT * FROM TABLE(dbms_xplan.display);

-- Delete the records when finished
DELETE from plan_table;
COMMIT;

If the query is fast enough that it can be run to completion in a reasonable amount of time, then just turn on the SQL*Plus AutoTrace feature. Once turned on, this feature will display an execution plan for every subsequent SQL statement you run.

SQL> SET AUTOTRACE ON
SQL> SELECT s.col1, s.col2, h.col3
FROM huge_table h JOIN small_table s USING (demo_id);

Explain plan results
In an explain plan output, the more indented an operation is, the earlier it is executed.
The result of the indented operation is fed to the parent (less indented) operation. In this way you can see the order of execution for the whole statement.
It is possible for several operations to be equally indented and have the same parent. These indentations are calculated from the id, and parent_id columns of the plan_table.
Operations: SELECT, INSERT, UPDATE, DELETE, AND-EQUAL, CONNECT BY, CONCATENATION, COUNT, DOMAIN INDEX, FILTER, FIRST , ROW, FOR UPDATE, HASH JOIN, INDEX, INLIST ITERATOR, INTERSECTION, MERGE JOIN, MINUS, NESTED LOOPS, PARTITION,REMOTE, SEQUENCE, SORT, TABLE ACCESS, UNION, VIEW.
There are also many Options which describe each Operation in more detail - here are a few of the most common:
TABLE ACCESS (FULL) = Full table scan
INDEX (RANGE SCAN) = Read multiple values from an index
INDEX (UNIQUE SCAN) = Read one value from an index
MERGE JOIN () = Sort two tables and merge the sorted rows
SORT (JOIN) = Sort returning multiple rows
SORT (AGGREGATE) = Sort returning one row
"Nobody expects the Spanish Inquisition!!!" - Monty Python

PL/SQL Where current of

... WHERE CURRENT OF cursor

WHERE CURRENT is used as a reference to the current row when 
using a cursor to UPDATE or DELETE the current row.

The cursor must be based on SQL that selects 'FOR UPDATE'

e.g.

DECLARE
   CURSOR trip_cursor IS
      SELECT
           bt_id_pk,
           bt_duration
      FROM
           business_trips
      WHERE
           bt_id_pk = 23
      FOR UPDATE OF bt_id_pk, bt_duration;
BEGIN
   FOR trip_record IN trip_cursor LOOP
     UPDATE business_trips
     SET bt_duration = 5
     WHERE CURRENT OF trip_cursor;
   END LOOP;
   COMMIT;
END;

=====

PL/SQL SELECT Statement

Retrieve data from one or more tables, views, or snapshots.

Syntax:

   SELECT [hint][DISTINCT] select_list
   INTO {variable1, variable2... | record_name}
   FROM table_list
   [WHERE conditions]
   [GROUP BY group_by_list]
   [HAVING search_conditions]
   [ORDER BY order_list [ASC | DESC] ]
   [FOR UPDATE for_update_options]

key:

select_list
A comma-separated list of table columns (or expressions) eg:

column1, column2, column3 
table.column1, table.column2
table.column1 Col_1_Alias, table.column2 Col_2_Alias
schema.table.column1 Col_1_Alias, schema.table.column2 Col_2_Alias
schema.table.*
*
expr1, expr2
(subquery [WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT constraint]])

In the select_lists above, 'table' may be replaced with view or snapshot.
Using the * expression will return all columns.
If a Column_Alias is specified this will appear at the top of any column headings in the query output.
DISTINCT
Supress duplicate rows - display only the unique values.
Duplicate rows have matching values across every column (or expression) in the select_list.

INTO
A list of previously defined variables - there must be one variable for each item SELECTed - the results of the select statement are stored in these variables.
When SELECTing data INTO variables in this way the query must return ONE row.
FROM table_list
Contains a list of the tables from which the result set data is retrieved.

[schema.]{table | view | snapshot}[@dblink] [t_alias]

When selecting from a table you can also specify Partition and/or Sample clauses e.g.

[schema.]table [PARTITION (partition)] [SAMPLE (sample_percent)]

If the SELECT statement involves more than one table, the FROM clause can also contain join specifications (SQL1992 standard).

WHERE search_conditionsA filter that defines the conditions each row in the source table(s) must meet to qualify for the SELECT. Only rows that meet the conditions will be included in the result set. The WHERE clause can also contain inner and outer join specifications (SQL1989 standard). e.g.

WHERE tableA.column = tableB.column
WHERE tableA.column = tableB.column(+)
WHERE tableA.column(+) = tableB.column

GROUP BY group_by_listThe GROUP BY clause partitions the result set into groups.
The rows in each group having a unique value in group_by_list.
The group_by_list may be one or more columns or expressions and may optionally include the CUBE / ROLLUP keywords for creating crosstab results.
(note the groups will be in random order unless you additionally specify ORDER BY)

For example, the Order_Items table contains:

oi_shipping   oi_value   oi_units
ldn           89.75      2
ny            12.99      1
ldn           55.15      4
edi           23.00      6

A GROUP BY oi_shipping will partition the result set into the three groups: ldn, ny, edi

Heirarchical Queries
Any query that does *not* include a GROUP BY clause may include a CONNECT BY heirarchy clause:

[START WITH condition] CONNECT BY condition

HAVING search_conditions
An additional filter - the HAVING clause acts as an additional filter to the grouped result rows - as opposed to the WHERE clause that applies to individual rows. The HAVING clause is most commonly used in conjunction with a GROUP BY clause.

ORDER BY order_list [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted ascending (1...9 a...z) or descending (9...1 z...a).
You can sort by any column even if that column is not actually in the main SELECT clause. If you do not include an ORDER BY clause then the order of the result set rows will be unpredictable (random or quasi random).

FOR UPDATE options - this locks the selected rows (Oracle will normally wait for a lock unless you specify NOWAIT)

Cursors are often used with a SELECT FROM ... FOR UPDATE [NOWAIT]
Specifying NOWAIT will exit with an error if the rows are already locked by another session.
Because the locks are not released until the end of the transaction you should not 'commit across fetches' from an explicit cursor if FOR UPDATE is used.

FOR UPDATE [OF [ [schema.]{table|view}.] column] [NOWAIT]

DECLARE
   CURSOR ... IS
      SELECT ..FROM...WHERE...
      FOR UPDATE OF ... NOWAIT;

Writing a SELECT statement
The clauses (SELECT ... FROM ... WHERE ... HAVING ... ORDER BY ... ) must be in this order.
The position of commas and semicolons is not forgiving
Each expression must be unambiguous. In other words if the FROM clause includes 2 columns with the same name, then both column names must be prefixed with the tablename (or view name).

    SELECT DISTINCT
        customer_id,
        oi_ship_date
    FROM
        customers
        order_items  
    WHERE
        customers.customer_id = order_items.customer_id
        AND order_items.oi_ship_date > '01-may-2001';

If the table and view names themselves must be qualified with the schema (scott.t_customers.customer_id,) then this can become rather verbose. This syntax can be greatly simplified by assigning a table_alias (sometimes also known as a range variable or correlation name).

The fully qualified table has to be specified only in the FROM clause. All other table or view references can then use the alias name. e.g.

    SELECT DISTINCT
        cst.customer_id, 
        cst.customer_id,
        ord.oi_ship_date
    FROM
        customers cst
        order_items ord
    WHERE
        cst.customer_id = ord.customer_id
        AND ord.oi_ship_date > '01-may-2001';

Ambiguous expressions can also be avoided through the use of an appropriate naming convention.
"Computers are useless. They only give you answers" - Pablo Picasso

Cursor FOR Loops

Without defining a cursor explicitly we can simply substitute the subquery inside a FOR statement.

e.g. 
SET SERVEROUTPUT ON
BEGIN
   FOR trip_record IN (SELECT bt_id_pk, bt_duration
                      FROM business_trips) LOOP
      -- implicit open/fetch occurs
      IF trip_record.bt_duration = 1 THEN
        DBMS_OUTPUT_LINE ('trip Number ' || trip_record.bt_id_pk
                        || ' is a one day trip');
      END IF;
   END LOOP; -- implicit CLOSE occurs
END;
/

Cursor with Parameters

DECLARE
   v_hotel     business_trips.bt_hotel_id%TYPE := 12;
   v_duration  business_trips.bt_duration.%TYPE := 3;
   CURSOR trip_cursor(p_hotel NUMBER, p_duration VARCHAR2) IS
     SELECT ...

--Then to open the cursor either

OPEN trip_cursor (12, 3);

--or using the variables:

OPEN trip_cursor (v_hotel, v_duration);

--Alternatively open the cursor implicitly as part of
a Cursor FOR loop
pass the parameters like this...

BEGIN
   FOR trip_record IN trip_cursor(12, 3) LOOP ...

PL/SQL Looping Statements

LOOP, WHILE Loop, FOR Loop

A basic LOOP command will continue until 'condition' evaluates to true
(condition is a boolean var)

LOOP
   STATEMENT1;
   ...
   EXIT [WHEN condition];
END LOOP;

The only difference between LOOP and WHILE is that the
condition is evaluated at the start of each iteration.

WHILE condition LOOP
   statement1;
   statement2;
...
END LOOP;

A PL/SQL FOR Loop will implicitly declare a counter,
the counter can only be referenced within the loop. 
Don't try to change the counter's value using code.

FOR loop

FOR counter in [REVERSE]
   lower_bound..upper_bound LOOP
   statement1;
   statement2;
...
END LOOP;


The 3 loop commands above (LOOP, WHILE, FOR) can be Nested...

To give each loop a specific name - put the name in double
angle brackets << >>
Put this name definition on the line immediately before each LOOP 

<<Main_loop>>
LOOP
...
   <<sub_loop>>
   LOOP
   ...
   END LOOP sub_loop;

END LOOP Main_loop;

When PL/SQL code contains labels like the above it is also possible to simply GOTO a given label:

e.g.
GOTO Main_loop

This is generally poor programming practice - and some destinations, such as the middle of a loop, won't work.

Related Commands:
Cursor FOR loop - 
EXIT - 

Declaring RECORD variables

DECLARE

Declare variables and constants in a PL/SQL declare block.

Syntax:

     name [CONSTANT] datatype [NOT NULL]
        [:= | DEFAULT expr]

key
   name      : The name of the variable

   datatype  : may be scalar, composite, reference or LOB

   expr      : a literal value, another variable 
               or any plsql expression involving operators & functions

For readability put only one declaration per line .

A constant MUST have it's initial value in the declaration.

Composite datatypes are TABLE, RECORD, NESTED TABLE and VARRAY

You can use [schema.]object%TYPE to define variables based on actual object datatypes.

Declaring RECORD variables

A specific RECORD TYPE corresponding to a fixed number (and datatype) of underlying table columns can simplify the job of defining variables.
%TYPE is used to declare a field with the same type as that of a specified table's column.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor.

Syntax:

TYPE type_name IS RECORD
      (field_declaration,...);

Options
 'field_declaration' is defined as:

   field_name {field_type |
               variable%TYPE |
               table.column%TYPE |
               table%ROWTYPE}
               [ [NOT NULL] {:= | DEFAULT} expr ]

        Where field_type is the datatype of the field
        (any plsql datatype except REF CURSOR)

        expr is the field_type or an initial value

Then to declare a record variable of this type..

   identifier type_name;

Declare %ROWTYPE Record variables:

   DECLARE
      variable_name table_name%ROWTYPE

At runtime the system will evaluate the number of variables and their datatype; The columns may be based on an underlying table or a cursor.

Declare SQL*Plus bind variables.

Syntax:

plus > VARIABLE g_bar VARCHAR2(30)
plus > ACCEPT p_foo PROMPT 'enter the value required'

You can reference host variables in PL/SQL statements *unless* the statement is in a procedure, function or package.
This is done by prefixing with & (to read the variable) or prefix with : (writing to the variable)

Examples:

DECLARE
-- Declare individual variables:

   v_ename emp.ename%TYPE;
   v_balance NUMBER(7,2);
   v_min_bal v_balance%TYPE := 10;

-- Declare RECORD TYPE variable:

   TYPE job_type IS RECORD
      (jobid    NUMBER(7,2),
       jobname  t_jobs.jb_name%Type);
   
   job_record job_type;

-- Declare a variable based on SQL*Plus Bind variable

   v_amount NUMBER(6,2) := &p_foo

-- Assign value to a SQL*Plus variable from a PL/SQL variable

BEGIN
   :g_bar := v_amount *12

Using TABLE variable Methods

DECLARE

Declare TABLE TYPE variables in a PL/SQL declare block.

Table variables are also known as index-by table or array. The table variable contains one column which must be a scalar or record datatype plus a primary key of type BINARY_INTEGER.

Syntax:

   DECLARE
   TYPE type_name IS TABLE OF
      (column_type |
      variable%TYPE |
      table.column%TYPE
         [NOT NULL]
            INDEX BY BINARY INTEGER;

-- Then to declare a TABLE variable of this type:

   variable_name type_name;

-- Assigning values to a TABLE variable:
   variable_name(n).field_name := 'some text';
-- Where 'n' is the index value

Using TABLE variable Methods:

To execute these use the syntax

   table_name[ (parameters)]

EXISTS(n)   Returns TRUE if nth element of the table exists.

COUNT       The number of elements (rows) in the plsql table

FIRST       First and Last index no.s in the table
LAST        returns NULL if table is empty

PRIOR(n)    Returns index no that preceeds n in the plsql table

NEXT(n)     Returns index no that succeeds n in the plsql table

EXTEND(n,i) Append n copies of the 'i'th element to a plsql table
            i defaults to NULL n defaults to 1

TRIM(n)     Remove n elements from the end of a plsql table
            n defaults to 1

DELETE(m,n) Delete elements in range m...n
            m defaults to = n
            n defaults to ALL elements

Note Extend and Trim are new to Oracle 8.

Examples:

   DECLARE
   -- declare the table type
   TYPE MyTrip_table_type IS TABLE OF
       business_trips.bt_cost%Type
       INDEX BY BINARY INTEGER;

   --declare a TABLE variable of this type
   myTrips MyTrip_table_type;

   BEGIN
      myTrips(1) := 'Test Job';
      UPDATE business_trips
      SET bt_cost = bt_cost * 1.2
      WHERE bt_id_pk = myTrips(1)
   END
   /

IF Statement

Syntax:

   IF condition THEN
      statements;
   [ELSIF condition THEN
      statements;]
   [ELSE
      statement;]
   END IF;

Note the spelling is ElsIF not ElseIF which you might expect from other languages.

'condition' may include logical comparisons...

true AND true = true
true OR true = true

false AND false = false
false OR false = false

null AND null = null
null OR null = null

true AND false = false
true OR false = true

true AND null = null
true OR null = true

false AND null = false
false OR null = null

NOT TRUE = FALSE
NOT FALSE = TRUE
NOT NULL = NULL 

Operators, comments, delimiters

For SQL*Plus and PL/SQL

Comparison Operators:

   NOT   IS NULL   LIKE   BETWEEN   IN   AND   OR
   +  -  * / @  ;  =  <>  !=  ||  <=   >=

Comments
   -- comment
   /* comment */ 
   << Begin label - end label >>

Assignment operator
   :=
   You can assign values to a variable, literal value, or function call 
   but NOT a table column.

Exponential operator (valid for plsql only)
   ** 

Delimiters
 Item separator . 
 Character string delimiter '
 Quoted String delimiter "
 Bind variable indicator :
 Attribute indicator %
 Statement terminator  ;

Functions
   All SQL functions that return a single row can be used in 
   a plsql procedural statement. Group and DECODE functions are
   not supported.

Examples

v_myDate := TO_DATE('01-OCT-2001',DD-MON-YYYY)
The TO_DATE is required because date formats are region specific.

new in oracle 8i is 
v_mychar := '01-OCT-2001'
Previous versions need a to_char()

Assigning values to a RECORD TYPE or ROWTYPE variable
use dot notation to specify the field:

   job_record.jobname := 'Test Job';

DBA_OBJECTS

All objects in the database

Columns
   ___________________________
 
   OWNER
      Username of the owner of the object
   OBJECT_NAME
      Name of the object
   SUBOBJECT_NAME
      Name of the sub-object (for example,partititon)
   OBJECT_ID
      Object number of the object
   DATA_OBJECT_ID
      Object number of the segment which contains the object
   OBJECT_TYPE
      Type of the object
   CREATED
      Timestamp for the creation of the object
   LAST_DDL_TIME
      Timestamp for the last DDL change (including GRANT and REVOKE) to the object
   TIMESTAMP
      Timestamp for the specification of the object
   STATUS
      Status of the object
   TEMPORARY
      Can the current session only see data that it place in this object itself?
   GENERATED
      Was the name of this object system generated?
   SECONDARY
      Is this a secondary object created as part of icreate for domain indexes?

USER_OBJECTS

Objects owned by the user

Columns
   ___________________________
 
   OBJECT_NAME
      Name of the object
   SUBOBJECT_NAME
      Name of the sub-object (for example,partititon)
   OBJECT_ID
      Object number of the object
   DATA_OBJECT_ID
      Object number of the segment which contains the object
   OBJECT_TYPE
      Type of the object
   CREATED
      Timestamp for the creation of the object
   LAST_DDL_TIME
      Timestamp for the last DDL change (including GRANT and REVOKE) to the object
   TIMESTAMP
      Timestamp for the specification of the object
   STATUS
      Status of the object
   TEMPORARY
      Can the current session only see data that it place in this object itself?
   GENERATED
      Was the name of this object system generated?
   SECONDARY
      Is this a secondary object created as part of icreate for domain indexes?

Related:
Running the SQL*Plus script below (substituting &Owner and &NewUser) will produce a listing of all the permissions to allow the New User to access all the objects owned by OWNER. Review the output of the script and then run it to Grant the new permissions to NewUser.
Set pagesize 0
define OWNER=Kate
define NEWUSER=Alex
Spool new_grants.txt
Select
decode(OBJECT_TYPE,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',
'VIEW','GRANT SELECT ON '||'&OWNER'||'.',
'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',
'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',
'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',
'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'
From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
Order By OBJECT_TYPE;
Spool Off

USER_SOURCE

Source of stored objects accessible to the user

Columns
   ___________________________
 
   NAME
      Name of the object
   TYPE
      "Type of the object: "TYPE","TYPE
   LINE
      Line number of this line of source
   TEXT
      Source text

ALL_OBJECTS

Objects accessible to the user

Columns
   ___________________________
 
   OWNER
      Username of the owner of the object
   OBJECT_NAME
      Name of the object
   SUBOBJECT_NAME
      Name of the sub-object (for example,partititon)
   OBJECT_ID
      Object number of the object
   DATA_OBJECT_ID
      Object number of the segment which contains the object
   OBJECT_TYPE
      Type of the object
   CREATED
      Timestamp for the creation of the object
   LAST_DDL_TIME
      Timestamp for the last DDL change (including GRANT and REVOKE) to the object
   TIMESTAMP
      Timestamp for the specification of the object
   STATUS
      Status of the object
   TEMPORARY
      Can the current session only see data that it placed in this object itself?
   GENERATED
      Was the name of this object system generated?
   SECONDARY
      Is this a secondary object created as part of icreate for domain indexes?

DESC[RIBE] (SQL*Plus command)

Describe an Oracle Table, View, Synonym, package or Function.

Note that because this is a SQL*Plus command you don't need to terminate it with a semicolon.

Syntax:

   DESC table

   DESC view

   DESC synonym

   DESC function

   DESC package

In Oracle 7 you could describe individual procedures e.g. desc DBMS_UTILITY.GET_PARAMETER_VALUE
In Oracle 8/9/10 you can only describe the whole package: desc DBMS_UTILITY
It is also possible to describe objects in another schema or via a database link
e.g.
DESCRIBE user.table@db_link

Recursive

The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command.
For example use the SET commands:
SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF
To display these settings use: SHOW DESCRIBE
Data Types

The description for functions and procedures contains the type of PL/SQL object (function or procedure) the name of the function or procedure, the type of value returned (for functions) the argument names, types, whether input or output, and default values, if any.

DESC user.object_name will always identify a distinct database object because a user's database objects must have unique names. e.g. you cannot create a FUNCTION with the same name as a TABLE in the same schema.
Data Dictionary
An alternative to the DESC command is selecting directly from the data dictionary -

DESC MY_TABLE

is equivalent to

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';
Column Comments
To view column comments:
SELECT comments
FROM user_col_comments
WHERE table_name='MY_TABLE';
SELECT 'comment on column '||table_name||'.'||column_name||' is '''||comments||''';'
FROM user_col_comments
WHERE comments is not null;
Writing code and find yourself typing in a bunch of column names? Why bother when it's all available in the data dictionary. The script below will help out:
COL.SQL
-- List all the columns of a table.

select chr(9)||lower(column_name)||',' 
from USER_tab_columns 
where table_Name = UPPER('&1') 
/ 

So now if you want a list of the columns in the EMP table simply type:
@col emp
This will produce a list of columns:

empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,

You know the great thing about TV? If something important happens anywhere at all in the world, no matter what time of the day or night, you can always change the channel. - Jim Ignatowski

EXEC[UTE] (SQL*Plus command)

Execute a PL/SQL function or procedure.

Syntax:

   EXEC statement

   EXEC [:bind_variable :=] package.procedure;

   EXEC [:bind_variable :=] package.function(parameters);

The length of the EXEC command cannot exceed the length defined by SET LINESIZE.
If the EXEC command is too long to fit on one line, use the SQL*Plus continuation character (a hyphen) -

Example
SQL> EXEC :answer := EMP_PAY.BONUS('SMITH')
Executing directly from the shell (in this case Windows):
C:\> echo execute demoProc|sqlplus demo/password

"Everyone wants results, but no one is willing to do what it takes to get them" - Dirty Harry

Oracle Supplied Packages

Packages marked * are new in 9.2

Package       Description

DBMS_ALERT    Notify a database event (asynchronous)

DBMS_APPLICATION_INFO
              Register an application name with the database
              for auditing or performance tracking.
              Application info can be pushed into V$SESSION/V$SESSION_LONGOPS
 
DBMS_AQ       Add a message (of a predefined object type) onto a queue 
              or dequeue a message.
 
DBMS_AQADM    Administer a queue or queue table 
              for messages of a predefined object type.
 
DBMS_AQELM    Configure Advanced Queuing
              asynchronous notification by e-mail and HTTP. *
 
DBMS_BACKUP_RESTORE
              Normalize filenames on Windows NT platforms.
 
DBMS_DDL      Access SQL DDL statements from a stored procedure,
              provides special administration operations 
              not available as DDLs.
 
DBMS_DEBUG    Implement server-side debuggers and provide a way to 
              debug server-side PL/SQL program units. 
 
DBMS_DEFER    User interface to a replicated transactional deferred 
              RPC facility. Requires the Distributed Option. 
 
DBMS_DEFER_QUERY
              Permit querying the deferred remote procedure calls (RPC)
              queue data that is not exposed through views.
              Requires the Distributed Option. 
 
DMBS_DEFER_SYS
              The system administrator interface to a replicated 
              transactional deferred RPC facility.
              Requires the Distributed Option.
 
DBMS_DESCRIBE
              Describe the arguments of a stored procedure
              with full name translation and security checking.
 
DBMS_DISTRIBUTED_TRUST_ADMIN
              Maintain the Trusted Database List, which is used to 
              determine if a privileged database link from a particular 
              server can be accepted.

DBMS_ENCODE   Encode???
 
DBMS_FGA      Fine-grained security functions. *
 
DMBS_FLASHBACK
              Flash back to a version of the database at a specified
              wall-clock time or a specified system change 
              number (SCN). *
 
DBMS_HS_PASSTHROUGH
              Send pass-through SQL statements to non-Oracle systems.
              (via Heterogeneous Services)
 
DBMS_IOT      Create a table into which references to the chained rows 
              for an Index Organized Table can be placed using the 
              ANALYZE command. 
 
DBMS_JOB      Schedule PL/SQL procedures that you want performed at 
              periodic intervals; also the job queue interface. 
 
DBMS_LDAP     Functions and procedures to access data from
              LDAP servers. *
 
DBMS_LIBCACHE
              Prepares the library cache on an Oracle instance by 
              extracting SQL and PL/SQL from a remote instance and
              compiling this SQL locally without execution. *
 
DBMS_LOB      General purpose routines for operations on Oracle Large
              Object (LOBs) datatypes - BLOB, CLOB (read-write),
              and BFILEs (read-only). 
 
DBMS_LOCK     Request, convert and release locks through Oracle Lock 
              Management services.
 
DBMS_LOGMNR   Functions to initialize and run the log reader. 
 
DBMS_LOGMNR_CDC_PUBLISH
              Identify new data that has been added to, modified, or 
              removed from, relational tables and publish the changed 
              data in a form that is usable by an application. *
 
DBMS_LOGMNR_CDC_SUBSCRIBE
              View and query the change data that was captured 
              and published with the DBMS_LOGMNR_CDC_PUBLISH package. *
 
DBMS_LOGMNR_D
              Query the dictionary tables of the current database, and 
              create a text based file containing their contents.
 
DBMS_METADATA
              Retrieve complete database object definitions (metadata) 
              from the dictionary. *
 
DBMS_MVIEW    Refresh snapshots that are not part of the same 
              refresh group and purge logs. DBMS_SNAPSHOT is a synonym.
 
DBMS_OBFUSCATION_TOOLKIT
              Procedures for Data Encryption Standards.
 
DBMS_ODCI     Get the CPU cost of a user function based on the 
              elapsed time of the function. *
 
DBMS_OFFLINE_OG
              Public APIs for offline instantiation of master groups.
 
DBMS_OFFLINE_SNAPSHOT
              Public APIs for offline instantiation of snapshots.
 
DBMS_OLAP     Procedures for summaries, dimensions, and query rewrites.
 
DBMS_ORACLE_TRACE_AGENT
              Client callable interfaces to the Oracle TRACE
              instrumentation within the Oracle7 Server.
 
DBMS_ORACLE_TRACE_USER
              Public access to the Oracle release 7 Server 
              Oracle TRACE instrumentation for the calling user.
 
DBMS_OUTLN    Interface for procedures and functions associated with 
              management of stored outlines. Synonymous with OUTLN_PKG
 
DBMS_OUTLN_EDIT
              Edit an invoker's rights package. *
 
DBMS_OUTPUT   Accumulate information in a buffer so that it can be 
              retrieved out later.
 
DBMS_PCLXUTIL Intra-partition parallelism for creating partition-wise 
              local indexes.
 
DBMS_PIPE     A DBMS pipe service which enables messages to be sent 
              between sessions.
 
DBMS_PROFILER A Probe Profiler API to profile PL/SQL applications
              and identify performance bottlenecks.
              To install this run profload.sql(as SYS) and proftab.sql(as user)
 
DBMS_RANDOM   A built-in random number generator.
              Options to generate random numbers within a range or distribution.
 
DBMS_RECTIFIER_DIFF
              APIs used to detect and resolve data inconsistencies 
              between two replicated sites.
 
DBMS_REDEFINITION
              Reorganise a table (change it's structure) while it's
              still online and in use. *
 
DBMS_REFRESH  Create groups of snapshots that can be refreshed together
              to a transactionally consistent point in time.
              Requires the Distributed Option. 
 
DBMS_REPAIR   Repair data corruption.
 
DBMS_REPCAT   Administer and update the replication catalog and environment. 
              Requires the Replication Option.
 
DBMS_REPCAT_ADMIN
              Create users with the privileges needed by the symmetric 
              replication facility. Requires the Replication Option.
 
DBMS_REPCAT_INSTATIATE
              Instantiates deployment templates.
              Requires the Replication Option.
 
DBMS_REPCAT_RGT
              Define and maintain refresh group templates. 
              Requires the Replication Option.

DBMS_REPUTIL
              Generate shadow tables, triggers, and packages 
              for table replication.
 
DBMS_RESOURCE_MANAGER
              Maintain plans, consumer groups, and plan directives; 
              also provides semantics so that you may group together 
              changes to the plan schema. 
 
DBMS_RESOURCE_MANAGER_PRIVS
              Maintain privileges associated with resource consumer groups. 
 
DBMS_RESUMABLE
              Suspend large operations that run out of space or reach space 
              limits after executing for a long time, fix the problem, and 
              make the statement resume execution.
 
DBMS_RLS      Row level security administrative interface.
 
DBMS_ROWID    Procedures to create rowids and to interpret their contents. 
 
DBMS_SESSION  Access to SQL ALTER SESSION statements, and other session 
              information, from stored procedures.
 
DBMS_SHARED_POOL
              Keep objects in shared memory, so that they will not be aged
              out with the normal LRU mechanism.
 
DBMS_SNAPSHOT
              Synonym for DBMS_MVIEW
 
DBMS_SPACE    Segment space information not available through standard SQL.
              How much space is left before a new extent gets allocated?
              How many blocks are above the segments High Water Mark?
              How many blocks are in the free list(s)
 
DBMS_SPACE_ADMIN
              Tablespace and segment space administration not available 
              through the standard SQL.
 
DBMS_SQL      Use dynamic SQL to access the database.
 
DBMS_STANDARD 
              Language facilities that help your application interact 
              with Oracle.
 
DBMS_STATS    View and modify optimizer statistics gathered for database objects.
              In a small test environment this allows faking the stats
              to simulate running a large production database.
 
DBMS_TRACE    Routines to start and stop PL/SQL tracing.
 
DBMS_TRANSACTION
              Access to SQL transaction statements from stored 
              procedures and monitors transaction activities.
 
DBMS_TRANSFORM
              An interface to the message format transformation features 
              of Oracle Advanced Queuing. *
 
DBMS_TTS      Check if a transportable set is self-contained.
 
DBMS_TYPES    Constants, which represent the built-in and user-defined types.

DBMS_URL      Oracle Spatial connection_type ??

DBMS_UTILITY  Utility routines, Analyze, Time, Conversion etc.
 
DBMS_WM       Database Workspace Manager (long transactions) *
 
DBMS_XMLGEN   Convert the results of a SQL query to a canonical XML format. *
 
DMBS_XMLQUERY
              Database-to-XMLType functionality. *
 
DBMS_XMLSAVE
              XML-to-database-type functionality. *
 
DEBUG_EXTPROC
              Debug external procedures on platforms with debuggers 
              that can attach to a running process.
 
OUTLN_PKG     Synonym of DBMS_OUTLN.
 
PLITBLM       Handle index-table operations.(Don't call directly)

SDO_CS,SDO_GEOM,SDO_LRS,SDO_MIGRATE,SDO_TUNE
              see Oracle Spatial User's Guide and Reference 
              Spatial packages are installed in user MDSYS with public synonyms.

STANDARD      Types, exceptions, and subprograms which are
              available automatically to every PL/SQL program. 

UTL_COLL      Collection locators - query and update from a PL/SQL program.
 
UTL_ENCODE    Encode RAW data into a standard encoded format
              so that the data can be transported between hosts. *
 
UTL_FILE      Read and write OS text files via PL/SQL. 
              A restricted version of standard OS stream file I/O. 
 
UTL_HTTP      Enable HTTP callouts from PL/SQL and SQL to access data 
              on the Internet or to call Oracle Web Server Cartridges.
 
UTL_INADDR    A procedure to support internet addressing.
 
UTL_PG        Convert COBOL numeric data into Oracle numbers 
              and convert Oracle numbers into COBOL numeric data. 
 
UTL_RAW       SQL functions for RAW datatypes that concat, 
              substr, etc. to and from RAWS.
 
UTL_REF       Enable a PL/SQL program to access an object by providing a 
              reference to the object.
 
UTL_SMTP      Send SMTP email. The mailer program needs to run on the server,
              but can be invoked from a client.
 
UTL_TCP       Simple TCP/IP-based communication between servers and the outside world.  
 
UTL_URL       Escape and unescape mechanism for URL characters. 
 
ANYDATA TYPE  A self-describing data instance TYPE.
 
ANYDATASET TYPE
              Describe a given TYPE plus a set of data instances of that type.
 
ANYTYPE TYPE  Contains a type description of any persistent SQL type,
              named or unnamed, including object types and collection types.

Oracle PL/SQL

Oracle Procedural SQL extensions:

PL/SQL Structure
  DECLARE - also declaring Table Variables
  BEGIN-EXCEPTION-END

Operators
Conditional Statements - IF

Cursor commands

1) Cursor DECLARE - Define structure & create a named SQL area
2) Cursor OPEN    - Interpret any bind variables and Query the database  
3) Cursor FETCH   - Load the current row into variables
4) Cursor CLOSE   - When there are no more rows to process

Looping Statements - LOOP, WHILE, FOR
Cursor FOR loops

Cursor Variables (REF cursors)

1) REF Cursor DECLARE - Define structure & create a named SQL area
2) REF Cursor OPEN    - Interpret any bind variables and Query the database  
3) REF Cursor FETCH   - Load the current row into variables
4) REF Cursor CLOSE   - When there are no more rows to process

SELECT... INTO v_myvar... ;
INSERT INTO... VALUES... ;
UPDATE... SET... =... WHERE CURRENT OF cursor_name;
DELETE FROM... WHERE CURRENT OF cursor_name;

PL/SQL BEGIN

The BEGIN section

Can contain variable assignments, Embedded SQL and calls to other functions and procedures.

A BEGIN-END block can contain nested DECLARE-BEGIN-END sub blocks.
The use of nested sub-blocks allows the use of local variables with limited scope.

Each plsql block should be terminated with / on a line by itself.

BEGIN
   code block
   /
EXCEPTION
   code block
   /
END;

Exceptions

Oracle includes about 20 predefined exceptions (errors) - we can allow Oracle to raise these implicitly.

For errors that don't fall into the predefined categories - declare in advance and allow oracle to raise an exception.

For problems that are not recognised as an error by Oracle - but still cause some difficulty within your application - declare a User Defined Error and raise it explicitly
i.e IF x >20 then RAISE ...

Syntax:

EXCEPTION
   WHEN exception1 [OR exception2...]] THEN
   ...
   [WHEN exception3 [OR exception4...] THEN
   ...]
   [WHEN OTHERS THEN
   ...]

Where exception is the exception_name e.g. WHEN NO_DATA_FOUND... Only one handler is processed before leaving the block.

Trap non-predefined errors by declaring them You can also associate the error no. with a name so that you can write a specific handler.
This is done with the PRAGMA EXCEPION_INIT pragma.

PRAGMA (pseudoinstructions) indicates that an item is a 'compiler directive' Running this has no immediate effect but causes all subsequent references to the exception name to be interpreted as the associated Oracle Error.

-

Trapping a non-predefined Oracle server exception

DECLARE
   -- name for exception
   e_emps_remaining EXCEPTION
   PRAGMA_EXCEPTION_INIT (
      e_emps_remaining, -2292);
   v_deptno dept.deptno%TYPE :=&p_deptno;

BEGIN
   DELETE FROM dept
   WHERE deptno = v_deptno
   COMMIT;
EXCEPTION
   WHEN e_emps_remaining THEN
   DBMS_OUTPUT.PUT_LINE ('Cannot remove dept '||
   TO_CHAR(v_deptno) || '. Employees exist. ');
END;

When an exception occurs you can identify the associated error code/message with two supplied functions SQLCODE and SQLERRM

SQLCODE - Number
SQLERRM - message

An example of using these:
DECLARE
   v_error_code NUMBER;
   v_error_message VARCHAR2(255);

BEGIN

   ...

EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
   v_error_code := SQLCODE
   v_error_message := SQLERRM
   INSERT INTO t_errors
   VALUES ( v_error_code, v_error_message);
END;

Trapping user-defined exceptions

DECLARE the exception
RAISE the exception
Handle the raised exception

e.g.
DECLARE
  e_invalid_product EXCEPTION
BEGIN
   update PRODUCT
   SET descrip = '&prod_descr'
   WHERE prodid = &prodnoumber';
   IF SQL%NOTFOUND THEN
     RAISE e_invalid_product;
   END IF;
   COMMIT;
EXCEPTION
   WHEN e_invalid_product THEN
   DBMS_OUTPUT.PUT_LINE ('INVALID PROD NO');
END;

Propagation of Exception handling in sub blocks

If a sub block does not have a handler for a particular error it will propagate to the enclosing block - where it can be caught by more general exception handlers.

RAISE_APPLICATION_ERROR (error_no, message[,{TRUE|FALSE}]);

This procedure  allows user defined error 
messages from stored sub programs - call only from stored sub prog.
error_no = a user defined no (between -20000 and -20999)

TRUE = stack errors
FALSE = keep just last

This can either be used in the executable section of code or 
the exception section

e.g.
EXCEPTION
   WHEN NO_DATA_FOUND THEN
   RAISE_APPLICATION_ERROR (-2021,
        'manager not a valid employee');
END;

Standard Exceptions, from the the STANDARD package

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You attempted to create a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	A resource timed out, took too long.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	The cursor does not yet exist. The cursor must be OPENed before any FETCH cursor or CLOSE cursor operation.
NOT_LOGGED_ON	ORA-01012	You are not logged on.
LOGIN_DENIED	ORA-01017	Invalid username/password.
NO_DATA_FOUND	ORA-01403	No data was returned
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	Divide by zero error.
INVALID_NUMBER	ORA-01722	Converting a string to a number was unsuccessful.
STORAGE_ERROR	ORA-06500	Out of memory.
PROGRAM_ERROR	ORA-06501	Generic "Contact Oracle support" message.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
ROWTYPE_MISMATCH	ORA-06504
CURSOR_ALREADY_OPEN	ORA-06511	The cursor is already open.
ACCESS_INTO_NULL	ORA-06530
COLLECTION_IS_NULL	ORA-06531

What is a Trigger?

What is a Trigger?

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

The Syntax for creating a trigger is:

 CREATE [OR REPLACE ] TRIGGER trigger_name 

 {BEFORE | AFTER | INSTEAD OF } 

 {INSERT [OR] | UPDATE [OR] | DELETE} 

 [OF col_name] 

 ON table_name 

 [REFERENCING OLD AS o NEW AS n] 

 [FOR EACH ROW] 

 WHEN (condition)  

 BEGIN 

   --- sql statements  

 END; 

• CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
• {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
• [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
• CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
• [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
• [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
• [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
• WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.
We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.
1) Create the 'product' table and 'product_price_history' table

CREATE TABLE product_price_history 

(product_id number(5), 

product_name varchar2(32), 

supplier_name varchar2(32), 

unit_price number(7,2) ); 

CREATE TABLE product 

(product_id number(5), 

product_name varchar2(32), 

supplier_name varchar2(32), 

unit_price number(7,2) ); 

2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger 

BEFORE UPDATE OF unit_price 

ON product 

FOR EACH ROW 

BEGIN 

INSERT INTO product_price_history 

VALUES 

(:old.product_id, 

 :old.product_name, 

 :old.supplier_name, 

 :old.unit_price); 

END; 

/ 

3) Lets update the price of a product.

UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100

Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.
1) Row level trigger - An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger - An event is triggered for each sql statement executed.

PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.
For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired.

CREATE TABLE product

(Message varchar2(50), 

 Current_Date number(32)

);

Let's create a BEFORE and AFTER statement and row level triggers for the product table.
1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' before a sql update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER Before_Update_Stat_product 

BEFORE 

UPDATE ON product 

Begin 

INSERT INTO product_check 

Values('Before update, statement level',sysdate); 

END; 

/ 

2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'product_check' before each row is updated.

 CREATE or REPLACE TRIGGER Before_Upddate_Row_product 

 BEFORE 

 UPDATE ON product 

 FOR EACH ROW 

 BEGIN 

 INSERT INTO product_check 

 Values('Before update row level',sysdate); 

 END; 

 / 

3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' after a sql update statement is executed, at the statement level.

 CREATE or REPLACE TRIGGER After_Update_Stat_product 

 AFTER 

 UPDATE ON product 

 BEGIN 

 INSERT INTO product_check 

 Values('After update, statement level', sysdate); 

 End; 

 / 

4) AFTER UPDATE, Row Level: This trigger will insert a record into the table 'product_check' after each row is updated.

 CREATE or REPLACE TRIGGER After_Update_Row_product 

 AFTER  

 insert On product 

 FOR EACH ROW 

 BEGIN 

 INSERT INTO product_check 

 Values('After update, Row level',sysdate); 

 END; 

 / 

Now lets execute a update statement on table product.

 UPDATE PRODUCT SET unit_price = 800  

 WHERE product_id in (100,101); 

Lets check the data in 'product_check' table to see the order in which the trigger is fired.

 SELECT * FROM product_check; 

Output:
Mesage                                             Current_Date
------------------------------------------------------------
Before update, statement level          26-Nov-2008
Before update, row level                    26-Nov-2008
After update, Row level                     26-Nov-2008
Before update, row level                    26-Nov-2008
After update, Row level                     26-Nov-2008
After update, statement level            26-Nov-2008
The above result shows 'before update' and 'after update' row level events have occured twice, since two records were updated. But 'before update' and 'after update' statement level events are fired only once per sql statement.
The above rules apply similarly for INSERT and DELETE statements.

How To know Information about Triggers.

We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.
The below statement shows the structure of the view 'USER_TRIGGERS'

 DESC USER_TRIGGERS; 

NAME                              Type
--------------------------------------------------------
TRIGGER_NAME                 VARCHAR2(30)
TRIGGER_TYPE                  VARCHAR2(16)
TRIGGER_EVENT                VARCHAR2(75)
TABLE_OWNER                  VARCHAR2(30)
BASE_OBJECT_TYPE           VARCHAR2(16)
TABLE_NAME                     VARCHAR2(30)
COLUMN_NAME                  VARCHAR2(4000)
REFERENCING_NAMES        VARCHAR2(128)
WHEN_CLAUSE                  VARCHAR2(4000)
STATUS                            VARCHAR2(8)
DESCRIPTION                    VARCHAR2(4000)
ACTION_TYPE                   VARCHAR2(11)
TRIGGER_BODY                 LONG
This view stores information about header and body of the trigger.

SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product'; 

The above sql query provides the header and body of the trigger 'Before_Update_Stat_product'.
You can drop a trigger using the following command.

DROP TRIGGER trigger_name;

CYCLIC CASCADING in a TRIGGER

This is an undesirable situation where more than one trigger enter into an infinite loop. while creating a trigger we should ensure the such a situtation does not exist.
The below example shows how Trigger's can enter into cyclic cascading.
Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created.
1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'.
2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.
In such a situation, when there is a row inserted in table 'abc', triggerA fires and will update table 'xyz'.
When the table 'xyz' is updated, triggerB fires and will insert a row in table 'abc'.
This cyclic situation continues and will enter into a infinite loop, which will crash the database.