Introduction to PL/SQL By Example

This is a simple introduction to Oracle's PL/SQL language. Basic knowledge of SQL is assumed. It is aimed especially at Infrastructure DBAs.

For a full treatment of PL/SQL, Oracle's PL/SQL Language Reference is excellent. Especially Chapter 1, Overview of PL/SQL, which has plenty of good code examples.

Links direct to that are:

  • 11gR2 PL/SQL Language Reference, Chapter 1 Overview of PL/SQL
  • 11gR1 PL/SQL Language Reference, Chapter 1 Overview of PL/SQL
  • 10gR2 PL/SQL User's Guide and Reference, Chapter 1 Overview of PL/SQL

    Also excellent is Steve Feurstein, Oracle Pl/SQL Programming, O'Reilly Media. The current 5th edition of 2009 has to be purchsed, but the 2nd edition of 1997 is published for free online.

    And Tom Kyte has many searchable examples, answers and tips at his asktom site. Worth noting in particular are his reasons to use implicit in preference to explicit cursors here and here.

    I have a post on effective comments in PL/SQL.

    Oracle SQL Developer is a free download (from here) GUI that helps with writing and debugging large PL/SQL scripts.

    Why Should DBAs learn PL/SQL?

    1. To provide assistance and answer questions from developers.
    2. To write DBA cron and batch processing scripts.
    3. To help background understanding during performance tuning issues where PL/SQL code is involved.

    Microsoft SQL Server and MySQL

    SQL Server has Transact-SQL as an equivalent to PL/SQL, documented here. SQL Server also comes with the excellent procedural code GUI called SQL Server Integration Services, which DBAs would often use in place of T-SQL. MySQL has compound statements, documented here.

    0) Set Serverout On

    Note that for all the below code examples to output text in SQL*Plus, you first need to set serverout on: 

    SQL> set serverout on

    (In ancient versions of oracle, the default serverout size was small, so commands like this were used: 

    SQL> set serverout on size 1000000

    That is unneccesary from 10gR2 onwards, because serverout size is now unlimited by default.)

    In Oracle SQL Developer, set serverout on by selecting "View" > "Dbms Output" and then also click the green plus button in the Dbms Output tab and select the database connection you want to work with.

    Remember - if you don't set serverout on, you won't see any output.

    1) Simple PL/SQL 

    exec dbms_output.put_line('Hello world!')

    (in SQL: 

    SQL> prompt Hello world!
    )

    2) Begin..End Construct 

    BEGIN
   dbms_output.put_line('Hello world!') ;
END ;
/
    Note indentation - not required, but good practice for readability.
    Note semicolons and slash - they are required.

    3) Declare..Begin..End Construct 

    DECLARE
   mytext varchar2(100) ;  
BEGIN
   mytext := 'Hello world!' ;
   dbms_output.put_line(mytext) ;
   /* and again */
   dbms_output.put_line(mytext) ;
END ;
/
    Only needed when you want a reusable variable.
    Note the comment line.

    4) Declare a Variable and Initialise it with a Value 

    DECLARE
   mytext varchar2(100) := 'Hello world!' ;  
BEGIN
   dbms_output.put_line(mytext) ;
   dbms_output.put_line(mytext) ;
END ;
/

    5) Select a Single Value - Use Into 

    DECLARE
   mytext varchar2(100) ;  
BEGIN
   select 'Hello world!' INTO mytext FROM dual ;
   dbms_output.put_line(mytext) ;
END ;
/
    Note the use of into in the select statement.
    That would return an error if more than one row was returned by the select statement.

    6) Select a Lot of Values - Use a Cursor For Loop: 

    BEGIN
   FOR c1 IN (select tablespace_name from dba_tablespaces order by 1)
   LOOP
      dbms_output.put_line('Working on : '||c1.tablespace_name) ;
   END LOOP ;
END ;
/
    Note the concatenation of two strings in dbms_output.put_line using the || symbols as in SQL.

    (in SQL: 

    select 'Working on : '||tablespace_name from dba_tablespaces order by 1;
    )

    7) Nested Cursor For Loops 

    BEGIN
   FOR c1 IN (select tablespace_name from dba_tablespaces order by 1)
   LOOP
      dbms_output.put_line('Working on : '||c1.tablespace_name) ;
      FOR c2 IN (select file_name from dba_data_files
                 where tablespace_name = c1.tablespace_name order by 1)
      LOOP
        dbms_output.put_line('Which contains : '||c2.file_name) ;
      END LOOP ;
   END LOOP ;
