Blog - Rajiv Sharma

How to create an Oracle PL/SQL package body

Rajiv Sharma — Thu, 19 Feb 2009 16:29:07 GMT

There are many benefits to encapsulating PL/SQL functions and stored procedures into packages:

Better PL/SQL performance. Oracle stored procedures load once into the shared pool and remain there unless they become paged out.

Coupling of data with methods. DBAs can use naming conventions with Oracle packages to couple tables with the PL/SQL associated with a table, essentially using Oracle stored procedures as "methods".

Isolation of PL/SQL code. Since all PL/SQL anonymous blocks are moved out of the external programs and into the Oracle stored procedures, the application programs become nothing more than calls to stored procedures. As such, it becomes very simple to swap out one database and swap in another one.
One of the foremost reasons PL/SQL packages function faster than traditional code is related to caching in the Oracle SGA. Once loaded into the RAM memory of the shared pool, PL/SQL packages will execute very quickly.

The package body includes the definition of procedures and functions declared in the package spec and, like the package spec, can also include variables, constants, user-defined exceptions, and user-defined datatypes.

Unlike objects created in a PL/SQL package specification, variables and other constructs defined within a package body are private to the package body. These constructs can only be referenced by procedures and functions within the package body. Stored procedures are created inside a package without using the CREATE PROCEDURE command. Instead, a procedure’s definition is defined as part of the CREATE PACKAGE BODY command.

Here is an example of the Oracle create package body command:

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
tot_emps NUMBER;
tot_depts NUMBER;
FUNCTION hire
. .
RETURN(new_empno);
END;
FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER IS
   new_deptno NUMBER;
BEGIN
   SELECT . . .
END;
PROCEDURE remove_emp (employee_id NUMBER) IS
BEGIN
   DELETE . . .
END;
PROCEDURE remove_dept(department_id NUMBER) IS
BEGIN
   DELETE FROM . .
END;
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
curr_sal NUMBER;
BEGIN
   SELECT . .
END;
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
curr_comm NUMBER;
BEGIN
   SELECT . .
END;
END emp_mgmt;
/

The DBA will be required to alter a package when there are changes to tables, views, sequences, and so on that the package procedures and functions reference. This is accomplished through the use of the CREATE OR REPLACE PACKAGE [BODY] form of the CREATE PACKAGE command.

From www.dba-oracle.com

Using Oracle Supplied Packages

Most Oracle supplied packages are automatically installed when the database is created and the CATPROC.SQL script is run. For example, to create the DBMS_ALERT package, the DBMSALRT.SQL and PRVTALRT.PLB scripts must be run when connected as the user SYS. These scripts are run automatically by the CATPROC.SQL script.

Certain packages are not installed automatically. Special installation instructions for these packages are documented in the individual chapters.

To call a PL/SQL function from SQL, you must either own the function or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.

Creating New Packages

To create packages and store them permanently in an Oracle database, use the CREATE PACKAGE and CREATE PACKAGE BODY statements. You can execute these statements interactively from SQL*Plus or Enterprise Manager.

To create a new package, do the following:

Create the package specification with the CREATE PACKAGE statement.
You can declare program objects in the package specification. Such objects are called public objects. Public objects can be referenced outside the package, as well as by other objects in the package.
Note:
It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE statement.
Create the package body with the CREATE PACKAGE BODY statement.
You can declare and define program objects in the package body.
- You must define public objects declared in the package specification.
- You can declare and define additional package objects, called private objects. Private objects are declared in the package body rather than in the package specification, so they can be referenced only by other objects in the package. They cannot be referenced outside the package.

Separating the Specification and Body

The specification of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Oracle stores the specification and body of a package separately in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body. Using this distinction, you can change the definition of a program object in the package body without causing Oracle to invalidate other schema objects that call or reference the program object. Oracle invalidates dependent schema objects only if you change the declaration of the program object in the package specification.

Referencing Package Contents

To reference the types, items, and subprograms declared in a package specification, use the dot notation. For example:

package_name.type_namepackage_name.item_namepackage_name.subprogram_name

From cs.scu.edu.cn

Blog Posted on: Thursday, February 19, 2009 at 04:29-PM

How to Escape Characters in Oracle PL/SQL Queries

Rajiv Sharma — Fri, 20 Feb 2009 15:14:04 GMT

