Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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
DBA_AUTO_INDEX_CONFIG
DBMS_XMLGEN_LIB
URIFACTORY
DBMS_CUBE
DRIREPM
URITYPE
DBMS_LOB
PRVTEMX_CELL
WRI$_ADV_OBJSPACE_TREND_T
DBM_RCVCAT
SDO_OLS
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
ORDER BY 1;
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 NOCOPY 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;
/