END ;
/
    Here (at last!) is something where PL/SQL is better at than SQL.

    To do this in SQL, would have to use SQL-from-SQL to call a second command file, e.g.: 

    set verify off pages 0 head off feed off 
spool go.tmp
select '@2 '||tablespace_name from dba_tablespaces order by 1 ;
spool off
@go.tmp
set pages 9999 head on feed 6
    Where the called file "2.sql" would contain this: 
    prompt Working on : &1'
select 'Which contains : '||file_name from dba_data_files
  where tablespace_name = '&1' order by 1 ;
    That works ok, but can get a bit ugly and unsupportable, especially for complex code. The PL/SQL is easier to read. The PL/SQL also doesn't need to write to or make calls to operating system files, so is more portable, and is platform independent. Especially good for windows servers which don't have a good shell language.

    Spooling to files can have unexpected behaviour when run by different users. For example, if the above script was setup to run from cron as userid oracle, but was run as a one off as userid root, subsequent cron runs would not have permissions to overwrite the now root owned file go.tmp. They would instead run the old go.tmp file as is, potentially generating duplicate data.

    8) Use of Nested Cursor For Loops in a Hot Backup / Clone Script

    Here is an example of the use a DBA could have for this type of PL/SQL.

    To copy datafiles from an open database, the tablespaces must be placed in backup mode. That has a performance impact, so it is preferrable to just have one tablespace in backup mode at a time, copying its files then, and then moving on to the next tablespace. What makes that awkward is that tablespaces can have variable number of datafiles.

    The below PL/SQL generates code to copy away the files working on one tablespace at a time: 

    BEGIN
   FOR c1 IN (select tablespace_name from dba_tablespaces order by 1)
   LOOP
      dbms_output.put_line('alter tablespace '||c1.tablespace_name||' begin backup ;') ;
      FOR c2 IN (select file_name from dba_data_files
                    where tablespace_name = c1.tablespace_name order by 1)
      LOOP
         dbms_output.put_line('host scp -p '||c2.file_name||' devserver:'||c2.file_name) ;
      END LOOP ;
      dbms_output.put_line('alter tablespace '||c1.tablespace_name||' end backup ;') ;
      dbms_output.put_line('alter system switch logfile ;') ;
      dbms_output.put_line('alter database backup controlfile to trace ;') ;
   END LOOP ;
END ;
/
    That gives output like the below: 
    alter tablespace DATA begin backup ;
host scp -p /oracle/data/data_f1.dbf devserver:/oracle/data/data_f1.dbf
host scp -p /oracle/data/data_f2.dbf devserver:/oracle/data/data_f2.dbf
alter tablespace DATA end backup ;
alter tablespace SYSTEM begin backup ;
host scp -p /oracle/data/system.dbf devserver:/oracle/data/system.dbf
alter tablespace SYSTEM end backup ;
alter tablespace TOOLS begin backup ;
host scp -p /oracle/data/tools.dbf devserver:/oracle/data/tools.dbf
host scp -p /oracle/data/tools_f2.dbf devserver:/oracle/data/tools_f2.dbf
host scp -p /oracle/data/tools_f3.dbf devserver:/oracle/data/tools_f3.dbf
alter tablespace TOOLS end backup ;
    Setting pages and lines large before running this (along with setting serverout) would be a good idea for output formatting.

    This script is useful for taking hot backups or making database clones without shutting down the source database. Cloning databases in that way is often faster than exp/imp, especially for medium-large size databases, or databases with LONGs or LOBs. The copied database would need recovered using any redo logs generated during the copy period. A control file would also have to be created from a backup controlfile to trace SQL file. RMAN disk backups are another method of doing this, and they have the benefit of not needing to use backup mode at all.

    9) Execute Immediate

    (Note that this expects a table called "mytable" to have been created. The script for that is below).

    PL/SQL can run select/insert/update/delete: 

    BEGIN
   INSERT INTO mytable (id, col1, col2) values (0,'a','b') ;
   DELETE FROM mytable WHERE col1 = 'x' ;
   UPDATE mytable SET col1 = 'y' WHERE col2 = 'b' ;
