| General Information |
| Note: 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. |
| Source |
$ORACLE_HOME/rdbms/admin/dbmsxml.sql |
| First Availability |
9.0.1 |
| Constants |
| Name |
Data Type |
Value |
| Conversion or Schema Specs |
| DTD |
NUMBER |
1 |
| NONE |
NUMBER |
0 |
| SCHEMA |
NUMBER |
2 |
| Conversion Type |
| ENTITY_DECODE |
conversionType |
1 |
| ENTITY_ENCODE |
conversionType |
0 |
| Null Handling |
| DROP_NULLS |
NUMBER |
0 |
| EMPTY_TAG |
NUMBER |
2 |
| NULL_ATTR |
NUMBER |
1 |
|
| Defined 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_LOB |
URITYPE |
| DBMS_XMLGEN_LIB |
WRI$_ADV_OBJSPACE_TREND_T |
| SDO_OLS |
WWV_FLOW_DATLOAD_XML |
| URIFACTORY |
XMLTYPE |
|
| Security Model |
Execute is granted to PUBLIC |
| Is XDK For PL/SQL Installed |
col comp_name format a45
SELECT comp_name, status, substr(version,1,10) as version
FROM dba_registry;
-- should return
COMP_NAME STATUS VERSION
---------------------- -------- ---------
Oracle Database Catalog Views VALID 10.2.0.1.0
Oracle Database Packages and Types VALID 10.2.0.1.0
Oracle Workspace Manager VALID 10.2.0.1.0
JServer JAVA Virtual Machine VALID 10.2.0.1.0
Oracle XDK VALID 10.2.0.1.0
Oracle Database Java Packages VALID 10.2.0.1.0
Oracle Expression Filter VALID 10.2.0.1.0
Oracle interMedia VALID 10.2.0.1.0
Oracle Data Mining VALID 10.2.0.1.0
Oracle Text VALID 10.2.0.1.0
Oracle XML Database VALID 10.2.0.1.0
OLAP Analytic Workspace VALID 10.2.0.1.0
Oracle OLAP API VALID 10.2.0.1.0
OLAP Catalog VALID 10.2.0.1.0
Spatial VALID 10.2.0.1.0
Oracle Enterprise Manager VALID 10.2.0.1.0
Oracle Ultra Search NO SCRIPT 10.1.0.4.0
-- if not installed run
$ORACLE_HOME/rdbms/admin/initxml.sql |
| Demo Tables |
-- emp table from SCOTT schema
CREATE TABLE test (testcol VARCHAR2(4000)); |
| |
| clearBindValues |
| Undocumented |
dbms_xmlgen.clearBindValues(ctx IN ctxHandle); |
| TBD |
| |
| closeContext |
| Closes a given context and releases all resources associated with it, including the SQL cursor and bind and define buffers |
dbms_xmlgen.closeContext(ctx IN ctxHandle); |
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
emp_no NUMBER := 7369;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_xmlgen.closeContext(ctx);
dbms_output.put_line(ctx);
END;
/ |
| |
| Convert |
Converts the XML into the escaped or unescaped XML equivalent
Overload 1 |
dbms_xmlgen.convert(xmlData IN VARCHAR2, flag IN NUMBER := ENTITY_ENCODE)
RETURN VARCHAR2;
-- Flags are the Conversion Type constants |
| TBD |
| Overload 2 |
dbms_xmlgen.convert(xmlData IN CLOB, flag IN NUMBER := ENTITY_ENCODE)
RETURN CLOB;
-- Flags are the Conversion Type constants |
| TBD |
| |
| getNumRowsProcessed |
| 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
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;
/ |
| |
| getXML |
Gets the XML document
Overload 1 |
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
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;
/ |
| |
| getXMLType |
Gets the XML document and returns it as XMLType
Overload 1 |
dbms_xmlgen.getXMLType(
ctx IN ctxHandle,
tmpxmltype IN OUT NOCOPY xmltype,
dtdOrSchema IN NUMBER := NONE); |
| See newContextFromHierarchy Demo |
| Overload 2 |
dbms_xmlgen.getXMLType(
ctx IN ctxHandle,
dtdOrSchema IN number := NONE)
RETURN sys.XMLType;
|
| TBD |
| Overload 3 |
dbms_xmlgen.getXMLType(
sqlQuery IN VARCHAR2,
dtdOrSchema IN NUMBER := NONE)
RETURN sys.XMLType;
|
| TBD |
| |
| newContext |
Creates a new context handle
Overload 1 |
dbms_xmlgen.newContext(query VARCHAR2) RETURN ctxHandle; |
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
emp_no NUMBER := 7369;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_output.put_line(ctx);
dbms_xmlgen.closeContext(ctx);
END;
/ |
| Overload 2 |
dbms_xmlgen.newContext(queryString SYS_REFCURSOR) RETURN ctxHandle; |
| TBD |
| |
| newContextFromHierarchy |
| Undocumented. Used as part of new XML document creation |
dbms_xmlgen.newContextFromHierarchy(queryString IN
VARCHAR2)
RETURN ctxHandle; |
conn scott/tiger
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);
lob_output(RESULT.getClobVal());
END;
/ |
| |
| restartQUERY |
| Restarts the query to start fetching from the beginning |
dbms_xmlgen.restartQUERY(ctx IN ctxHandle); |
| TBD |
| |
| removeXSLTParam |
| Undocumented XSLT Support |
dbms_xmlgen.removeXSLTParam(ctx IN ctxType, name IN VARCHAR2); |
| TBD |
| |
| setBindValue |
| Undocumented |
dbms_xmlgen.setBindValue(
ctx IN ctxHandle,
bindName IN VARCHAR2,
bindValue IN VARCHAR2); |
| TBD |
| |
| setCheckInvalidChars |
| Sets whether checking for invalid characters such as the NULL character |
dbms_xmlgen.setCheckInvalidChars(ctx IN ctxHandle, chk IN BOOLEAN); |
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
emp_no NUMBER := 7369;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_xmlgen.setCheckInvalidChars(ctx, TRUE);
dbms_xmlgen.closeContext(ctx);
dbms_output.put_line(ctx);
END;
/ |
| |
| setConvertSpecialChars |
| Sets whether special characters such as $, which are non-XML characters,
should be converted or not to their escaped representation |
dbms_xmlgen.setConvertSpecialChars(ctx IN ctxHandle, conv IN BOOLEAN); |
| TBD |
| |
| setIndentationWidth |
| Undocumented |
dbms_xmlgen.setIndentationWidth(ctx IN ctxHandle, width IN NUMBER); |
conn scott/tiger
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;
/ |
| |
| SetMaxRows |
| Sets the maximum number of rows to be fetched each time |
dbms_xmlgen.setMaxRows(ctx IN ctxHandle, maxRows IN NUMBER); |
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;
/ |
| |
| SetNullHandling |
| Sets NULL handling options |
dbms_xmlgen.setNullHandling(ctx IN ctxHandle, flag IN NUMBER);
-- Flags are the NULL handling constants |
| TBD |
| |
| SetPrettyPrinting |
| Undocumented |
dbms_xmlgen.setPrettyPrinting(ctx IN ctxHandle, pp IN BOOLEAN); |
| TBD |
| |
| setRowSetTag |
| Sets the name of the element enclosing the entire result |
dbms_xmlgen.setRowSetTag(ctx IN ctxHandle, rowSetTagName IN VARCHAR2); |
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;
/ |
| |
| setRowTag |
| Sets the name of the element enclosing each row of the result |
dbms_xmlgen.setRowTag(ctx IN ctxHandle, rowTagName IN VARCHAR2); |
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;
/ |
| |
| setSkipRows |
| Sets the number of rows to skip every time before generating the XML |
dbms_xmlgen.setSkipRows(ctx IN ctxHandle, skipRows IN NUMBER); |
| TBD |
| |
| setXSLT |
Undocumented
Overload 1 |
dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN CLOB); |
| TBD |
| Overload 2 |
dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN XMLType); |
| TBD |
| Overload 3 |
dbms_xmlgen.setXSLT(ctx IN ctxType, uri IN VARCHAR2); |
| TBD |
| |
| setXSLTParam |
| Undocumented |
dbms_xmlgen.setXSLT(ctx IN ctxType, name IN VARCHAR2, value IN VARCHAR2); |
| TBD |
| |
| useItemTabsForColl |
| Forces the use of the collection column name appended with the tag _ITEM for collection elements |
dbms_xmlgen.useItemTagsForColl(ctx IN ctxHandle); |
| TBD |
| |
| useNullAttributeIndicator |
|
Specified whether to use an XML attribute to indicate NULLness, or to do it by omitting the inclusion of the particular entity in the XML document. |
dbms_xmlgen.useNullAttributeIndicator(
ctx IN ctxHandle,
attrind IN BOOLEAN := TRUE); |
| TBD |
