Converting Mod_Plsql to the Embedded PL/SQL Gateway (DBMS_EPG)

A HunBug Document
Document No.: 11410
Database Version: 10.2.0.3
Last Updated: Sep 07
Author: HunBug

When we were upgrading a database from Oracle 9i Release 2 to Oracle 10g Release 2, we made a decision to use the new DBMS_EPG instead of mod_plsql, so that we could keep the gateway details inside the database and not have to install additional components to get mod_plsql to work. This article walks through the steps to create the gateway using DBMS_EPG on 10.2.0.3 Standard Edition.

Please be aware that one of the first things we found is that DBMS_EPG is not supported until 10.2.0.3, as it has bugs! So if you're on a earlier version you'll have to upgrade to get it to work.

The DBMS_EPG package is new for 10g. Unlike mod_plsql where the DAD details are stored as part of the apache server, DBMS_EPG resides in the database and uses the tns listener for its connections.

If you need to refer to your existing 9i DAD configuration settings, they can be found in the text file OH\Apache\modplsql\cfg\wdbsvr.app.

The mod_plsql DAD we converted connected to an account that had restricted privileges, with only execute permissions to specific procedures owned by the main schema user. This allowed anybody to call the procedures with security being taken care of within the code, but that's a whole different story. This walkthrough uses the new anonymous user for database connection and does not cover specific secure connections. More about this later.

Check XDB is Installed and is Valid.

Before we get going we need to check that XDB is installed correctly.

To check it is installed run the following in sqlplus.
SET LINESIZE 300
SELECT status, version, comp_name FROM dba_registry;
The results should contain
...
VALID       10.2.0.3.0                     Oracle XML Database
...
If this does not appear, it needs to be installed before you continue.

The XDB installation creates a user, also called XDB. To check all the objects for this user are valid, run the following in sqlplus.
SELECT COUNT(*) FROM dba_objects
 WHERE owner  = 'XDB'
   AND status = 'INVALID';
This should return 0. If there are invalid objects these should be compiled before you continue.

Setting up the Embedded PLSQL Gateway on the Database

First we need to add two parameters to the init.ora file, to configure the database to listen to the port for the DBMS_EPG requests.

dispatchers="(PROTOCOL=TCP)(SERVICE=<sid>XDB)"
local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(port=<port>))"
So, if your database sid is MYDB, your server hostname is MYSERVER and the tns listener uses port 1521 the entries you need to add to your init.ora are:
dispatchers="(PROTOCOL=TCP)(SERVICE=MYDBXDB)"
local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=MYSERVER)(port=1521))"
We then need to set the port number the requests will be recieved on. This is done from within the database. The default port is 8080 but this can be changed and must be changed if the port is already in use by another service. So we need to run the following from sqlplus.
CONN / AS SYSDBA
CALL DBMS_XDB.SETHTTPPORT(8080);
ALTER SYSTEM REGISTER;
EXIT
If FTP is also being used, this can also be set up too. Its default port is 2100.
CONN / AS SYSDBA
CALL DBMS_XDB.SETFTPPORT(2100);
ALTER SYSTEM REGISTER;
EXIT
OK, now restart the database and listener, so the init.ora parameters take effect.

Once they have both restarted you can check the listener is accepting requesting by looking at the status.
C:\> lsnrctl status
For our example above, with the server called MYSERVER using http port 8080, the following line should appear within the status results
...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER)(PORT=8080))(Presentation=HTTP)(Session=RAW))
...
By default the anonymous user account is locked, so we need to unlock it, otherwise we will get a logon prompt for the XDB account when we try to call a procedure, which we don't want.
ALTER USER anonymous ACCOUNT UNLOCK;
We now need to create a DAD and a test page to check it's all working.

Setting up a Database Access Descriptor 

The following procedure sets up a DAD for our requirements.

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

  l_dad          VARCHAR2(30) := '<DAD Name>';
  l_path         VARCHAR2(30) := '/<Path Name>/*';
  l_dbUser       VARCHAR2(30) := '<Username (of restricted user)>';
  l_docTable     VARCHAR2(30) := '<owner>.<table name>';
  l_authMode     VARCHAR2(30) := 'Basic';

  l_attrNames    DBMS_EPG.VARCHAR2_TABLE;
  l_attrValues   DBMS_EPG.VARCHAR2_TABLE;
 