END ;
/
    But PL/SQL cannot run DDL (create/alter/drop/grant/etc.) unless called by 'execute immediate'. So this will not work: 
    BEGIN
   GRANT SELECT ON mytable TO public ;
   DROP TABLE mytable ;
END ;
/
    Instead, use this form, which will work ok: 
    BEGIN
   execute immediate 'grant select on mytable to public' ;
   execute immediate 'drop table mytable' ;
END ;
/
    You need double or triple quote marks when passing quote marks into the execute immediate command: 
    BEGIN
  execute immediate 'alter system kill session ''6,304''' ;
END ;
/
    (In SQL, that would be: 
    SQL> alter system kill session '6,304' ;
    )

    Beware that DDL causes an implicit commit of all your sessions uncommitted transactions, and does so even if it is inside an execute immediate.

    10) If..Then Control

    This is where PL/SQL comes has especially more power than SQL.

    There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. IF's can be nested within each other

    IF condition
THEN
   sequence_of_statements
END IF;

IF condition1
THEN
   sequence_of_statements1
ELSIF condition2
THEN
   sequence_of_statements2
ELSE
   sequence_of_statements3
END IF;
    Simple example: 
    BEGIN
   IF to_char(sysdate,'Dy') = 'Sun'
   THEN
      dbms_stats.gather_database_stats ;
   END if ;
END;
/

    11) If..Then Example 

    DECLARE
   qty_on_hand number(5) ;
BEGIN
   select quantity into qty_on_hand from inventory
      where product = 'TENNIS RACKET'
      for update of quantity ;
   IF qty_on_hand > 0    /* check quantity */
   THEN
      UPDATE inventory SET quantity = quantity - 1
         WHERE product = 'TENNIS RACKET' ;
      INSERT INTO purchase_record
         VALUES ('Tennis racket purchased', sysdate) ;
   ELSE
      INSERT INTO purchase_record
         VALUES ('Out of tennis rackets', sysdate) ;
   END IF ;
END ;
/
commit ;
    Demo tables for this can be set up with: 
    create table inventory ( quantity number , product varchar2(20) ) ;
create table purchase_record ( description varchar2(30) , timestamp date ) ;
insert into inventory values ( 1 , 'TENNIS RACKET' ) ;
    How would that be written in SQL? 
    select quantity from inventory where product = 'TENNIS RACKET' and quantity > 0 for update of quantity ;

/* check quantity */
insert into purchase_record
select 'Tennis racket purchased', sysdate
from dual
where exists ( select null
  from inventory
  where product = 'TENNIS RACKET'
  and quantity > 0 ) ;

insert into purchase_record
select 'Out of tennis rackets', sysdate
from dual
where not exists ( select null
  from inventory
  where product = 'TENNIS RACKET'
  and quantity > 0 ) ;

update inventory set quantity = quantity - 1
where product = 'TENNIS RACKET'
and quantity > 0 ;

commit ;
    Works fine, but arguably not as readable.

    12) Store PL/SQL in Procedures Inside the Database

    You can run PL/SQL in the normal way from a SQL command file, using
    SQL> @myfile
    or
    SQL> start myfile

    But PL/SQL can also be separated from operating system files altogether and stored purely in the database. 

    CREATE OR REPLACE PROCEDURE myproc AS
BEGIN
   FOR c1 IN (select tablespace_name from dba_tablespaces order by 1)
   LOOP
      dbms_output.put_line('Working on : '||c1.tablespace_name) ;
      FOR c2 IN (select file_name from dba_data_files
                    where tablespace_name = c1.tablespace_name order by 1) ;
      LOOP
         dbms_output.put_line('Which contains : '||c2.file_name) ;
      END LOOP ;
   END LOOP ;
END ;
/
    Then to execute that code anytime, just: 
    SQL> exec myproc
    Is often done with big complex chunks of code. Is a bit like views for SQL statements.

    13) Required Privileges for Stored Procedures

    If you run the above code as a user other than SYS (e.g. ops$oracle) you will see it fail.

    Yet the exact same good works as a non-SYS user when run directly, outside of a stored procedure.

    That is because oracle puts extra security in place on stored plsql.

    Privileges acquired through roles (even the DBA role) are ignored. Only direct grants are taken into account.

    So the above stored procedure will work if owned by SYS or if owned by a user (e.g. ops$oracle) which has had the following direct grants given it:

  • grant select on dba_tablespaces to ops$oracle ;
  • grant select on dba_data_files to ops$oracle ;

    14) Input/Output Parameter Variables in Procedures: 

    CREATE OR REPLACE PROCEDURE kill_session (sid_to_kill number) AS
  serial number ;
