8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

DBMS_EPG - The Embedded PL/SQL Gateway in Oracle 10g Database Release 2

Since the introduction of XML DB in Oracle 9i Release 2, the Oracle server has contained an embedded HTTP server in addition to the Apache HTTP server. In Oracle 10g Release 2 this HTTP server can be used as an embedded PL/SQL gateway to run PL/SQL applications, similar to mod_plsql in the Oracle HTTP Server. The administration of Database Access Descriptors (DADs) for the XML DB HTTP server is performed using the DBMS_EPG package. This article presents a simple example of its use.

Related articles.

Schema Setup

We must ensure that we have access to the SCOTT sample schema and the XDB schema.

CONN sys/password AS SYSDBA

-- Create SCOTT schema if you don't currently have it.
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

Create DAD

We use the CREATE_DAD procedure to create a database access descriptor with an associated virtual path.

BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'my_epg_dad',
    path     => '/my_epg_dad/*');
END;
/

The current mappings for a specific DAD can be retrieved using the get_all_dad_mappings procedure.

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_paths  DBMS_EPG.varchar2_table;
BEGIN
  DBMS_EPG.get_all_dad_mappings (
    dad_name => 'my_epg_dad',
    paths    => l_paths);

  DBMS_OUTPUT.put_line('Mappings');
  DBMS_OUTPUT.put_line('========');
  FOR i IN 1 .. l_paths.count LOOP
    DBMS_OUTPUT.put_line(l_paths(i));
  END LOOP;