Oracle databases reserve some special characters with specific meaning and purpose within Oracle environment. These reserved characters include _ (underscore) wild card character which used to match exactly one character, % (percentage) which used to match zero or more occurrences of any characters and ‘ (apostrophe or quotation mark) which used to mark the value supplied. These special characters will not be interpreted literally when building SQL query in Oracle, and may caused error in results returned especially when performing string search with LIKE keyword. To use these characters so that Oracle can interpret them literally as a part of string value instead of preset mean, escape character has to be assigned.

Oracle allows the assignment of special escape characters to the reserved characters in Oracle can be escaped to normal characters that is interpreted literally, by using ESCAPE keyword.

For example, to select the name of guests with _ (underscore) in it, use the following statement:

SELECT guest_name FROM guest_table WHERE name LIKE ‘%\_%’ ESCAPE ‘\’;

Without specifying the \ (backslash) as escape clause, the query will return all guest names, making the unwanted results problem.

The above syntax will not work on ‘ (quote). To escape this quotation mark and to display the quote literally in string, insert another quote (total 2 quotes) for every quote that want to be displayed. For example:

SELECT ‘This will display line with quote’’s word.’ FROM temp_table;
SELECT ‘This will display ””double quoted”” word.’ FROM temp_table;

will return the following respectively:

This will display line with quote’s word.
This will display ”double quoted” word.

From www.mydigitallife.info

Blog Posted on: Friday, February 20, 2009 at 03:14-PM

How to Find and Replace Text in MySQL Database using SQL

Rajiv Sharma — Fri, 20 Feb 2009 15:14:04 GMT

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)

The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Another example:

SELECT REPLACE(’www.mysql.com’, ‘w’, ‘Ww’);

Above statement will return ‘WwWwWw.mysql.com’ as result.

From www.mydigitallife.info

Blog Posted on: Friday, February 20, 2009 at 03:14-PM

Oracle Create Sequence

Rajiv Sharma — Sat, 21 Feb 2009 12:35:55 GMT

The oracle docs note the syntax for the Oracle CREATE SEQUENCE function as follows:

CREATE SEQUENCE [ schema. ]sequence
   [ { INCREMENT BY | START WITH } integer
   | { MAXVALUE integer | NOMAXVALUE }
   | { MINVALUE integer | NOMINVALUE }
   | { CYCLE | NOCYCLE }
   | { CACHE integer | NOCACHE }
   | { ORDER | NOORDER }
   ]
     [ { INCREMENT BY | START WITH } integer
     | { MAXVALUE integer | NOMAXVALUE }
     | { MINVALUE integer | NOMINVALUE }
     | { CYCLE | NOCYCLE }
     | { CACHE integer | NOCACHE }
     | { ORDER | NOORDER }
     ]... ;

Let's look at this example for using the Oracle CREATE SEQUENCE:

SQL> create sequence pubs1;

Sequence created.

SQL> select pubs1.nextval from dual;

NEXTVAL
----------
1

SQL> select pubs1.nextval from dual;

NEXTVAL
----------
2

In the example above, we used CREATE SEQUENCE to create a sequence called pubs1. We use the NEXTVAL function to retrieve the next value. You can get the current value by calling the CURRVAL function. You must call the NEXTVAL function before calling the CURRVAL function, or you will get an error. There is no current value to the sequence until the next value has been called at least once.

When you use Oracle CREATE SEQUENCE to create the sequence, you have a lot of flexibility as to how the sequence generates the next number.

SQL> create sequence pubs2
2 start with 8
3 increment by 2
4 maxvalue 10000
5 cycle
6 cache 5;

Sequence created.

SQL> select pubs2.nextval from dual;

NEXTVAL
----------
8

In this example, using Oracle CREATE SEQUENCE, the sequence pubs2 will start at number eight and increment by twos to 10000 and then start over. Let’s cover each of their parameters:

INCREMENT BY

Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

START WITH

Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.

Note:

This value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.

MAXVALUE

Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.

NOMAXVALUE

Specify NOMAXVALUE to indicate a maximum value of 10²⁷ for an ascending sequence or -1 for a descending sequence. This is the default.

MINVALUE

Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

NOMINVALUE

Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -10²⁶ for a descending sequence. This is the default.

CYCLE

Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

NOCYCLE

Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

CACHE

Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Note:

Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.

NOCACHE

Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

ORDER

Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

NOORDER

Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

Example:

Creating a Sequence:

The following statement creates the sequence customers_seq in the sample schema oe. This sequence could be used to provide customer ID numbers when rows are added to the customers table.