BEGIN
   SELECT serial# INTO serial FROM v$session s WHERE s.sid = sid_to_kill
      AND osuser != 'oracle' ;
   execute immediate 'alter system kill session '''||sid_to_kill||','||serial||'''' ;
END kill_session ;
/
    Note the use of multiple quotation marks in the execute immediate.

    To execute this and kill a session, just run e.g.: 

    SQL> exec kill_session(23)

    Or, if you want, you can specify the parameter name when you execute it: 

    SQL> exec kill_session( sid_to_kill => 23 )

    That second form is useful for procedures with many different parameters, such as those supplied by oracle like dbms_stats.

    Again, the owner of this procedure will either have to be SYS, or a user who has been given direct select grant of v$session, e.g.: 

    SQL> grant select on sys.v$session to ops$oracle ;
    The users who execute this procedure don't need any special grants though, they just need the procedure itself: 
    SQL> grant execute on sys.kill_session to user1 ;
    They don't even need to normally have select on v$session or alter system privilege - having privilege on this procedure is enough.

    15) Group Bunches of Related Procedures Inside a Package 

    CREATE OR REPLACE PACKAGE mypackage AS
   PROCEDURE myproc ;
   PROCEDURE otherproc ;
END mypackage ;
/
    That was the header, this is the body: 
    CREATE OR REPLACE PACKAGE BODY mypackage AS

PROCEDURE myproc AS
BEGIN
   FOR c1 IN (select tablespace_name from dba_tablespaces order by 1)
   LOOP
      dbms_output.put_line('Working on : '||c1.tablespace_name) ;
      FOR c2 IN (select file_name from dba_data_files
                    where tablespace_name = c1.tablespace_name order by 1)
      LOOP
         dbms_output.put_line('Which contains : '||c2.file_name) ;
      END LOOP ;
   END LOOP ;
END myproc ;

PROCEDURE otherproc AS
   dbname varchar2(17) ;
   version varchar2(17) ;
BEGIN
   SELECT instance_name, version INTO dbname, version FROM v$instance ;
   dbms_output.put_line('This is database : '||dbname) ;
   dbms_output.put_line('Version : '||version) ;
END otherproc ;

END mypackage ;
/
    Then to execute that code anytime, just: 
    SQL> exec mypackage.myproc
    or 
    SQL> exec mypackage.otherproc

    16) Management of Packages and Procedures

    You can describe these objects: 

    SQL> desc myproc

SQL> desc mypackage
    And they appear in dba_objects: 
    SQL> select * from user_objects where object_name in ('MYPROC','MYPACKAGE') ;
    And the source code appears in dba_source: 
    SQL> select text from user_source where object_name = 'MYPROC' ;
    And the dependencies appear in dba_dependencies: 
    SQL> select * from user_dependencies where object_name in ('MYPROC','MYPACKAGE') ;

    17) Grants of Procedures and Packages

    You can grant privileges on procedures and packages: 

    SQL> grant execute on myproc to public ;
    Note that the user DOESN'T need the underlying privileges. So for the below, the owner of the procedure needs "alter system" privilege, but anyone else doesn't they just need execute privileges of the procedure alone: 
    CREATE OR REPLACE PROCEDURE kill_session (sid_to_kill number) AS
   serial number ;
BEGIN
   SELECT serial# INTO serial FROM v$session s WHERE s.sid = sid_to_kill
      AND osuser != 'oracle' ;
   execute immediate 'alter system kill session '''||sid_to_kill||','||serial||'''' ;
END kill_session ;
/
    Note the use of multiple quotation marks in the execute immediate.

    Just give the user: 

    SQL> grant execute on kill_session to user1 ;
    and they can kill sessions using that procedure.

    They don't need "alter system" privilege for that - safer.

    Grants are recorded in dba_tab_privs (under table_name - counterintuitive) 

    SQL> select * from user_tab_privs where table_name = 'MYPROC' ;
    Packages and procedures are included in export/import.

    18) Triggers on Tables

    Triggers are chunks of PL/SQL that fire when a particular event happens.

    They are often placed on tables to fire for every insert/update/delete.

    Especially when sequences are in use: 

    CREATE OR REPLACE TRIGGER my_insert_trigger
BEFORE INSERT ON mytable FOR EACH ROW
BEGIN
   SELECT mysequence.nextval INTO :new.id FROM dual;
END;
/
    Note the ":new." prefix in front of the column name.

    The SQL equivalent (which is faster) is to explicitly specify the sequence on insert: 

    SQL> insert into mytable (id,col1) values (mysequence.nextval,'x') ;

    Table triggers are also used for tracking last modified date and/or user: 

    CREATE OR REPLACE TRIGGER my_update_insert_trigger
BEFORE UPDATE OR INSERT ON mytable FOR EACH ROW
BEGIN
   :new.modified_date := sysdate ;
   :new.modified_by := user ;
END;
/

    It is possible to code procedure calls into triggers: 

    CREATE OR REPLACE TRIGGER my_delete_trigger
AFTER DELETE ON mytable FOR EACH ROW
BEGIN
  myproc ;
END;
/
    Procedures in triggers are better for performance, because procedures are compiled, while triggers are interpreted. That performance difference will be more significant for more complex PL/SQL code.

    Beware that triggers will fire during imports into existing tables (that is, with "ignore=y") and during other big data loads. That will seriously slow down those processes, with a context switch from SQL into PL/SQL happening for every row. Triggers work well for single row and small volume updates/inserts/deletes, such as from data input from an end user through a GUI application. They can lead to performance issues for large scale batch processing. But they can be toggled on and off with the "alter trigger enable/disable" command.

    19) Triggers on Database Events 

    create or replace trigger sys.set_trace
after logon on database
when (user = 'MYUSER')
BEGIN
   execute immediate 'alter session set timed_statistics=true'; 
   execute immediate 'alter session set max_dump_file_size=unlimited';
   execute immediate 'alter session set events ''10046 trace name context forever, level 8''';
