Dear Guys,
This is a virtual rip off from Tim's article at the location
http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php
This is for my reference and ease,if someone finds it useful thank Tim@Oracle-base.com for this article.
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.
First 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;
Next 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;
/
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
The embedded HTTP server should register itself automatically with
the listener. The XML DB documentation suggests that port 8080 should be
presented for HTTP access automatically. In my experience this does not
seem to be the case, so I was forced to added 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 ar3 ready to develop PL/SQL web applications using the
PL/SQL Web Toolkit or
PL/SQL Server Pages.
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;