What are the benefits of packages over standalone procedures and functions

I use AQ for

• transactions between databases
• implementing business rules that would otherwise need to be implemented using triggers ( ie actions must be taken on the same table that initiates the action)

By using a queue you can do these things:

• a transaction can take place in the order you want it to, just not instantaneously. eg insert in one database, copy same record to another database
• the second transaction is now independent of the first but you still have consistency. eg only if the first insert succeeds does the second AQ transaction take place and if the second transaction takes place on another database it only happens if the second database is ready to receive

Here is more information on how I use AQ between databases. I am not an expert and got most of the code from the Internet. Oracle documentation is lengthy but did not really help me.

First create the queue:

BEGIN
  SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
  (
    QUEUE_TABLE           =>        'QT_NEW_CASE'
   ,QUEUE_PAYLOAD_TYPE    =>        'FILE_ACTION'
   ,COMPATIBLE            =>        '8.1'
   ,STORAGE_CLAUSE        =>        '
                                     TABLESPACE USERS
                                     PCTUSED    0
                                     PCTFREE    10
                                     INITRANS   1
                                     MAXTRANS   255
                                     STORAGE    (
                                                 INITIAL          64K
                                                 NEXT             1M
                                                 MINEXTENTS       1
                                                 MAXEXTENTS       UNLIMITED
                                                 PCTINCREASE      0
                                                 BUFFER_POOL      DEFAULT
                                                )'
   ,SORT_LIST             =>        'ENQ_TIME'
   ,MULTIPLE_CONSUMERS    =>         FALSE
   ,MESSAGE_GROUPING      =>         0
   ,SECURE                =>         FALSE
   );
End;

On the origin database:

CREATE OR REPLACE TYPE FILE_ACTION                                                                                                                                                       AS OBJECT
(    ACTION VARCHAR2(20),
     CASE_ID NUMBER(10),
     OTHER VARCHAR2(20)
);

The Action is admittedly crude but versatile. The requirements were to funnel changes from multiple tables to another database where further processing was required without touching the application code. A typical call is to a package from a trigger, other procedure or job.

queue_util.add_file ('CLOSE', v_case_id,:NEW.ID);

Inside the package

   PROCEDURE add_file_to_queue (action_in      IN VARCHAR2,
                                d_case_id_in   IN NUMBER,
                                d_other_in     IN VARCHAR2:= NULL)
   IS
      /******************************************************************************
      PURPOSE: when there is a change to a file (create, closed or reopen)  add the change to the queue of changes 
     ******************************************************************************/
      queue_options        SYS.DBMS_AQ.enqueue_options_t;
      message_properties   SYS.DBMS_AQ.message_properties_t;
      message_id           RAW (16);
      my_message           file_action;
      err_text             VARCHAR2 (2000);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      my_message := file_action (action_in, d_case_id_in, d_other_in);
      DBMS_AQ.enqueue (queue_name           => 'NEW_CASE_QUEUE',
                       enqueue_options      => queue_options,
                       message_properties   => message_properties,
                       payload              => my_message,
                       msgid                => message_id);

      IF g_debugging
      THEN
        ;
      --insert debugging info if g_debugging is true
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         err_text := SQLERRM;
         --logging error to another table

      WHEN OTHERS
      THEN
         err_text := SQLERRM;
         --more logging
   END add_file_to_queue;