END set_trace;
/

    20) Triggers Raising Application Errors

    This trigger is acting as a check constraint - for logic too complex (spans two columns) for a check constraint to handle.

    The raise application error call means that the user will see a meaningful error message explaining what has gone wrong with their update. 

    create or replace trigger mytable_decommission
/*
|| Author : Andrew Fraser
|| Date : 25th February 2009
|| Name : mytable_decommision
|| Purpose : Ensures that status DECOMMISSIONED and decomm date go together
*/
AFTER INSERT OR UPDATE ON mytable FOR EACH ROW
BEGIN
   /*
   || if status is decommissioned, make sure a decomm date has been entered
   */
   IF ( :new.db_status = 'DECOMMISSIONED' AND :new.decomm is null )
   THEN
      raise_application_error( -20003 , 'If status is decommissioned, make sure a decomm date has been entered.') ;
   END IF ;
   /*
   || and vice versa: if a decomm date has been entered, make sure status is decommissioned
   */
   IF ( :new.decomm IS NOT NULL AND :new.db_status != 'DECOMMISSIONED' )
   THEN
      raise_application_error( -20003 , 'If a decomm date has been entered, make sure status is decommissioned.') ;
   END IF ;
END ;
/
    Note the comments, always good practice, especially declaring author, date, purpose at start.

    The simpler check constraints work like this: 

    alter table mytable add constraint valid_status check ( db_status in (
  'ASM','CLIENT INSTALL','DECOMMISSIONED','DEVELOPMENT','LIVE','LIVE SOX',
  'NOT SUPPORTED','OMS','STANDBY','STANDBY SOX') )
/

    21) Management of Triggers

    Triggers can be compiled same as procedures and packages: 

    SQL> alter trigger my_insert_trigger compile ;
SQL> sho err
    Triggers can also be disabled - meaning that they will not fire 
    SQL> alter trigger my_insert_trigger disable ;
    And enabled - switched back on again 
    SQL> alter trigger my_insert_trigger enable ;
    Triggers are seen in dba_objects and their source code is in dba_source, just like procedures and pacakges.

    But triggers and their source code are also recorded in dba_triggers. The source code in that view is stored as a long, so set long to a large value before selecting it, or the output will be truncated to miss most of the source code. 

    SQL> set long 9999
SQL> select * from dba_triggers where trigger_name = 'MY_INSERT_TRIGGER' ;

    22) Dba_Source Shell Script

    dba_source lines can be long, and selecting from sql generates trailing whitespace.

    So it is often neater to generate out from a shell script like this: 

    sqlplus -s / <<e1 > myproc.sql