END;
/
Mappings
========
/my_epg_dad/*

PL/SQL procedure successfully completed.

SQL>

The mappings associated with existing DADs can be altered using the UNMAP_DAD and MAP_DAD procedures.

BEGIN
  DBMS_EPG.unmap_dad (
    dad_name => 'my_epg_dad',
    path     => '/my_epg_dad/*');

  DBMS_EPG.map_dad (
    dad_name => 'my_epg_dad',
    path     => '/my_epg_dad/*');
END;
/

The SET_DAD_ATTRIBUTE procedure is used to associate attributes with the DAD. In the example below the database-username and default-page attributes are set for the DAD.

BEGIN
  DBMS_EPG.set_dad_attribute (
    dad_name   => 'my_epg_dad',
    attr_name  => 'default-page',
    attr_value => 'home');

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'my_epg_dad',
    attr_name  => 'database-username',
    attr_value => 'SCOTT');
END;
/

The attributes associated with a specific DAD can be retrieved using the GET_ALL_DAD_ATTRIBUTES procedure. The following example should produce a list of attributes, but at the time of writing this procedure does not appear to work.

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_attr_names   DBMS_EPG.varchar2_table;
  l_attr_values  DBMS_EPG.varchar2_table;
BEGIN
  DBMS_OUTPUT.put_line('Attributes');
  DBMS_OUTPUT.put_line('==========');

  DBMS_EPG.get_all_dad_attributes (
    dad_name    => 'my_epg_dad',
    attr_names  => l_attr_names,                       
    attr_values => l_attr_values);

  FOR i IN 1 .. l_attr_names.count LOOP
    DBMS_OUTPUT.put_line(l_attr_names(i) || '=' || l_attr_values(i));
  END LOOP;
END;
/

The GET_DAD_ATTRIBUTE function is used to retrieve the value of a specific attribute, and proves that the attributes are being set correctly, despite the output of the previous example.

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_attr_name   VARCHAR2(30);
  l_attr_value  VARCHAR2(30);
  
  PROCEDURE my_get_dad_attribute (p_dad_name   IN  VARCHAR2,
                                  p_attr_name  IN  VARCHAR2) AS
    l_attr_value  VARCHAR2(30);
  BEGIN
    l_attr_value := DBMS_EPG.get_dad_attribute (
                      dad_name  => p_dad_name,
                      attr_name => p_attr_name);
  
    DBMS_OUTPUT.put_line(p_attr_name || '=' || l_attr_value);
  END;
BEGIN
  DBMS_OUTPUT.put_line('Attribute');
  DBMS_OUTPUT.put_line('=========');

  l_attr_name  := 'authentication-mode';
  my_get_dad_attribute('my_epg_dad', 'default-page');
  my_get_dad_attribute('my_epg_dad', 'database-username');
  my_get_dad_attribute('my_epg_dad', 'authentication-mode');
END;
/
Attribute
=========
default-page=home
database-username=
authentication-mode=

PL/SQL procedure successfully completed.

SQL>

The DELETE_DAD_ATTRIBUTE procedure is used to remove DAD attributes.

BEGIN
  DBMS_EPG.delete_dad_attribute (
    dad_name   => 'my_epg_dad',
    attr_name  => 'default-page');
END;
/

The AUTHORIZE_DAD procedure is used to enable access to the specified schema via the DAD. If the user parameter is not specified the current user is assumed. If multiple users are authorized the database-username attribute is used to decide which one to use.

BEGIN
  DBMS_EPG.authorize_dad (
    dad_name => 'my_epg_dad',
    user     => 'SCOTT');
END;
/

The authorization can be reversed using the DEAUTHORIZE_DAD procedure.

BEGIN
  DBMS_EPG.deauthorize_dad (
    dad_name => 'my_epg_dad',
    user     => 'SCOTT');
END;
/

Create Test Procedure

Once the DAD is configured we need to compile a procedure into the SCOTT schema so that the DAD can be tested.

CREATE OR REPLACE PROCEDURE scott.home IS
BEGIN
  HTP.htmlopen;
  HTP.headopen;
  HTP.title('This is a test page!');
  HTP.headclose;
  HTP.bodyopen;
  HTP.print('This is a test page! DateTime: ' || TO_CHAR(SYSTIMESTAMP));
  HTP.bodyclose;
  HTP.htmlclose;
END home;
/
SHOW ERRORS

Setting The Gateway Port

The port associated with the gateway can be displayed using the following query.

SELECT DBMS_XDB.gethttpport FROM dual;

GETHTTPPORT
-----------
	  0

SQL>

A value of "0" means the gateway is disabled. You can set the port number, in this case to 8080, using the following command.

EXEC DBMS_XDB.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL>


SELECT DBMS_XDB.gethttpport FROM dual;

GETHTTPPORT
-----------
       8080

SQL>

The embedded HTTP server should register itself automatically with the listener. In newer version of the database this seems very reliable. In older versions I have sometimes been forced to add the following entries into the DESCRIPTION_LIST of the listener.ora file. The listener should be reloaded or restarted before any tests are performed.

(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW)
)
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=2100))(Presentation=FTP)(Session=RAW)
)

The DAD can now be tested by calling the following URL from a browser.

http://<server-name>:8080/my_epg_dad/home

Once you've convinced yourself that the DAD works you are ready to develop PL/SQL web applications using the PL/SQL Web Toolkit or PL/SQL Server Pages.

Anonymous Access

If you wish to enable anonymous access to the XML DB repository, the following code creates the "<allow-repository-anonymous-access>" element if it is missing, or updates it if it is already present in the xdbconfig.xml file.

CONN sys/password AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_configxml XMLTYPE;
  l_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  l_configxml := DBMS_XDB.cfg_get();

  IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           (
             l_configxml,
       	     '/xdbconfig/sysconfig/protocolconfig/httpconfig',
       	     'allow-repository-anonymous-access',
       	     XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' ||
       	              l_value ||
       	             '</allow-repository-anonymous-access>'),
       	     'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
       	   )
    INTO   l_configxml
    FROM   dual;

    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           (
             DBMS_XDB.cfg_get(),
             '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
             l_value,
             'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;

    DBMS_OUTPUT.put_line('Element updated.');
  END IF;

  DBMS_XDB.cfg_update(l_configxml);
  DBMS_XDB.cfg_refresh;
END;
/

Once the the "<allow-repository-anonymous-access>" element is set to "true", anonymous access to the XML DB repository is enabled by unlocking the anonymous database account.

CONN sys/password AS SYSDBA

ALTER USER anonymous ACCOUNT UNLOCK;

Removing anonymous access to the XML DB repository can be accomplished by locking the anonymous database account, or setting the "<allow-repository-anonymous-access>" element back to "false".

If you need to remove the "<allow-repository-anonymous-access>" element entirely, it can be accomplished using the following code.

CONN sys/password AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_configxml XMLTYPE;
BEGIN
  l_configxml := DBMS_XDB.cfg_get();

  IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') != 0 THEN
    SELECT deleteXML
           (
              l_configxml,
              '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access',
              'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
           )
    INTO   l_configxml
    FROM   dual;

    DBMS_XDB.cfg_update(l_configxml);
    DBMS_XDB.cfg_refresh;

    DBMS_OUTPUT.put_line('Element deleted.');
  END IF;
END;
/

The DROP_DAD is used to remove an unwanted DAD.

-- Cleanup the DAD.
BEGIN
  DBMS_EPG.drop_dad (
    dad_name => 'my_epg_dad');
END;
/

-- Lock the users again.
ALTER USER scott ACCOUNT LOCK;

Security

You should avoid creating DADs that grant access to the schema owner, as this will allow access to all objects owned and accessible by that schema. Instead, create an application user and let the DAD connect to that. This application user can then be granted the necessary privileges to access just those objects it needs. This concept is explained here.

In addition, anonymous access should be avoided where possible.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.