Oracle | UTL

Oracle UTL_XML Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose PL/SQL wrapper over COREs C-based XML/XSL processor.

Note This is the package header for the PL/SQL interface to CORE's C-based XML Parser and XSL Processor. It currently does not provide an interface to CORE's C-based DOM, SAX and Namespace APIs. You MUST call function XMLINIT before any others in this package. Pkg. body and trusted lib. implementations are in: /vobs/rdbms/src/server/datapump/ddl.

AUTHID DEFINER

Constants

Name	Data Type	Value
DISCARD_WHITESPACE	BINARY_INTEGER	2
DTD_ONLY	BINARY_INTEGER	4
STOP_ON_WARNING	BINARY_INTEGER	8
VALIDATE	BINARY_INTEGER	1

Data Types Data Type (Opaque Handles) SUBTYPE xmlCtx IS PLS_INTEGER;

Dependencies

DBMS_DATAPUMP	DBMS_SYS_ERROR	KUPW$WORKER
DBMS_METADATA	KU$_10_2_STRMTABLE_VIEW	LOGMNR_KRVRDLUID3
DBMS_METADATA_INT	KU$_STRMTABLE_VIEW	UTL_XML_LIB
DBMS_METADATA_UTIL	KUPF$FILE_INT

Documented No

First Available 9.0.1

Security Model Owned by SYS with no privileges granted.

Access to some subpograms prevented by an Accessible By clause.

Source {ORACLE_HOME}/rdbms/admin/utlcxml.sql

Subprograms

COMPARE	SSCFIND	XMLPARSE
GETDDLSRCFROMXML	SSCGETCTX	XMLSETMEMDEBUG
GETFDO	SSCMINIMIZECACHE	XMLSETPARSEFLAG
GETHASHCODE	SSCPARSE	XSLLOADFROMFILE
GETNEXTTYPEID	SSCPURGE	XSLRESETPARAMS
HASTSTZ	SSCSETDEBUG	XSLSETPARAM
ISNAMEOMF	TYPEHASHCODEEQ	XSLTRANSFORM
LONG2CLOB	WINDOWS32	XSLTRANSFORMCTOX
PARSEEXPR	XMLCLEAN	XSLTRANSFORMXTOC
PARSEQUERY	XMLDUMPCTXS	XSLTRANSFORMXTOX
SETXMLTRANSFORMENGINE	XMLINIT

COMPARE

Compares the DDL of 2 input objects and returns a diff document utl_xml.compare( ctx IN xmlCtx, doc1 IN CLOB, doc2 IN CLOB, difDoc IN CLOB, flags IN OUT BINARY_INTEGER);

TBD

GETDDLSRCFROMXML

Bypass XSL processing for retrieval of PL/SQL source from source$

Generating DDL for very large pkgs via XSL can be very expensive. This routine forms the heart of an alternate fast method of retrieving the source of an object via C string manipulations rather than XSL transformation. utl_xml.getDDLSrcFromXML( src IN CLOB, dst IN OUT NOCOPY CLOB);