set pages 0 head off feed off lines 390
select text from dba_source where name = 'MYPROC' ;
e1
vi myproc.sql

    Some software vendors however encrypt their PL/SQL code using the wrap utility. If that is done, you will just see gibberish characters and numbers in dba_source.

    Alternatively, Oracle SQL Developer (a free download from here) displays source code in its GUI editor interface.

    23) Compiling Procedures and Packages

    The status field in dba_objects is valid for compiled procedures, invalid for the others. Procedures become invalid when underlying objects are changed. Oracle will attempt to recompile them automatically when they are next executed, but you can pre-empt that with: 

    SQL> alter procedure myproc compile ;
SQL> sho errors
    "Sho errors" lists the reasons for code not compiling.

    Packages are the same, except they have both a header and a body, which are compiled separately (and so can have a different status): 

    SQL> alter package mypackage compile ;
SQL> alter package mypackage compile body ;
SQL> sho errors
    When compiling objects in different schemas, "sho errors" does not work, so look in the dba_errors view instead: 
    SQL> select * from dba_errors;
    Finally, supplied script utlrcmp will recompile everything in a database: 
    SQL> @?/rdbms/admin/utlrcmp

    24) Supplied Packages

    Oracle comes with a lot of in built packages. Some of the more commonly used are:

  • dbms_stats.gather_database_stats = analyzes database
  • dbms_stats.gather_table_stats = analyzes table
  • statspack.snap = takes a statspack snapshot
  • dbms_lock.sleep = pause, just like the unix shell sleep command.
  • dbms_output.put_line = output text to screen, just like prompt in SQL*Plus.
  • utl_file = reads from and writes to files on the operating system. Can also copy files. Utl_file is not the easiest thing in the world to use, the SQL*Plus spool comamnd or external tables are often easier and better.
  • dbms_scheduler (before 10g, dbms_job) = schedules PL/SQL jobs, is oracle's equivalent of cron in unix.

    These can be called from within PL/SQL, e.g.: 

    BEGIN
   dbms_output.put_line('Starting') ;
   statspack.snap ;
   dbms_lock.sleep(60) ;
   dbms_stats.gather_schema_stats(user) ;
END;
/

    They are documented in Oracle's PL/SQL Packages and Types Reference. Links direct to that are:

  • 11gR2 PL/SQL Packages and Types Reference
  • 11gR1 PL/SQL Packages and Types Reference
  • 10gR2 PL/SQL Packages and Types Reference

    25) Some Real World Examples

    Accommodation Apply Online cron: 

    [srslive@uobcb201 srslive]$ crontab -l | grep accom_online_php.com
30 5 * * * /applive/srslive/accphp/exec/accom_online_php.com > /applive/srslive/cronlogs/accom_online_php.cronlog 2>&1
[srslive@uobcb201 srslive]$ cat /applive/srslive/accphp/exec/accom_online_php.com
####################################
# Author : Andrew Fraser (Operational DBA) , Karen Grant (Developer)
# Date : 11 January 2011
# Purpose : Populates accom_php_student lookup table across database link onto PHP online accommodation database
#   Can be re-run repeatedly throughout the day without any adverse impact or duplicate issues.
#   Database link requires: "grant select, insert, update, delete on accom_php_student to link_from_accom;" on remote database to work.
# Change History :
#   09-May-2011 Andrew Fraser explicitly specified "accphp.accom_php_student" because synonym was found to be pointing at the wrong accom_php_student
#     so explicit owner.table[@db] used now throughout this script for safety.
####################################
. /applive/srslive/cron_env.com
sqlplus -s / as sysdba <<END_SQL
set serverout on

DECLARE
  v_numrows number ;
BEGIN
  -- populate local copy of accom_php_student
  accphp.pop_online_verification_table.main_process ;
  -- copy this entire table over database link ONLY if it looks to be populated with a reasonable amount of data
  SELECT COUNT(*) INTO v_numrows FROM accphp.accom_php_student ;
  IF v_numrows > 5000
  THEN
    dbms_output.put_line('Starting copy to php database.') ;
    DELETE accom.accom_php_student@accomphp ;
    INSERT INTO accom.accom_php_student@accomphp SELECT * FROM accphp.accom_php_student ;
  ELSE
    dbms_output.put_line('Error - copy to php accommodation database failed.') ;
  END IF;