CREATE SEQUENCE customers_seq START WITH     1000 INCREMENT BY   1 NOCACHE NOCYCLE;

The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.

Blog Posted on: Saturday, February 21, 2009 at 12:35-PM

Send email from Oracle Database

Rajiv Sharma — Sat, 21 Feb 2009 12:45:49 GMT

Introduction
This code shows how to send email using Oracle PL/SQL / Oracle Database.

Language and Platform
Oracle9i database server

Code
create or replace package mail_pkg
    as
            type array is table of varchar2(255);

            procedure send( p_sender_email in varchar2,
                            p_from         in varchar2,
                            p_to           in array default array(),
                            p_cc           in array default array(),
                            p_bcc          in array default array(),
                           p_subject      in varchar2,
                           p_body         in long );
   end;

create or replace package body mail_pkg
    as

    g_crlf        char(2) default chr(13)||chr(10);
   g_mail_conn   utl_smtp.connection;
   g_mailhost    varchar2(255) := 'chiana.kyamk.fi';

   function address_email( p_string in varchar2,
                           p_recipients in array ) return varchar2
   is
       l_recipients long;
   begin
      for i in 1 .. p_recipients.count
      loop
         utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
         if ( l_recipients is null )
         then
             l_recipients := p_string || p_recipients(i) ;
         else
             l_recipients := l_recipients || ', ' || p_recipients(i);
         end if;
      end loop;
      return l_recipients;
   end;

   procedure send( p_sender_email in varchar2,
                   p_from         in varchar2 default NULL,
                   p_to           in array default array(),
                   p_cc           in array default array(),
                   p_bcc          in array default array(),
                   p_subject      in varchar2 default NULL,
                   p_body         in long default NULL )
   is
       l_to_list   long;
       l_cc_list   long;
       l_bcc_list long;
       l_date      varchar2(255) default
                   to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );

       procedure writeData( p_text in varchar2 )
       as
       begin
           if ( p_text is not null )
           then
               utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
           end if;
       end;
   begin
       g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);

       utl_smtp.helo(g_mail_conn, g_mailhost);
       utl_smtp.mail(g_mail_conn, p_sender_email);

       l_to_list := address_email( 'To: ', p_to );
       l_cc_list := address_email( 'Cc: ', p_cc );
       l_bcc_list := address_email( 'Bcc: ', p_bcc );

       utl_smtp.open_data(g_mail_conn );

       writeData( 'Date: ' || l_date );
       writeData( 'From: ' || nvl( p_from, p_sender_email ) );
       writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );

       writeData( l_to_list );
       writeData( l_cc_list );

       utl_smtp.write_data( g_mail_conn, '' || g_crlf );
       utl_smtp.write_data(g_mail_conn, p_body );
       utl_smtp.close_data(g_mail_conn );
       utl_smtp.quit(g_mail_conn);
   end;

   end;

begin
        mail_pkg.send
        ( p_sender_email => 'iam@rajivsharma.com',
          p_from => 'Oracle Database Account <'iam@rajivsharma.com'>',
          p_to => mail_pkg.array( 'iam@rajivsharma.com',
                                           'info@rajivsharma.com' ),
          p_cc => mail_pkg.array( 'iam@rajivsharma.com' ),
          p_bcc => mail_pkg.array( 'iam@rajivsharma.com' ),
          p_subject => 'This is Testing',
          p_body => 'Hello, Testing Oracle DB Mail Sending Capabilities' );
   end;

From it.toolbox.com

Blog Posted on: Saturday, February 21, 2009 at 12:45-PM

Sending content-type: html e-mail from an Oracle database with utl_smtp

Rajiv Sharma — Sat, 21 Feb 2009 13:56:33 GMT

If you're running Oracle 10g or later, you can use the nice modern utl_mail package to send e-mails from your PL/SQL applications. Even though the application I maintain is now running on Oracle 10g, it was built back on an Oracle 8i database, so it sends e-mails via the older (and more complicated) utl_smtp package.

Code Syntax to send Content Type HTML emails using Oracle database:

utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);

To begin, the utl_smtp package has to be installed (in the SYS schema, naturally). If it wasn't part of your install, you can find the utlsmtp.sql script in your ORACLE_HOME\RDBMS\admin directory. You'll also need utl_tcp; again, if it's not already loaded, the utltcp.sql script for that is in the same location as the utlsmtp.sql. Finally, you will need to know the URL for your corporate SMTP server. (Note: This example won't work with secured SMTP servers like Gmail.)

