Collections-Varrays

A VARRAY is similar to a nested table except an upper bound must be specified in the declaration.  Like nested tables, they can be stored in the database. But unlike nested tables, individual elements cannot be deleted so they remain dense.  The following code shows how
the previous example can be rewritten to use a VARRAY.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE t_collection IS VARRAY(5) OF NUMBER(10);

  l_coll  t_collection;
  l_idx   NUMBER;
BEGIN
  -- Initialise the collection with two values.
  l_coll := t_collection(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    l_coll.extend;
    l_coll(l_coll.last) := i;
  END LOOP load_loop; 

  -- Can't delete from a VARRAY.
  -- l_coll.DELETE(3);

  -- Traverse collection
  l_idx := l_coll.FIRST;
  << display_loop >>
  WHILE l_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || l_coll(l_idx));
    l_idx := l_coll.NEXT(l_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 3
The number 4
The number 5

PL/SQL procedure successfully completed.

Extending the load_loop to "3 .. 6" attempts to extend the VARRAY beyond its limit of 5 elements resulting in the following error:

DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 13

Collection Methods

A variety of methods exist for collections, but not all are relevant for every collection type:

• EXISTS(n) - Returns TRUE if the specified element exists.
• COUNT - Returns the number of elements in the collection.
• LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
• FIRST - Returns the index of the first element in the collection.
• LAST - Returns the index of the last element in the collection.
• PRIOR(n) - Returns the index of the element prior to the specified element.
• NEXT(n) - Returns the index of the next element after the specified element.
• EXTEND - Appends a single NULL element to the collection.
• EXTEND(n) - Appends n NULL elements to the collection.
• EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
• TRIM - Removes a single element from the end of the collection.
• TRIM(n) - Removes n elements from the end of the collection.
• DELETE - Removes all elements from the collection.
• DELETE(n) - Removes element n from the collection.
• DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

Triggers and PL/SQL

Database triggers are stored programs associated with specific table, view or system events, such that when the specific event occurs, the associated code is executed.  Triggers can be used to validate data entry, log specific events, perform maintenance tasks or perform additional application logic.  The following example shows how a table trigger could be used to keep an audit of update actions.

-- Create and populate an items table and create an audit log table.

CREATE TABLE items (
  id           NUMBER(10),
  description  VARCHAR2(50),
  price        NUMBER(10,2),
  CONSTRAINT items_pk PRIMARY KEY (id)
);

CREATE SEQUENCE items_seq;

INSERT INTO items (id, description, price) VALUES (items_seq.NEXTVAL, 'PC', 399.99);

CREATE TABLE items_audit_log (
  id           NUMBER(10),
  item_id      NUMBER(10),
  description  VARCHAR2(50),
  old_price    NUMBER(10,2),
  new_price    NUMBER(10,2),
  log_date     DATE,
  CONSTRAINT items_audit_log_pk PRIMARY KEY (id)
);

CREATE SEQUENCE items_audit_log_seq;

-- Create a trigger to log price changes of items.
CREATE OR REPLACE TRIGGER items_aru_trg
  AFTER UPDATE OF price ON items
  FOR EACH ROW
BEGIN
   INSERT INTO items_audit_log (id, item_id, description, old_price, new_price, log_date)
   VALUES (items_audit_log_seq.NEXTVAL, :new.id, :new.description, :old.price, :new.price,
SYSDATE);
END;
/

-- Check the current data in the audit table, should be no rows.
COLUMN description FORMAT A10
SELECT * FROM items_audit_log;

no rows selected

-- Update the price of an item.
UPDATE items
SET    price = 499.99
WHERE  id    = 1;

-- Check the audit table again.
COLUMN description FORMAT A10
SELECT * FROM items_audit_log;

        ID    ITEM_ID DESCRIPTIO  OLD_PRICE  NEW_PRICE LOG_DATE
---------- ---------- ---------- ---------- ---------- -------------
         1          1 PC             399.99     499.99 19-AUG-2005 10:14:11

1 row selected.

-- Clean up.
DROP TABLE items_audit_log;
DROP TABLE items;

This example shows that the trigger fired when the price of the record was updated; thus allowing an audit of the action.

The following trigger sets the current_schema parameter for each session logging on as the APP_LOGON user, making the default schema that of the SCHEMA_OWNER user.

CREATE OR REPLACE TRIGGER APP_LOGON.after_logon_trg AFTER
LOGON ON APP_LOGON.SCHEMA BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';
END;