END ;
/
END_SQL
# End of file.
[srslive@uobcb201 srslive]$

    Postgraduate Admissions Table Trigger: 

    CREATE OR REPLACE TRIGGER adm_ola_app_transfer_trig
/*
|| Name : adm_ola_app_transfer_trig
|| Author : Andrew Fraser
|| Date : 17-May-2012
|| Purpose : Copy data to core admissions whenever php inserts into driving table.
*/
BEFORE INSERT ON adm_ola_app_summary
FOR EACH ROW WHEN ( new.process_status = 'S' )
BEGIN
   adm_ola_app_transfer(p_personal_id => :new.personal_id, p_application_id => :new.application_id) ;
/*
|| Trap ALL errors here - so we always allow the main insert to work to completion even if this trigger is a total failure.
|| Failed rows (status 'F') get picked up by a cron job and have a second attempt at being processed.
|| If they fail that second attempt then an error is logged and manual intervention is needed to correct.
*/
EXCEPTION
   WHEN OTHERS
   THEN
      :new.process_status := 'F' ;
END ;
/

    Postgraduate Admissions Cron: 

    BEGIN
  FOR d1 in SELECT personal_id, application_id FROM adm_ola_app_summary WHERE process_status = 'S'
  LOOP
      adm_ola_app_transfer(p_personal_id => d1.personal_id, p_application_id => d1.application_id) ;
   END LOOP ;
END ;
/

    Pl/SQL wrapper around “alter user” to allow front end interfaces (Servicedesk, Javascript or PHP web pages, etc.) to safely change passwords. 

    CREATE OR REPLACE PROCEDURE sys.php_reset_passwords
/***************************
|| Name : sys.php_reset_passwords
|| Author : Andrew Fraser
|| Date : 05-Apr-2012
|| Purpose : Allow users to reset their database passwords from a php web page
|| Parameters :
||     p_username - the AD username of the user - case INsensitive
||     p_new_password - the new password that the user has typed into web page - case SENSITIVE
||         Validation rules on that password are maintained in php code (mininmum length, different from username, etc.)
||         so watch out for strict dba_profile rules preventing password changes
||     p_result - returns to php whether the command succeeded or not.
|| Installation notes:
||     This procedure should be owned by sys and the php user account then needs execute privilege on this procedure only.
||     Users other than sys could own this procedure if they are given explicit select on dba_role_privs and dba_users.
|| Change History :
***************************/
   ( p_username IN VARCHAR2
   , p_new_password IN VARCHAR2
   , p_result OUT VARCHAR2 )
AS
   v_forbidden NUMBER := 0 ;
   v_num_users NUMBER := 0 ;
BEGIN
   /*
   || Prevent any attempts to change superuser passwords
   */
   SELECT COUNT(*) INTO v_forbidden FROM sys.dba_role_privs WHERE grantee = UPPER(p_username) AND granted_role IN ('DBA','IMP_FULL_DATABASE') ;
   IF v_forbidden > 0
   THEN
      p_result := 'superuser account cannot be changed with this utility' ;
   ELSE
      SELECT COUNT(*) INTO v_num_users FROM ALL_USERS WHERE username = UPPER(p_username) ;
      IF v_num_users = 1
      THEN
         EXECUTE IMMEDIATE 'alter user ' || p_username || ' identified by "' || p_new_password ||'" account unlock' ;
         p_result := 'success' ;
      ELSE
         p_result := 'no database account matches that username' ;
      END IF ;
   END IF ;
END ;
/

    26) Scripts to Build Demo Tables 

    create table mytable (
    id number not null primary key
  , col1 varchar2(20)
  , col2 varchar2(20)
  , db_status varchar2(30)
  , decomm date
  , modified_date date
  , modified_by varchar2(30) ) ;
create sequence mysequence ;
create table inventory ( quantity number , product varchar2(20) ) ;
create table purchase_record ( description varchar2(30) , timestamp date ) ;
insert into inventory values ( 1 , 'TENNIS RACKET' ) ;
    And to clean up afterwards: 
    drop table mytable ;
drop sequence mysequence ;
drop procedure myproc ;
drop procedure sys.myproc ;
drop procedure kill_session ;
drop procedure sys.kill_session ;
drop package mypackage ;
drop package sys.mypackage ;
drop table inventory ;
drop table purchase_record ;
    Andrew Fraser, May 2012