MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle 11g XDB DADs

with 2 comments

Somebody asked me why the DBMS_EPG.GET_DAD_LIST is a procedure because you can’t just simply list the DAD values. I answered that Oracle chose to implement it that way. Then, they asked how they could query it. I suggested they just run the epgstat.sql diagnostic script provided in the $ORACLE_HOME/rdbms directory, which provides those values and much more.

You can run the diagnostic script as the SYS privileged user, or as any user that has been granted the XDBADMIN role, like this:

SQL> @?/rdbms/admin/epgstat.sql

Notwithstanding the diagnostic script, they asked how you could wrap the OUT mode PL/SQL data type in the procedure call, and return the list of values in a SQL query. Because the formal parameter is a PL/SQL data type, this requires two key things. One is a local variable that maps to the DBMS_EPG package collection data type, and a pipelined table function. Here’s one way to solve the problem:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

CREATE OR REPLACE TYPE dad_list AS TABLE OF VARCHAR2(4000);
/
 
CREATE OR REPLACE FUNCTION get_dbms_epg_dads
RETURN dad_list
PIPELINED IS
  -- Deine a local variable as the OUT mode target of GET_DAD_LIST procedure.
  SOURCE DBMS_EPG.VARCHAR2_TABLE;
  -- Declare a local variable of the SQL collection data type.  
  list DAD_LIST := dad_list(); 
BEGIN
  -- Call the procedure to populate the source.
  dbms_epg.get_dad_list(SOURCE);
  -- Extend space for all defined DAD values.
  list.EXTEND(SOURCE.COUNT);
  -- Assign values from PL/SQL collection to SQL collection.
  FOR i IN 1..source.COUNT LOOP
    list(i) := SOURCE(i);
    PIPE ROW(list(i));
  END LOOP;
  RETURN;
END get_dbms_epg_dads;
/
 
-- Set SQL*Plus width.
SET LINESIZE 79
 
-- Query collection.
SELECT column_value AS "DAD LIST"
FROM   TABLE(get_dbms_epg_dads);

Marco Gralike provided a simpler approach them the Pipelined Table Function here. I’ve copied the code example below:

1
2
3
4
5
6

SELECT u.dad AS "PL/SQL DAD List"
FROM   XMLTable(XMLNAMESPACES ( DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd' )
       , '/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-language="PL/SQL"]'
       PASSING DBMS_XDB.CFG_GET()
       COLUMNS DAD VARCHAR2(15)
       PATH '/servlet/servlet-name/text()') u;

Hope this proves handy to somebody else, too.

Written by maclochlainn

December 1st, 2009 at 10:46 pm

Posted in Oracle,pl/sql,XDB Server

«
»