ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Converts the results of a SQL query to a canonical XML format. The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB.
This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel. This package can only be run on the database.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
Conversion/Schema Specs
DTD
NUMBER
1
NONE
NUMBER
0
SCHEMA
NUMBER
2
Conversion Types
ENTITY_DECODE
conversionType
1
ENTITY_ENCODE
conversionType
0
NULL Handling
DROP_NULLS
NUMBER
0
EMPTY_TAG
NUMBER
2
NULL_ATTR
NUMBER
1
Data Types
SUBTYPE ctxHandle IS NUMBER;
SUBTYPE ctxType IS NUMBER;
SUBTYPE conversionType IS NUMBER;
TYPE PARAM_HASH IS TABLE OF VARCHAR2(100)
INDEX BY VARCHAR2(32);
/
Dependencies
DBMS_CUBE
DRIREPM
URITYPE
DBMS_LOB
SDO_OLS
WRI$_ADV_OBJSPACE_TREND_T
DBMS_XMLGEN_LIB
URIFACTORY
XMLTYPE
Determine if XDB is installed and valid
col comp_name format a45
SELECT comp_name, status, substr(version,1,10) as version
FROM dba_registry;
Returns the number of SQL rows that were processed in the last call to getXML
dbms_xmlgen.getNumRowsProcessed(ctx IN ctxHandle) RETURN NUMBER;
conn scott/tiger@pdbdev
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
i PLS_INTEGER;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
xml := dbms_xmlgen.getXML(ctx);
i := dbms_xmlgen.getNumRowsProcessed(ctx);
dbms_output.put_line(TO_CHAR(i));
END;
/
dbms_xmlgen.getXML(
ctx IN ctxHandle,
tmpclob IN OUT NCOPY CLOB,
dtdOrSchema IN NUMBER := NONE);
TBD
Overload 2
dbms_xmlgen.getXML(
ctx IN ctxHandle,
dtdOrSchema IN NUMBER := NONE)
RETURN CLOB;
TBD
Overload 3
dbms_xmlgen.getXML(
sqlQuery IN VARCHAR2,
dtdOrSchema IN NUMBER := NONE)
RETURN CLOB;
conn scott/tiger@pdbdev
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = ' || emp_no);
dbms_output.put_line(ctx);
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- display first part
dbms_output.put_line(xmlc);
END;
/
Undocumented. Used as part of new XML document creation
dbms_xmlgen.newContextFromHierarchy(queryString IN VARCHAR2) RETURN ctxHandle;
conn scott/tiger@pdbdev
desc emp
set serveroutput on
DECLARE
qryctx dbmx_xmlgen.ctxhandle;
result XMLTYPE;
PROCEDURE lob_output (p_clob CLOB) IS l_clob CLOB;
l_clob_length NUMBER;
l_iterations NUMBER;
l_chunk VARCHAR2(32767);
l_chunk_length NUMBER := 32767;
BEGIN
l_clob := p_clob;
l_clob_length := dbms_lob.getlength(l_clob);
l_iterations := CEIL(l_clob_length / l_chunk_length);
FOR i IN 0 .. l_iterations - 1 LOOP
l_chunk := dbms_lob.substr(l_clob,l_chunk_length,i*l_chunk_length+1);
dbms_output.put_line(l_chunk);
END LOOP;
END;
BEGIN
qryctx := dbms_xmlgen.newcontextFromHierarchy('SELECT level,
XMLElement("Position", XMLElement("Name", ename), XMLElement("Title",
job)) FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr is NULL');
result := dbms_xmlgen.getxmltype(qryctx);
dbms_xmlgen.closeContext(qryctx);
dbms_xmlgen.setIndentationWidth(
ctx IN ctxHandle,
width IN NUMBER);
conn scott/tiger@pdbdev
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_xmlgen.setIndentationWidth(ctx, 10);
dbms_output.put_line(ctx);
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- display first part
dbms_output.put_line(xmlc);
END;
/
Sets the maximum number of rows to be fetched each time
dbms_xmlgen.setMaxRows(
ctx IN ctxHandle,
maxRows IN NUMBER);
conn scott/tiger@pdbdev
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
i PLS_INTEGER;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp');
dbms_xmlgen.setMaxRows(ctx, 14);
xml := dbms_xmlgen.getXML(ctx);
i := dbms_xmlgen.getNumRowsProcessed(ctx);
dbms_output.put_line(TO_CHAR(i));
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- display first part
INSERT INTO test (testcol) VALUES (xmlc);
COMMIT;
END;
/
Sets the name of the element enclosing the entire result
dbms_xmlgen.setRowSetTag(
ctx IN ctxHandle,
rowSetTagName IN VARCHAR2);
conn scott/tiger@pdbdev
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_xmlgen.setRowSetTag(ctx, 'SRST');
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- display first part
dbms_output.put_line(xmlc);
END;
/
Sets the name of the element enclosing each row of the result
dbms_xmlgen.setRowTag(
ctx IN ctxHandle,
rowTagName IN VARCHAR2);
conn scott/tiger@pdbdev
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_xmlgen.setRowTag(ctx, 'SRT');
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- display first part
dbms_output.put_line(xmlc);
END;
/
