8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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:
- DBMS_EPG
- PL/SQL Web Toolkit
- PL/SQL Server Pages
- XML DB Developer's Guide
- Schema Owners and Application Users
Hope this helps. Regards Tim...