The spec for our little e-mail package is fairly straightforward:

create or replace PACKAGE sendmail IS

  procedure send (p_sender varchar2,
                  p_recipient varchar2,
                  p_subject varchar2,
                  p_body varchar2 default null);

end sendmail;

For the body, you'll notice that the public method send relies on a private method called common because I want to extend this package later and show how to send Binary Large OBject (blob) attachments. For example, if you've generated a PDF and stored it in your database, you might want to e-mail it as an attachment; the common method is in preparation for that. The code that will be used is from the basic send method and the send_blob method.

Here's the package body:

create or replace PACKAGE BODY sendmail IS

  procedure common (p_sender varchar2,
                    p_recipient varchar2,
                    p_subject varchar2,
                    c out utl_smtp.connection) is

    v_recipient varchar2(1000);

  begin

    --make connection to smtp
    c := utl_smtp.open_connection('smtp.example.com');

    --identify the domain of the sender
    utl_smtp.helo(c, 'example.com');

    --start a mail, specify the sender
    utl_smtp.mail(c, p_sender);

    --identify recipient
    utl_smtp.rcpt(c, v_recipient);

    --start the mail body
    utl_smtp.open_data(c);

    utl_smtp.write_data(c, 'From: ' || p_sender || utl_tcp.crlf);
    utl_smtp.write_data(c, 'To: ' || p_recipient || utl_tcp.crlf);
    utl_smtp.write_data(c, 'Subject: ' || p_subject || utl_tcp.crlf);

  exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
      utl_smtp.quit(c);
      raise;
    when others then
      raise;
  end common;

  procedure send (p_sender varchar2,
                  p_recipient varchar2,
                  p_subject varchar2,
                  p_body varchar2 default null) is

    c utl_smtp.connection;

  begin

    common(p_sender, p_recipient, p_subject, c);

    utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);
    utl_smtp.write_data(c, utl_tcp.crlf || p_body);
    utl_smtp.close_data(c);
    utl_smtp.quit(c);

  exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
      utl_smtp.quit(c);
      raise;
    when others then
      raise;
  end send;

end sendmail;

There are a couple of places above where you'll need to substitute your specific information. First is the line where I provide the SMTP server:

    --make connection to smtp    c := utl_smtp.open_connection('smtp.example.com');

That's where you'll want to put whatever your corporate SMTP server happens to be. And the second place is where you identify your domain:

    --identify the domain of the sender    utl_smtp.helo(c, 'example.com');

Again, replace that with whatever your domain really is. That's all you'll need to get the basic e-mail functionality working.

To call this, you'd use something like:

begin
  sendmail.send ('sender@example.com',
                  'recipient@example.com',
                  'Subject: Testing',
                  'Test Email!');
end;

You'll notice that the body string I gave above has HTML embedded in it. This is because, in the send method, I set the content type to be text/html:

    utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);

From www.builderau.com.au

Blog Posted on: Saturday, February 21, 2009 at 01:56-PM

Oracle Triggers

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

What is a trigger?

A trigger is a special case of stored procedure that is fired during an event rather than being explicitly executed. A function or a procedure can be called from a command line or from within a different calling program. A trigger is called automatically when an event is fired.

In Oracle, a trigger can have the procedural code embedded in the body of the trigger, you can call out to another existing function or procedure, or a combination of both. The code in a trigger is generally the same as any procedure or function. You are allowed to create Java triggers but I see this rarely in live systems.

You cannot execute transaction control statements with in a trigger. That means no commits or rollbacks. If you need to rollback a transaction due to logic in a trigger, raise an exception and allow the calling program to take the appropriate action. As for commits, the calling application should always commit or rollback as needed.

Technically, there is a way to execute transaction control within a trigger but that can be abused, and really damage your data integrity, if done poorly. I will leave that topic for a discussion on advanced PL/SQL.

What are trigger events?

Various databases support different triggering events. Most all enterprise class databases support DML triggers (excluding SELECT). That means that the database will optionally fire anytime a table has a row updated, deleted or inserted. Many databases, including Oracle, also support DDL triggers. These fire when you create, alter or drop an object.

Oracle supports many events in addition to DML events. Oracle supports system events such as the database starting or stopping or when there is a server error. Oracle also supports user events such as a user logging on or off.