--and popping messages off the queue

   PROCEDURE send_from queue (case_id_in IN NUMBER := NULL)
   IS
      /******************************************************************************
      PURPOSE:get the list of file changes and send them out
      *****************************************************************************/
      queue_options                DBMS_AQ.dequeue_options_t;
      message_properties           DBMS_AQ.message_properties_t;
      message_id                   file_action;
      v_file                       VARCHAR2 (20);
      v_case_id                    NUMBER (10);
      v_filename                   VARCHAR2 (500);
      v_action                     VARCHAR2 (20);
      v_other                      VARCHAR2 (20);
      v_err_id                     INTEGER;
      bad_data_ex EXCEPTION;
      v_err_text                   VARCHAR2 (50);

      TYPE cases_cur IS REF CURSOR;

      new_cases                    cases_cur;
   BEGIN
      IF case_id_in IS NULL
      THEN
         OPEN new_cases FOR
              SELECT   qt.msgid
                FROM   cqt_new_case qt
            ORDER BY   qt.enq_time;
      ELSE
         OPEN new_cases FOR
              SELECT   qt.msgid
                FROM   qt_new_case qt
               WHERE   qt.user_data.case_id = case_id_in
            ORDER BY   qt.enq_time;
      END IF;

      --should have added a check here to make sure
      --the other database is up and running
      LOOP
         BEGIN
            FETCH new_cases INTO   message_id;

            --reinitialize values to null
            v_case_id := NULL;
            v_filename := NULL;
            v_file := NULL;
            v_action := NULL;
            v_other := NULL;
            --to try and clear all locks
            COMMIT;
            EXIT WHEN new_cases%NOTFOUND;

            IF case_id_in IS NOT NULL
            THEN
               queue_options.deq_condition :=
                  'tab.user_data.case_id = ' || case_id_in;
            END IF;

            DBMS_AQ.dequeue (queue_name           => 'NEW_CASE_QUEUE',
                             dequeue_options      => queue_options,
                             message_properties   => message_properties,
                             payload              => my_message,
                             msgid                => message_id);
            v_case_id := my_message.case_id;
            v_action := my_message.action;
            v_other := my_message.other;

            IF v_case_id IS NOT NULL AND v_case_id > 0
            THEN
               IF g_debugging
               THEN
                 ;
               --insert your debugging information
               END IF;
            --continues on with lengthy data transforms
            --for actions like NEW, CLOSE, REOPEN
END SEND_FROM_QUEUE;

I would certainly write this differently today but it works.... The key problem with AQ for me is that I have never gotten queue to queue messaging going between different databases. This was described as a primary value for it. Yet I still like having the certainty that the initial transaction can complete without regard for the state of the destination database. If the message fails to be inserted when into the destination database an error is logged and an email is sent notifying the developer.

_{Community wiki answer initially based on comments left by thatjeffsmith:}

This is an exhaustive list of what it supports:

(reproduced from http://pldoc.sourceforge.net/maven-site/samples/sample1.sql)

CREATE OR REPLACE
PACKAGE CUSTOMER_DATA
IS
/** 
* Project:         Test Project (<a href="http://pldoc.sourceforge.net">PLDoc</a>)<br/>
* Description:     Customer Data Management<br/>
* DB impact:       YES<br/>
* Commit inside:   NO<br/>
* Rollback inside: NO<br/>
* @headcom
*/

/**
* Record of customer data.
*
* @param id     customer ID
* @param name       customer name
* @param regno      registration number or SSN
* @param language   preferred language
*/
TYPE customer_type IS RECORD (
  id                        VARCHAR2(20),
  name                      VARCHAR2(100),
  regno                     VARCHAR2(50),
  language                  VARCHAR2(10)
);

/** Table of customer records. */
TYPE customer_table IS TABLE OF customer_type INDEX BY BINARY_INTEGER;

/**
* Gets customer by ID.
*
* @param p_id       customer ID
* @param r          record of customer data
* @throws no_data_found if no such customer exists
*/
PROCEDURE get_customer (
  p_id              VARCHAR2,
  customer_rec      OUT customer_type);

/**
* Searches customer by criteria.
*
* @param p_criteria record with assigned search criteria
* @param r_records  table of found customers <b>(may be empty!)</b>
*/
PROCEDURE get_by_criteria (
  p_criteria        customer_type,
  r_records         OUT customer_table);

/**
* Creates a customer record.
*
* @param customer_rec record of customer data
*/
PROCEDURE create_customer (
  customer_rec      customer_type);

/**
* Changes customer data.
*
* @param customer_rec record of updated customer data
*/
PROCEDURE update_customer (
  customer_rec      customer_type);

END;
/

We support everything in PLDOC - we just have a GUI vs a CLI for it. There are three code samples there, you should be able to do anything listed in those samples.