BEGIN

  l_dbUser := UPPER(l_dbUser);

  BEGIN
    DBMS_EPG.DROP_DAD(l_dad);
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;

  DBMS_EPG.CREATE_DAD
  ( dad_name  => l_dad,
    path      => l_path );

  DBMS_EPG.SET_DAD_ATTRIBUTE
  ( dad_name   => l_dad,
    attr_name  => 'database-username',
    attr_value => l_dbUser);

  DBMS_EPG.SET_DAD_ATTRIBUTE
  ( dad_name   => l_dad,
    attr_name  => 'authentication-mode',
    attr_value => l_authMode);

  DBMS_EPG.SET_DAD_ATTRIBUTE
  ( dad_name   => l_dad,
    attr_name  => 'document-table-name',
    attr_value => l_docTable );

  DBMS_EPG.AUTHORIZE_DAD
  ( dad_name   => l_dad,
    user       => l_dbUser );

  DBMS_EPG.GET_ALL_DAD_ATTRIBUTES(l_dad,l_attrNames,l_attrValues);
  FOR i IN 1..l_attrNames.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(LPAD(l_attrNames(i),20)||' : '||l_attrValues(i));
  END LOOP;

END;
/

 

In our example, the user we connect to is called SECUREUSER and the user which contains our schema is called SCHEMAUSER. We use a table for uploading files, called UPLOADED_DOCUMENTS, so we would need to change the parameters as follows.

  l_dad          VARCHAR2(30) := 'testdad';
  l_path         VARCHAR2(30) := '/test/*';
  l_dbUser       VARCHAR2(30) := 'SECUREUSER';
  l_docTable     VARCHAR2(30) := 'SCHEMAUSER.UPLOADED_DOCUMENTS';

A notable difference between mod_plsql and DBMS_EPG is that mod_plsql allowed a schema name parameter to be entered, when a new blank configuration is created. This allowed us to enter a default schema to prefix any procedures in the url. DBMS_EPG does not have a similar attribute, so you either have to create synonyms or add the username into the url instead. EG: http://myserver:8080/test/schemauser.procedurename.

Viewing the DAD settings

The following procedure will display the settings, mappings and authorizations for all of the DAD's.

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE
  l_dadNames     DBMS_EPG.VARCHAR2_TABLE;
  l_attrNames    DBMS_EPG.VARCHAR2_TABLE;
  l_attrValues   DBMS_EPG.VARCHAR2_TABLE;
BEGIN
  DBMS_EPG.GET_DAD_LIST(l_dadNames);
  FOR d IN 1..l_dadNames.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(CHR(10)||l_dadNames(d));
    DBMS_EPG.GET_ALL_DAD_ATTRIBUTES(l_dadNames(d),l_attrNames,l_attrValues);
    FOR a IN 1..l_attrValues.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('-  '||RPAD(l_attrNames(a),25)||' : '||l_attrValues(a));
    END LOOP;
    DBMS_EPG.GET_ALL_DAD_MAPPINGS(l_dadNames(d),l_attrValues);
    FOR a IN 1..l_attrValues.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('-  '||RPAD('mapping',25)||' : '||l_attrValues(a));
    END LOOP;
    FOR a IN ( SELECT username FROM dba_epg_dad_authorization WHERE dad_name = l_dadNames(d) ) LOOP
      DBMS_OUTPUT.PUT_LINE('-  '||RPAD('authorized',25)||' : '||a.username);
    END LOOP;
  END LOOP;
END;
/

For our example, the procedure will output the following results

testdad
-  database-username         : SECUREUSER
-  authentication-mode       : Basic
-  document-table-name       : schemauser.uploaded_documents
-  mapping                   : /test/*
-  authorized                : SECUREUSER

Create a Test Page

The following procedure is a simple test page for checking that the gateway is configured correctly.

CREATE OR REPLACE PROCEDURE testPage IS
  l_instance   v$instance%ROWTYPE;
BEGIN
  SELECT * INTO l_instance FROM v$instance;    
  htp.htmlOpen;
  htp.bodyOpen;
  htp.p('<h1>Test Page</h1>');
  htp.preOpen;
  htp.p('<b>Username.:</b> '||USER);
  htp.p('<b>HostName.:</b> '||l_instance.host_name);
  htp.p('<b>Instance.:</b> '||l_instance.instance_name);
  htp.p('<b>Version..:</b> '||l_instance.version);
  htp.p('<b>Time Now.:</b> '||TO_CHAR(SYSTIMESTAMP));
  htp.preClose;
  htp.bodyClose;
  htp.htmlClose;
END;
/

The url for this, for our example, would be http://myserver:8080/test/testpage. If it were owned by the SCHEMAUSER user and execute permissions were granted to SECUREUSER, we could use http://myserver:8080/test/schemauser.testpage

Using the Embedded PLSQL Gateway no longer requires the .../pls/... be included in the url.

The following output from the test page shows that the ANONYMOUS user is being used for connecting to the database.

Test Page
Username.: ANONYMOUS
HostName.: MYSERVER
Instance.: mydb
Version..: 10.2.0.3.0
Time Now.: 28-SEP-07 02.11.57.250000000 PM +01:00



You should now be able to call procedures using the Embedded PLSQL Gateway as you would have with mod_plsql.

Further Reading

Oracle 10g Documentation: Reference page for DBMS_EPG
Oracle 11g Documentation: Developing Web Applications


HunBug Store








Comments are all individually read, none are automatically posted to the site.