For a new user or developer, it is important that you understand DML triggers as you will find many applications that are Dependant on triggers. DDL, user and system triggers tend to have more advanced uses although you will see examples of those for various security related processing (such as VPD).

For the purpose of this blog entry, I'll stick to DML triggers.

Trigger Syntax

There are three main types of DML triggers: before, after and instead of. A before or after trigger can be a statement level trigger or a row level trigger.

Before

Obviously, a before statement trigger will fire before a statement is executed. A before row level trigger fires before each row that is affected by a statement.

After

An after statement trigger will fire after a statement is fired. A before row level trigger fires before each row that is affected by a statement.

Statement vs Row

The difference between a statement level trigger and a row level trigger is very easy to understand. If you perform an update that will affect 10 rows, a statement level trigger will fire once and a row level trigger will fire 10 times.

A statement level trigger does not have access to individual column values. A row level trigger does have access to column values. A before row trigger can change new column values.

Instead Of

An instead of trigger is built on a view. Many views are updateable with an instead of trigger. Many more views are not updateable for various reasons. With an instead of trigger, you can update any of these views. As the name implies, an instead of trigger is executed instead of Oracle trying to execute the command directly against the view.

Trigger Syntax

The syntax of a trigger varies database by database but for Oracle and PL/SQL (as well as for EnterpriseDB and SPL), the basic syntax of a DML trigger is:

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE|AFTER|INSTEAD OF       
  INSERT|DELETE|UPDATE ON table_name 
         [OF column_names]
  [REFERENCING [NEW AS new_cols] [OLD AS old_cols]]
  [FOR EACH ROW [WHEN (where_condition)]]
  [DECLARE]
  BEGIN
  EXCEPTION
  END;

Let's look at the command row by row. Anything within square brackets ([]) is an optional clause. Any items delimited with a pipe (|) is part of a list.

Row 1: The create or replace should seem familiar if you are familiar with procedures and functions. Naming of a trigger follows standard naming conventions and rules.

Row 2: The BEFORE|AFTER|INSTEAD OF clause tells the database what type of trigger it will be.

Row 3: This line allows you define if the triggering event will be on UPDATE, DELETE, INSERT, a combination or all of these. The "ON table_name" identifies the table for which the trigger will be created.

Row 4: An optional clause is the "OF column_names". This statement only affects an UPDATE. If you only want to fire an update trigger when certain columns are modified, you may include this optional statement and list the columns that should be monitored.

Row 5: In a row level trigger, you have the values of the columns as they existed before the command and the new values as a result of the command. For an insert, there are no old values and for a delete, there are no new values. In an update, you have both old and new.

The REFERENCING clause allows you to name the old row and the new row. By default, they are named :OLD and :NEW. You refer to them as :OLD.column_name and :NEW.column_name. You can query them in both before and after row triggers, like:

IF :old.gl_id = :new.gl_id THEN...

In a before row trigger, you can even set the values of :new columns, i.e.

:new.gl_id := sequence.nextval();

Statement level triggers cannot access row level column data.

Row 6: A DML trigger, by default, is a statement level trigger. When you use "FOR EACH ROW", you turn it into a row level trigger.

The WHEN clause allows you to introduce additional matching criteria that Oracle will test before executing a trigger. It is standard where clause type criteria,

FOR EACH ROW WHEN (old.order_date BETWEEN sysdate - 10 and sysdate)

Row 7 and on are standard PL/SQL syntax. Think of them as an anonymous block attached to the trigger name and criteria.

Example

Here is a very simple trigger using the Employees table from Oracle XE.

SQL> desc employees
Name Null? Type
----------------------------------------- -------- ------------

EMPLOYEE_ID                               NOT NULL NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
LAST_NAME                                 NOT NULL VARCHAR2(25)
EMAIL                                     NOT NULL VARCHAR2(25)
PHONE_NUMBER                                       VARCHAR2(20)
HIRE_DATE                                 NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                      NUMBER(4)
Now, I'll create the trigger on employees. The trigger will only fire on insert or update (not delete), update will only fire when email or hire_date is modified and the trigger body will only fire when the :old.salary is greater than 7500. Because it will only fire when an :old value is set, it will never fire for an insert, even though I am defining it for insert.

SQL> create or replace trigger emp_email
2 before insert or update of email, hire_date
3 on employees
4 for each row when (old.salary > 7500)
5 begin
6 dbms_output.put_line('Trigger executing');
7 end;
8 /

