Category: APEX

Michael McLaughlin had written last week some nice small blog posts on XML DB. I couldn’t resist to see if I could make this one easier… 😉

The package DBMS_EPG is sole based on the xdbconfig.xml file, which is the configuration file for the XML DB Protocol Server, which is not, by the way, an Apache Server configured inside the database, but a custom build protocol server hook-in into the Oracle listener, that passes its data to the Shared Server SQL*Net Architecture.

So I made some attempts on my 11gR2 database, without using the nowadays “extract” deprecated functions… After fiddling around with the XMLTABLE function, using DBMS_XDB.CFG_GET() to get the content of the xdbconfig.xml XML document (its result is an xmltype datatype), the following simplified code that results the DAD’s in a varchar2(15) list.

SQL> SELECT u.dad as "PL/SQL DAD List"
  2  FROM   XMLTable(XMLNAMESPACES (
  3                                  default 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'
  4                                ),
  5                  '//servlet-list/*[servlet-language="PL/SQL"]'
  6                  PASSING DBMS_XDB.CFG_GET()
  7                  COLUMNS DAD varchar2(15) PATH '/servlet/servlet-name/text()'
  8                 ) u
  9  ;

PL/SQL DAD List
---------------
APEX

1 row selected.

SQL>  EXECUTE dbms_epg.create_dad('MARCO','/XFiles/*');

PL/SQL procedure successfully completed.

SQL> SELECT u.dad as "PL/SQL DAD List"
  2  FROM   XMLTable(XMLNAMESPACES (
  3                                  default 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'
  4                                ),
  5                  '//servlet-list/*[servlet-language="PL/SQL"]'
  6                  PASSING DBMS_XDB.CFG_GET()
  7                  COLUMNS DAD varchar2(15) PATH '/servlet/servlet-name/text()'
  8                 ) u
  9  ;

PL/SQL DAD List
---------------
APEX
MARCO

2 rows selected.