DECLARE

       inVal  CLOB;

       outVal CLOB;

      

        CURSOR scur IS

        SELECT text

        FROM dba_source

        WHERE owner = 'SYS'

        AND name = 'UTL_FILE'

        ORDER BY line;

      BEGIN

        FOR srec IN scur LOOP

          inVal := inVal || ' ' || srec.text;

        END LOOP;

      

        utl_xml.getDDLSrcFromXML(inVal, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      

      PL/SQL procedure successfully completed.

GETFDO

Return the format descriptor object for objects on this platform utl_xml.getFdo RETURN RAW;

SELECT utl_xml.getFdo;

      

      GETFDO

      --------------------------------------------------------------------------

      0000006001240F0B0 <200 character string beginning with>

GETHASHCODE

Upgrading from 8.1.7 corrupts the hashcode in type$, so this functions calls kotgHashCode utl_xml.gethashcode( schema IN VARCHAR2, -- type schema typename IN VARCHAR2, -- type name flag IN BINARY_INTEGER, -- 1 = only return V1 hashcode, 0 = return any hashcode hashcode OUT RAW); -- returned hashcode

set serveroutput on

      

      DECLARE

       hc RAW(32);

      BEGIN

        utl_xml.gethashcode('SYSTEM', 'REPCAT$_OBJECT_NULL_VECTOR', 0, hc);

        dbms_output.put_line(hc);

      END;

      /

      

      PL/SQL procedure successfully completed.

GETNEXTTYPEID

Given the current value of next_typeid for a type hierarchy and another typeid, see if next_typeid needs to be incremented, and, if so, what its new value should be utl_xml.getnexttypeid( next_typeid IN RAW, typeid IN RAW, new_next_typeid OUT RAW);

TBD

HASTSTZ

Returns TRUE if the type have a TSTZ element or attribute utl_xml.HasTsTz( schema IN VARCHAR2, typename IN VARCHAR2) RETURN BOOLEAN;

TBD

ISNAMEOMF

Tests a file name to see if it is an OMF name

0 = not OMF
1 = OMF utl_xml.isnameomf( fname IN VARCHAR2, isomf OUT BINARY_INTEGER);

No demo possible

LONG2CLOB

Fetch a LONG as a CLOB

File comments state: "tab and col must belong to a short list of valid values which obviously is not reflected in the demo at right as the "short list" is not known. utl_xml.long2clob( tab IN VARCHAR2, col IN VARCHAR2, row_id IN ROWID, lobloc IN OUT NOCOPY CLOB);

CREATE TABLE t1 (x INT, y LONG);

      

      INSERT INTO t1 VALUES (1, RPAD('*',100,'*'));

      INSERT INTO t1 VALUES (2, RPAD('*',100,'$'));

      INSERT INTO t1 VALUES (3, RPAD('*',100,'#'));

      COMMIT;

      

      desc t1

      

      SELECT * FROM t1;

      

      set serveroutput on

      

      DECLARE

       rid    ROWID;

       lob    CLOB;

       xmlctx PLS_INTEGER;

      BEGIN

        SELECT rowid

        INTO rid

        FROM t1

        WHERE rownum = 1;

      

        xmlctx := sys.utl_xml.xmlInit;

      

        sys.utl_xml.long2clob('T1', 'Y', rid, lob);

        dbms_output.put_line(rid);

        dbms_output.put_line(lob);

      END;

      /

      *

      ORA-31600: invlaid input value T1 for parameter TAB in function UTL_XML.LONG2CLOB

PARSEEXPR

Parse an expression (boolean or arithmetic) and return in a CLOB as XML utl_xml.parseexpr( schema IN VARCHAR2, tab IN VARCHAR2, sqltext IN CLOB, arith IN BINARY_INTEGER, lobloc IN OUT NOCOPY CLOB);

No demo possible

PARSEQUERY

Parse a SQL query and return in a CLOB as XML utl_xml.parsequery( user IN VARCHAR2, sqltext IN CLOB, lobloc IN OUT NOCOPY CLOB);

No demo possible

SETXMLTRANSFORMENGINE

Sets the transformation engine to either XmlXvm or LPX. If TRUE , then XmlXvm otherwise Lpx. utl_xml.setXmlTransformEngine(use_xmlxvm_engine IN BOOLEAN);

exec utl_xml.setXmlTransformEngine(TRUE);

      

      PL/SQL procedure successfully completed.

SSCFIND

Find stylesheet by index or name or allocate it utl_xml.sscfind( ss_index IN BINARY_INTEGER, ss_name IN VARCHAR2) RETURN BINARY_INTEGER;

TBD

SSCGETCTX

Get xml context identifier for the specified stylesheet utl_xml.sscGetCtx(ss_index IN BINARY_INTEGER) RETURN xmlctx;

TBD

SSCMINIMIZECACHE

Minimize stylesheet cache LRU size (set to 1) utl_xml.sscMinimizeCache;

exec utl_xml.sscMinimizeCache;

      

      PL/SQL procedure successfully completed.

SSCPARSE

Sets the top-level style sheet for the upcoming transform and also establishes the base URI for any included or imported stylesheets utl_xml.sscParse( ss_index IN BINARY_INTEGER, uri IN VARCHAR2, encoding IN VARCHAR2 DEFAULT NULL);

No demo possible

SSCPURGE

Purge the stylesheet cache utl_xml.sscPurge;

exec utl_xml.sscPurge;

      

      PL/SQL procedure successfully completed.

SSCSETDEBUG

Set stylesheet cache's debug level to match prvtmeti.sql utl_xml.sscSetDebug(value IN BOOLEAN);

exec utl_xml.sscSetDebug(FALSE);

      

      PL/SQL procedure successfully completed.

TYPEHASHCODEEQ

Does the hashcode match the hc for the type? The hashcode versions changed between 10.2 and 11g so a simple compare doesn't work. This is a wrapper around kottyphcequ utl_xml.typehashcodeeq( schema IN VARCHAR2, -- type schema typename IN VARCHAR2, -- type name hashcode IN RAW) -- hashcode to check RETURN BOOLEAN;

TBD

WINDOWS32

Determines if Oracle is running on a 32bit Windows NT system

Why this procedure exists in version 23ai is outside of the extremes of our imagination.

Why it returns no value equally fascinating as testing was performed in Linux. utl_xml.windows32(flag OUT BINARY_INTEGER);

set serveroutput on

      

      DECLARE

       i BINARY_INTEGER;

      BEGIN

        utl_xml.windows32(i);

        dbms_output.put_line(i);

      END;

      /

      

      PL/SQL procedure successfully completed.

XMLCLEAN

Cleans up memory from last doc. associated with this parser utl_xml.xmlclean(ctx IN xmlCtx);

TBD

XMLDUMPCTXS

Dump info on the active XML contexts to the trace file utl_xml.xmlDumpCtxs;

exec utl_xml.xmlDumpCtxs;

      

      PL/SQL procedure successfully completed.

XMLINIT

Initializes a DOM XML parser utl_xml.xmlInit RETURN xmlCtx;

DECLARE

       handle utl_xml.xmlCtx;

      BEGIN

        handle := utl_xml.xmlInit;

      END;

      /

      

      PL/SQL procedure successfully completed.

XMLPARSE

Parses target of a URI (file or DB column) into a DOM format

Overload 1 utl_xml.xmlparse( ctx IN xmlCtx, uri IN VARCHAR2, encoding IN VARCHAR2 DEFAULT NULL);

No demo possible

Parses the CLOB source doc into a DOM format

Overload 2 utl_xml.xmlparse( ctx IN xmlCtx, srcDoc IN CLOB);

This overload is not protected with an ACCESSIBLE BY clause

XMLSETMEMDEBUG

Sets kux's Lpx memory tracing utl_xml.xmlSetMemDebug( value IN BOOLEAN, xvm_value IN BOOLEAN);

exec utl_xml.xmlSetMemDebug (FALSE, FALSE);

      

      PL/SQL procedure successfully completed.

XMLSETPARSEFLAG

Sets parsing options for this parser

These are sticky across parses using the same parser. utl_xml.xmlsetparseflag( ctx IN xmlctx, flag IN BINARY_INTEGER, value IN BOOLEAN);

TBD

XSLLOADFROMFILE

Load an XSL stylesheet from a BFILE into a CLOB utl_xml.xslloadfromfile( destLob IN CLOB, srcFile IN BFILE, amount IN BINARY_INTEGER);

TBD

XSLRESETPARAMS

Resets all parameters to their default values for the given XSL parser ctx utl_xml.xslresetparams(xslCtx IN xmlCtx);

TBD

XSLSETPARAM

set a parameter value for a stylesheet utl_xml.xslsetparam( xslCtx IN xmlCtx, paramName IN VARCHAR2, paramVal IN VARCHAR2);

TBD

XSLTRANSFORM

Transforms srcdoc into resdoc using the XSL stylesheet associated with xslCtx utl_xml.xsltransform( srcDoc IN CLOB, xslCtx IN xmlCtx, resDoc IN OUT NOCOPY CLOB);

TBD

XSLTRANSFORMCTOX

Transform a Clob to xmlCtx utl_xml.xxltransformctox( srcDoc IN CLOB, xslCtx IN xmlCtx) RETURN xmlCtx;

TBD

XSLTRANSFORMXTOC

Perform an XSL transformation on a pre-parsed xmlctx returning a CLOBU utl_xml.xxltransformxtoc( srcCtx IN xmlCtx, xslCtx IN xmlCtx, resDoc IN OUT NOCOPY CLOB);

TBD

XSLTRANSFORMXTOX

Transforms a pre-parsed xmlCtx returning another xmlCtx utl_xml.xsltransformxtox( srcCtx IN xmlCtx, xslCtx IN xmlCtx) RETURN xmlCtx;

TBD