Trigger created.
Next, I'll fire two inserts to add a couple of records. Only one of the records will have a salary > 7500. After the inserts, I will update each record. Only the record with a salary > 7500 will display the trigger message.

SQL> set serveroutput on

SQL> insert into employees
2 (employee_id, last_name, email,
3 hire_date, job_id, salary)
4 values
5 (101010, 'Smith', 'smith@barney.com',
6 sysdate, 'PR_REP', 5000);

1 row created.

SQL> insert into employees
2 (employee_id, last_name, email,
   3 hire_date, job_id, salary)
   4 values
   5 (101011, 'Jones', 'jones@barney.com',
   6 sysdate, 'PR_REP', 8000);

1 row created.

SQL> commit;

Commit complete.

   SQL> update employees set
   2 email = 'under7500@noexec.com'
   3 where employee_id = 101010;

1 row updated.

   SQL> update employees set
   2 email = 'over7500@willexec.com'
   3 where employee_id = 101011;
   Trigger executing

1 row updated.

SQL> commit;

Commit complete.

SQL>

That's pretty much it for DML triggers.

From it.toolbox.com by LewisC

Blog Posted on: Friday, February 27, 2009 at 08:31-AM

Oracle/PLSQL: BEFORE INSERT Trigger

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.

The syntax for an BEFORE INSERT Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.
For example:

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2),
create_date date,
created_by varchar2(10)
);

We could then create a BEFORE INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing INSERT into table
    SELECT user INTO v_username
    FROM dual;

-- Update create_date field to current system date
:new.create_date := sysdate;

-- Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;

END;

From www.techonthenet.com

Blog Posted on: Friday, February 27, 2009 at 08:31-AM

Oracle/PLSQL: AFTER INSERT Trigger

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.

The syntax for an AFTER INSERT Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

You can not create an AFTER trigger on a view.
You can not update the :NEW values.
You can not update the :OLD values.
For example:

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);

We could then create an AFTER INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN
    -- Find username of person performing the INSERT into the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       username )
    VALUES
     ( :new.order_id,
       :new.quantity,
       :new.cost_per_item,
       :new.total_cost,
       v_username );

END;

From www.techonthenet.com

Blog Posted on: Friday, February 27, 2009 at 08:31-AM

Oracle/PLSQL: BEFORE UPDATE Trigger

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.

The syntax for an BEFORE UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.

For example:

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2),
updated_date date,
updated_by varchar2(10)
);

We could then create a BEFORE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE on the table
    SELECT user INTO v_username
    FROM dual;

-- Update updated_date field to current system date
:new.updated_date := sysdate;

-- Update updated_by field to the username of the person performing the UPDATE
:new.updated_by := v_username;

END;

From www.techonthenet.com

Blog Posted on: Friday, February 27, 2009 at 08:31-AM

Oracle/PLSQL: AFTER UPDATE Trigger

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.

The syntax for an AFTER UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

You can not create an AFTER trigger on a view.
You can not update the :NEW values.
You can not update the :OLD values.
For example:

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);

We could then create an AFTER UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE into table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity_before,
       quantity_after,
       username )
    VALUES
     ( :new.order_id,
       :old.quantity,
       :new.quantity,
       v_username );

END;

From www.techonthenet.com

Blog Posted on: Friday, February 27, 2009 at 08:31-AM

Oracle/PLSQL: BEFORE DELETE Trigger

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.

The syntax for an BEFORE DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.
For example:

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);

We could then create a BEFORE DELETE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by )
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

From www.techonthenet.com

Blog Posted on: Friday, February 27, 2009 at 08:31-AM

Oracle/PLSQL: AFTER DELETE Trigger

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.

The syntax for an AFTER DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

You can not create an AFTER trigger on a view.
You can not update the :NEW values.
You can not update the :OLD values.
For example:

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);

We could then create an DELETE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by)
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

From www.techonthenet.com

Blog Posted on: Friday, February 27, 2009 at 08:31-AM

Oracle/PLSQL: Drop a Trigger

Rajiv Sharma — Fri, 27 Feb 2009 08:31:21 GMT

The syntax for a dropping a Trigger is:

DROP TRIGGER trigger_name;

For example:

If you had a trigger called orders_before_insert, you could drop it with the following command:

DROP TRIGGER orders_before_insert;

From www.techonthenet.com

Blog Posted on: Friday, February 27, 2009 at 08:31-AM