Rem  
Rem $Header: dbmsepgc.sql 13-jul-00.15:03:17 rdecker Exp $
Rem
Rem dbmspgc.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      dbmsepgc.sql - DBMS Embedded PL/SQL Gateway Configuration package
Rem
Rem    DESCRIPTION
Rem
Rem      The Embedded PL/SQL Gateway runs as a plug-in in the Oracle Servlet
Rem      Engine embedded in the Oracle database. There can be multiple
Rem      instances of the Oracle Servlet Engine, each listening HTTP on a
Rem      unique port. With each such port (i.e. instance of the Oracle Servlet
Rem      Engine) one can associate an instance of the Embedded PL/SQL
Rem      Gateway.
Rem
Rem      Each instance of the Embedded PL/SQL Gateway is independently
Rem      configurable.  You can use this package to set/get configuration
Rem      information for an instance of the Embedded PL/SQL Gateway. An
Rem      instance of the Embedded PL/SQL Gateway is identified by a port
Rem      number.
Rem
Rem      For HTTP requests to the PL/SQL gateway, the port number of the
Rem      HTTP request determines which instance of the Embedded PL/SQL
Rem      Gateway (with its associated configuration) is used.
Rem
Rem  
Rem    NOTES
Rem     1. This package must be created under sys.
Rem     2. Transaction Context:
Rem        This package runs in the transaction context of the caller.
Rem  
Rem        All operations run in the caller's transactional context. Hence,
Rem        caller must explicitly commit after calling operations any
Rem        update operations such as "import", "set_*", "drop_*", etc.
Rem
Rem        If the caller wants to execute configuration operations in a
Rem        separate transaction context, they can wrapper the calls to this
Rem        package in an autonomous PL/SQL block.  (See section on
Rem        Autonomous Transactions, PL/SQL User's Guide, Oracle8i).
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rdecker     07/13/00 - UPDATE comments
Rem    kmuthukkk   05/11/00 - create/drop_instance/drop_all_instances
Rem    rdecker     05/09/00 - remove ampersand cuz it confuses sqlplus
Rem    kmuthukk    04/05/00 - more changes
Rem    kmuthukk    07/19/99 - support for reinitializing configuration
Rem    kmuthukk    07/19/99 - add more comments
Rem    kmuthukk    07/13/99 - PL/SQL Gateway Config Package Spec
Rem    kmuthukk    07/13/99 - Created
Rem

CREATE OR REPLACE PACKAGE dbms_epgc AUTHID CURRENT_USER IS

   ----------------
   ---- TYPES -----
   ----------------
   type VARCHAR2_TABLE is table of varchar2(4000) INDEX BY BINARY_INTEGER;

   ----------------
   -- EXCEPTIONS --
   ----------------
   config_error EXCEPTION;
   PRAGMA EXCEPTION_INIT(config_error, -20000);
   config_error_num CONSTANT PLS_INTEGER := -20000;
   -- *Mesg: "%s failed"
   -- *Cause: Some gateway configuration operations fails.
   -- *Action: Look at the previous errors on the stack.

   user_already_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(user_already_exists, -20001);
   user_already_exists_num CONSTANT PLS_INTEGER := -20001;
   -- *Mesg: "%s is already an admin user"
   -- *Cause:
   -- *Action:

   invalid_port EXCEPTION;
   PRAGMA EXCEPTION_INIT(invalid_port, -20002);
   invalid_port_num PLS_INTEGER := -20002;
   -- *Mesg: "invalid port number specified"
   -- *Cause: The argument is expecting a non-null, valid value but the
   --         argument value passed in is null, invalid, or out of range.
   -- *Action: Check your program and correct the caller of the routine
   --          to not pass a null, invalid or out-of-range argument value.
   --          Make sure that a gateway instance has been created for
   --          specified port (via a dbms_epgc.create_instance call).

   invalid_username EXCEPTION;
   PRAGMA EXCEPTION_INIT(invalid_username, -20003);
   invalid_username_num PLS_INTEGER := -20003;
   -- *Mesg: "invalid username specified"
   -- *Cause: specified username is NULL or too long.
   -- *Action:


   not_an_admin EXCEPTION;
   PRAGMA EXCEPTION_INIT(not_an_admin, -20004);
   not_an_admin_num PLS_INTEGER := -20004;
   -- *Mesg: "%s is not an admin user"
   -- *Cause:
   -- *Action:

   privilege_error EXCEPTION;
   PRAGMA EXCEPTION_INIT(privilege_error, -20005);
   privilege_error_num PLS_INTEGER := -20005;
   -- *Mesg: "%s does not have admin privileges on gateway instance %s"
   -- *Cause: Specified user does not have administrative privileges
   --         for configuring the PL/SQL gateway instance running on
   --         specified port.
   -- *Action:

   dad_not_found EXCEPTION;
   PRAGMA EXCEPTION_INIT(dad_not_found, -20006);
   dad_not_found_num PLS_INTEGER := -20006;
   -- *Mesg: "database access descriptor (DAD) %s not found"
   -- *Cause: Specified Database Access Descriptor (DAD) does not
   --         exist.
   -- *Action:

   invalid_dad_attribute EXCEPTION;
   PRAGMA EXCEPTION_INIT(invalid_dad_attribute, -20007);
   invalid_dad_attribute_num PLS_INTEGER := -20007;
   -- *Mesg: "%s is not a valid DAD attribute".
   -- *Cause: Specified attribute is not a valid DAD attribute.
   -- *Action: 

   invalid_global_attribute EXCEPTION;
   PRAGMA EXCEPTION_INIT(invalid_global_attribute, -20008);
   invalid_global_attribute_num PLS_INTEGER := -20008;
   -- *Mesg: "%s is not a valid DAD attribute".
   -- *Cause: Specified attribute is not a valid DAD attribute.
   -- *Action:

   instance_already_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(instance_already_exists, -20009);
   instance_already_exists_num PLS_INTEGER := -20009;
   -- *Mesg: "a gateway instance at port %s already exists"
   -- *Cause: There is already a gateway instance on specified
   --         port number.
   -- *Action: You must either drop the specified gateway instance,
   --          or use a different port number.

   ------------------------------------
   ---- INSTANCE LEVEL OPERATIONS -----
   ------------------------------------

   --
   -- Creates/reserves a gateway instance identified
   -- by <port> for further use. This call must be done
   -- prior to do further configuration of the instance
   -- (such as adding global attributes, dad attributes,
   -- or granting admin privileges).
   --
   -- If the instance (port) is already in use, then
   -- this operation will result in an error.
   --
   -- However, before using the "import" APIs (i.e. the
   -- bulk configuration upload APIs) describe later,
   -- the "create_instance" step need not be done.
   --
   -- The calling user of this routine automatically gets
   -- admin privileges on this gateway instance.
   --
   PROCEDURE create_instance(port IN PLS_INTEGER);

   --
   -- Drops the configuration information for the gateway
   -- instance identified by <port>.
   --
   -- This API can be used, for instance, to delete any prior (old)
   -- configuration that might be present for the gateway instance
   -- identified by "port" before starting to configuring the instance
   -- afresh (using create_instance).
   --
   PROCEDURE drop_instance(port IN PLS_INTEGER);

   --
   -- Drops the configuration information for ALL gateway
   -- instances in the database.
   --
   -- This API can be used, for instance, to delete any prior (old)
   -- configuration that might be present for any gateway instances
   -- in the database.
   --
   -- The caller of this routine should be either SYS or must have
   -- admin privileges on all gateway instances in the database.
   -- 
   PROCEDURE drop_all_instances;


   -----------------------
   ----- ADMIN APIs ------
   -----------------------   
   --
   -- The following APIs are for granting/revoking Gateway administration
   -- privileges to database users.
   --
   -- Note: The SYS and SYSTEM are always "admin" users by default.
   
   --
   -- grants admin privileges to a user.
   --
   procedure grant_admin(port IN pls_integer, username IN varchar2);

   -- revokes admin privileges of a user
   procedure revoke_admin(port IN pls_integer, username IN varchar2);

   --
   -- gets list of admin users (other than SYS/SYSTEM)
   -- 
   -- Note: if no admin users exist then <users> will be set to
   -- an empty table (i.e. the number of elements will be 0).
   --
   procedure get_admin_list(port  IN          pls_integer,
                            users OUT NOCOPY  VARCHAR2_TABLE);

   -----------------------------------------
   ------ CONFIGURING GLOBAL DEFAULTS ------
   -----------------------------------------
   --
   -- This interface must be used to set global default attributes
   -- for the Embedded PL/SQL Gateway.
   --
   -- If <attrname> attribute has been set before for a given
   -- gateway (i.e. port number), then the old value
   -- will be overwritten with the new <attrvalue> argument.
   --
   --
   -- The names of the attributes are not case sensitive. The values
   -- may or may not be case-senstive depending on the attribute.
   --
   -- Examples:
   --   set_global_attribute(8080, 'defaultDAD', 'myApp');
   --   set_global_attribute(8080, 'upload_as_blob', 'jpeg, *');
   --
   procedure set_global_attribute(port      IN pls_integer,
                                  attrname  IN varchar2,
                                  attrvalue IN varchar2);

   --
   -- Gets the value of a global attribute.
   --  Returns NULL if attribute has not been set.
   --  Raises exception if the attribute is not a valid attribute.
   --
   function get_global_attribute(port      IN pls_integer,
                                 attrname  IN varchar2)
     return VARCHAR2;

   --
   -- Deletes a global attribute.
   --
   procedure delete_global_attribute(port      IN pls_integer,
                                     attrname  IN varchar2);

   --
   -- Get all global attributes/values for an Embedded Gateway
   -- instance.
   --
   -- The output is 2 correlated index-by tables of the name,
   -- value pairs.
   --
   --
   -- Note: if the gateway instance has no global attributes set, then
   -- attrnamearray and attrvaluearray will be set to empty arrays.
   --
   procedure get_all_global_attributes(port           IN  pls_integer,
                                       attrnamearray  OUT NOCOPY VARCHAR2_TABLE,
                                       attrvaluearray OUT NOCOPY VARCHAR2_TABLE);

   ----------------------------------------------------------
   ----- CONFIGURING DATABASE ACCESS DESCRIPTORS (DADs) -----
   ----------------------------------------------------------
   --
   -- Creates a new DAD. None of its attributes will be set.
   --
   -- DAD name is not case-sensitive. If a DAD with this name
   -- already exists, then the old DAD's information will be
   -- deleted.
   --
   procedure create_dad(port IN pls_integer, dadname IN varchar2);

   --
   -- drops a DAD from the gateway configuration.
   --
   procedure drop_dad(port IN pls_integer, dadname IN varchar2);

   --
   -- This interface must be used to set attributes for a
   -- DAD (Database Access Descriptor). If the DAD whose
   -- attribute is being set doesn't already exist then
   -- it will be automatically created.
   --
   -- If <attrname> attribute has been set before for the given
   -- dad/port number combination, then the old value
   -- will be overwritten with the new <attrvalue> argument.
   --
   -- DAD names and DAD attribute names are not case
   -- sensitive. DAD attribute names might be case-sensitive
   -- depending upon the attribute.
   --
   --
   -- Example:
   --
   --  set_dad_attribute(8080, 'myApp', 'default_page', 'myApp.home');
   --  set_dad_attribute(8080, 'myApp', 'document_path', 'docs');
   --
   procedure set_dad_attribute(port      IN pls_integer,
                               dadname   IN varchar2,
                               attrname  IN varchar2,
                               attrvalue IN varchar2);

   --
   -- Gets the value of a DAD attribute.
   --  Raises an error if DAD does not exist.
   --  Raises an error if attribute is not a valid attribute.
   --  Returns NULL if attribute has not been set.
   -- 
   function get_dad_attribute(port      IN pls_integer,
                              dadname   IN varchar2,
                              attrname  IN varchar2) return VARCHAR2;

   --
   -- Deletes a DAD attribute.
   --
   procedure delete_dad_attribute(port      IN pls_integer,
                                  dadname   IN varchar2,
                                  attrname  IN varchar2);

   --
   -- Get list of all DADs for an Embedded Gateway instance.
   -- 
   -- Note: if no DADs exist then <dadarray> will be set to
   -- an empty table (i.e. the number of elements will be 0).
   --
   procedure get_dad_list(port IN pls_integer,
                          dadarray OUT NOCOPY VARCHAR2_TABLE);

   --
   -- Get all attributes of a DAD. The output is 2 correlated
   -- index-by tables of the name, value pairs.
   --
   -- Note: if the DAD has no attributes set, then attrnamearray
   -- and attrvaluearray will be set to empty arrays.
   --
   procedure get_all_dad_attributes(port           IN  pls_integer,
                                    dadname        IN  varchar2,
                                    attrnamearray  OUT NOCOPY VARCHAR2_TABLE,
                                    attrvaluearray OUT NOCOPY VARCHAR2_TABLE);

   

   -------------------------------------
   ---- BULK IMPORT/EXPORT OPERATIONS --
   -------------------------------------


   -- The following APIs can be used for bulk loading the
   -- configuration information for an Embedded PL/SQL Gateway.
   -- The configuration can be supplied in one of the following
   -- forms:
   --    - a varchar2
   --    - a index-by table of varchar2s
   --    - a CLOB
   --
   -- Note:The syntax of the configuration information must be
   -- the same as that used by the mod_plsql gateway running under 
   -- Apache.
   --
   --
   -- Import configuration information from a VARCHAR2 input.
   -- Note: This form has a 32K limitation on the VARCHAR2
   -- input size.
   --
   procedure import(port IN pls_integer,
                    cfg  IN varchar2);

   --
   -- Import configuration information from a index-by table
   -- of VARCHAR2 input.
   --
   procedure import(port IN pls_integer,
                    cfg  IN DBMS_EPGC.VARCHAR2_TABLE);

   --
   -- Import configuration information from a CLOB input.
   --
   procedure import(port IN pls_integer,
                    cfg  IN CLOB);


   -- The following APIs are to be used for exporting the
   -- configuration information of an Embedded PL/SQL Gateway
   -- to a flattened form so that it can be used against
   -- the mod_plsql gateway running under Apache. The configuration 
   -- information can be output to one of the following forms:
   --   -- a varchar2
   --   -- a index-by table of varchar2 elements
   --   -- a CLOB
   --

   --
   -- Output configuration to a varchar2 OUT argument.
   -- Note: this form has a 32K limitation.
   --
   procedure export(port   IN  pls_integer,
                    cfg    OUT NOCOPY varchar2);

   --
   -- Exports configuration information into a index-by table of
   -- varchar2 elements.
   --
   PROCEDURE export(port IN  PLS_INTEGER,
                    cfg  OUT NOCOPY dbms_epgc.varchar2_table);

   --
   -- Output configuration to a CLOB OUT argument.
   --
   procedure export(port   IN  pls_integer,
                    cfg    OUT NOCOPY CLOB);


END dbms_epgc;
/
show errors;
DROP PUBLIC SYNONYM dbms_epgc;
/
CREATE PUBLIC SYNONYM dbms_epgc FOR sys.dbms_epgc;
/
GRANT EXECUTE ON dbms_epgc TO PUBLIC
/
