Oracle - XML Types¶

Description¶

Extensible Markup Language (XML) is a standard format developed by the World Wide Web Consortium (W3C) for representing structured and unstructured data on the World Wide Web. Universal resource identifiers (URIs) identify resources such as Web pages anywhere on the Web. Oracle provides types to handle XML and URI data, as well as a class of URIs called DBURIRef types to access data stored within the database itself. (Oracle SQL Language Reference XML Types)

URIFactory Package¶

Description¶

Oracle also provides the URIFactory package, which can create and return instances of the various subtypes of the URITypes. The package analyzes the URL string, identifies the type of URL (HTTP, DBURI, and so on), and creates an instance of the subtype. (Oracle SQL Language Reference URIFactory Package)

URIFactory contains the following subprograms:

GETURI
ESCAPEURI
UNESCAPURI
REGISTERURLHANDLER
UNREGISTERURLHANDLER

GETURI¶

Oracle¶

SELECT SYS.URIFACTORY.GETURI('http://localhost/').GETURL() FROM dual;

Result¶

SYS.URIFACTORY.GETURI(‘HTTP://LOCALHOST’).GETURL()
http://localhost/

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.GETURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS GETURI.GETURL() FROM dual;

ESCAPEURI¶

Oracle¶

SELECT SYS.URIFACTORY.ESCAPEURI('http://www.<->') FROM dual;

Result¶

SYS.URIFACTORY.ESCAPEURI(‘HTTP://WWW.<->’)
`http://www.%3C-%3E`

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.ESCAPEURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS ESCAPEURI
FROM dual;

UNESCAPEURI¶

Oracle¶

SELECT SYS.URIFACTORY.UNESCAPEURI('http://www.%24-%26-%3C-%3E-%3F') FROM dual;

Result¶

SYS.URIFACTORY.UNESCAPEURI(`'HTTP://WWW.%24-%26-%3C-%3E-%3F'`)
http://www.$-&-<->-?

Snowflake¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.UNESCAPEURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS UNESCAPEURI
FROM dual;

REGISTERURLHANDLER¶

Oracle¶

CREATE TABLE url_table (urlcol varchar2(80));
INSERT INTO url_table VALUES ('http://www.google.com/');

CREATE OR REPLACE TYPE SCURIType UNDER SYS.URIType (
  OVERRIDING MEMBER FUNCTION getClob RETURN CLOB,
  OVERRIDING MEMBER FUNCTION getBlob RETURN BLOB,
  OVERRIDING MEMBER FUNCTION getExternalURL RETURN VARCHAR2,
  OVERRIDING MEMBER FUNCTION getURI RETURN VARCHAR2,
  STATIC FUNCTION createURI(url IN VARCHAR2) RETURN SCURIType);
/

CALL URIFACTORY.REGISTERURLHANDLER('sc://','HR','SCURITYPE');

INSERT INTO url_table VALUES ('SC://company1/company2=22/comp');

Snowflake¶

CREATE OR REPLACE TABLE url_table (urlcol VARCHAR(80))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO url_table
VALUES ('http://www.google.com/');

--!!!RESOLVE EWI!!! /*** SSC-EWI-OR0007 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!

--CREATE OR REPLACE TYPE SCURIType UNDER SYS.URIType (
--  OVERRIDING MEMBER FUNCTION getClob RETURN CLOB,
--  OVERRIDING MEMBER FUNCTION getBlob RETURN BLOB,
--  OVERRIDING MEMBER FUNCTION getExternalURL RETURN VARCHAR2,
--  OVERRIDING MEMBER FUNCTION getURI RETURN VARCHAR2,
--  STATIC FUNCTION createURI(url IN VARCHAR2) RETURN SCURIType)
                                                              ;

CALL URIFACTORY.REGISTERURLHANDLER('sc://','HR','SCURITYPE');

INSERT INTO url_table
VALUES ('SC://company1/company2=22/comp');

UNREGISTERURLHANDLER¶

Oracle¶

CALL URIFACTORY.UNREGISTERURLHANDLER('sc://');

Snowflake¶

CALL URIFACTORY.UNREGISTERURLHANDLER('sc://');

Known Issues¶

1. Subprograms of URIFactory Package are not recognized

Subprograms of built-in packages are not transformed. Most of the functionality of URI types is not currently supported by Snowflake.

2. Missing EWIs for URIFactory Package

The output code should display an EWI indicating that some functionality is not supported by Snowflake. There is a work item to fix this issue.

SSC-EWI-OR0007: Create Type Not Supported in Snowflake.
SSC-EWI-OR0076: Built In Package Not Supported.

XMLType¶

Description¶

This Oracle-supplied type can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. (Oracle SQL Language Reference XML Data Type)

Snowflake handles semi-structured data types (including XMLTYPE) using the VARIANT data type, for this reason, XMLTYPEs are to be migrated to VARIANT, and then usages of functions used to manipulate and query XML must be migrated to Snowflake’s counterparts. For more information on how to use XML in Snowflake, please refer to this post in the Snowflake forum and the TO_XML function documentation in Snowflake.

XMLTYPE

Sample Source Patterns¶

XMLType in Create Table¶

Oracle¶

CREATE TABLE xml_table(
    xml_column XMLTYPE
);

Snowflake¶

CREATE OR REPLACE TABLE xml_table (
        xml_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XMLTYPE DATA TYPE CONVERTED TO VARIANT ***/!!!
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

Insert data in the XML column¶

Oracle¶

INSERT INTO xml_table VALUES(
    XMLType(
'<?xml version="1.0"?>
<note>
  <to>SnowConvert AI</to>
  <from>Oracle</from>
  <heading>Greeting</heading>
  <body>Hello there!</body>
</note>')
);

Snowflake¶

INSERT INTO xml_table
VALUES(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0016 - FUNCTION RELATED WITH XML NOT SUPPORTED ***/!!!
    XMLType(
'<?xml version="1.0"?>
<note>
  <to>SnowConvert AI</to>
  <from>Oracle</from>
  <heading>Greeting</heading>
  <body>Hello there!</body>
</note>')
);

Known Issues¶

1. XMLType manipulation and query functions are not recognized¶

The functions for manipulating and querying XML such as XMLTYPE() are not being recognized nor transformed.

SSC-EWI-0036: Data type converted to another data type.
SSC-EWI-OR0016: XML is not supported.

URI Data Types¶

Description¶

Oracle supplies a family of URI types—URIType, DBURIType, XDBURIType, and HTTPURIType—which are related by an inheritance hierarchy. URIType is an object type and the others are subtypes of URIType. (Oracle SQL Language Reference URI Data Types)

DBURIType¶

Note

Some parts in the output code are omitted for clarity reasons.

Description¶

DBURIType can be used to store DBURIRef values, which reference data inside the database. Storing DBURIRef values lets you reference data stored inside or outside the database and access the data consistently. (Oracle SQL Language Reference URI Data Types)

DBURIType

Sample Source Patterns¶

Note

Check this section to set up the sample database.

DBURIType in create table¶

Oracle¶

CREATE TABLE dburitype_table(
    db_uritype_column DBURITYPE,
    sys_db_uritype_column SYS.DBURITYPE
);

INSERT INTO dburitype_table (db_uritype_column) VALUES (
    dburitype.createUri('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/FIRST_NAME ')
);

Snowflake¶

CREATE OR REPLACE TABLE dburitype_table (
        db_uritype_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'DBURITYPE' USAGE CHANGED TO VARIANT ***/!!!,
        !!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
        sys_db_uritype_column SYS.DBURITYPE
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

    CREATE OR REPLACE VIEW PUBLIC.dburitype_table_view
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
    AS
    SELECT
        db_uritype_column,
        sys_db_uritype_column
    FROM
        dburitype_table;

        INSERT INTO dburitype_table(db_uritype_column) VALUES (
    dburitype.createUri('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/FIRST_NAME ') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'dburitype.createUri' NODE ***/!!!
);

Retrieving data from DBURIType column¶

Oracle¶

SELECT dt.db_uritype_column.getclob() FROM dburitype_table dt;

Result¶

DT.DB_URITYPE_COLUMN.GETCLOB()
<?xml version=”1.0”?>¶ <FIRST_NAME>Shelley</FIRST_NAME>¶

This result query has XML syntax, this is how it is displayed:

<?xml version="1.0"?>
 <FIRST_NAME>Shelley</FIRST_NAME>

Snowflake¶

SELECT dt.db_uritype_column.getclob() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'dt.db_uritype_column.getclob' NODE ***/!!! FROM
dburitype_table dt;

Warning

getclob function is not being transformed by the tool, but is necessary to display the data in Oracle, this transformation is going to be available in future releases.

Known Issues¶

1. DBURIType Data Type not recognized

DBURIType is parsed and converted as Custom Data Type or as not supported type if it uses the prefix SYS, there is a work item to fix this issue

SSC-EWI-0028: Type not supported.
SSC-EWI-0062: Custom type usage changed to variant
SSC-EWI-0073: Pending functional equivalence review

HTTPURIType¶

Note

Some parts in the output code are omitted for clarity reasons.

Description¶

You can use HTTPURIType to store URLs to external Web pages or to files. Oracle accesses these files using HTTP (Hypertext Transfer Protocol). (Oracle SQL Language Reference URI Data Types)

HTTPURITYPE

Sample Source Patterns¶

HTTPURIType in create table¶

Oracle¶

CREATE TABLE httpuritype_table(
    http_uritype_column HTTPURITYPE,
    sys_http_uritype_column SYS.HTTPURITYPE
);

INSERT INTO httpuritype_table (http_uritype_column) VALUES(
    HTTPURITYPE.createuri('http://localhost/')
);
INSERT INTO httpuritype_table (http_uritype_column) VALUES(
    HTTPURITYPE.createuri('www.google.com')
);

Snowflake¶

CREATE OR REPLACE TABLE httpuritype_table (
	    http_uritype_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'HTTPURITYPE' USAGE CHANGED TO VARIANT ***/!!!,
	    !!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
	    sys_http_uritype_column SYS.HTTPURITYPE
	)
	COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
	;

	CREATE OR REPLACE VIEW PUBLIC.httpuritype_table_view
	COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
	AS
	SELECT
	    http_uritype_column,
	    sys_http_uritype_column
	FROM
	    httpuritype_table;

	    INSERT INTO httpuritype_table(http_uritype_column) VALUES(
    HTTPURITYPE.createuri('http://localhost/') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'HTTPURITYPE.createuri' NODE ***/!!!
);

	    INSERT INTO httpuritype_table(http_uritype_column) VALUES(
    HTTPURITYPE.createuri('www.google.com') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'HTTPURITYPE.createuri' NODE ***/!!!
);

Retrieving data from HTTPURIType column¶

Oracle¶

SELECT
	ut.http_uritype_column.getUrl(),
	ut.http_uritype_column.getExternalUrl()
FROM
	httpuritype_table ut;

Result¶

UT.HTTP_URITYPE_COLUMN.GETURL()	UT.HTTP_URITYPE_COLUMN.GETEXTERNALURL()
http://localhost/	http://localhost/
http://www.google.com	http://www.google.com

Snowflake¶

SELECT
	ut.http_uritype_column.getUrl() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ut.http_uritype_column.getUrl' NODE ***/!!!,
	ut.http_uritype_column.getExternalUrl() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ut.http_uritype_column.getExternalUrl' NODE ***/!!!
FROM
	httpuritype_table ut;

Warning

getUrl and getExternalUrl functions are not being transformed by the tool, but are necessary to display the data in Oracle, this transformation is going to be available in future releases.

Known Issues¶

1. HTTPURIType Data Type not recognized

HTTPURIType is parsed and converted as Custom Data Type or as not supported type if it uses the prefix SYS, there is a work item to fix this issue

SSC-EWI-0028: Type not supported.
SSC-EWI-0062: Custom type usage changed to variant.
SSC-EWI-0073: Pending functional equivalence review.

XDBURIType¶

Note

Some parts in the output code are omitted for clarity reasons.

Description¶

You can use XDBURIType to expose documents in the XML database hierarchy as URIs that can be embedded in any URIType column in a table. The XDBURIType consists of a URL, which comprises the hierarchical name of the XML document to which it refers and an optional fragment representing the XPath syntax. (Oracle SQL Language Reference URI Data Types)

XDBURITYPE

Sample Source Patterns¶

XDBURIType in create table¶

Oracle¶

CREATE TABLE xdburitype_table(
    xdb_uritype_column XDBURITYPE,
    sys_xdb_uritype_column SYS.XDBURITYPE
);

INSERT INTO xdburitype_table (xdb_uritype_column) VALUES(
    xdburitype('/home/OE/employees/emp_selby.xml')
);

Snowflake¶

CREATE OR REPLACE TABLE xdburitype_table (
        xdb_uritype_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'XDBURITYPE' USAGE CHANGED TO VARIANT ***/!!!,
        !!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
        sys_xdb_uritype_column SYS.XDBURITYPE
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

    CREATE OR REPLACE VIEW PUBLIC.xdburitype_table_view
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
    AS
    SELECT
        xdb_uritype_column,
        sys_xdb_uritype_column
    FROM
        xdburitype_table;

        INSERT INTO xdburitype_table(xdb_uritype_column) VALUES(
    xdburitype('/home/OE/employees/emp_selby.xml') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'xdburitype' NODE ***/!!!
);

Retrieving data from XDBURIType column¶

Oracle¶

SELECT ut.xdb_uritype_column.getclob() FROM xdburitype_table ut;

Result¶

UT.XDB_URITYPE_COLUMN.GETCLOB()
<emp_name>selby</emp_name>

This result query has XML syntax, this is how it is displayed:

<emp_name>selby</emp_name>

Snowflake¶

SELECT ut.xdb_uritype_column.getclob() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ut.xdb_uritype_column.getclob' NODE ***/!!! FROM
xdburitype_table ut;

Warning

getclob function is not being transformed by the tool, but is necessary to display the data in Oracle, this transformation is going to be available in future releases.

Known Issues¶

1. XDBURIType Data Type not recognized

XDBURIType is parsed and converted as Custom Data Type or as not supported type if it uses the prefix SYS, there is a work item to fix this issue

SSC-EWI-0028: Type not supported.
SSC-EWI-0062: Custom type usage changed to variant
SSC-EWI-0073: Pending functional equivalence review

Oracle - XML Types¶

Description¶

URIFactory Package¶

Description¶

GETURI¶

Oracle¶

Result¶

Snowflake¶

ESCAPEURI¶

Oracle¶

Result¶

Snowflake¶

UNESCAPEURI¶

Oracle¶

Result¶

Snowflake¶

REGISTERURLHANDLER¶

Oracle¶

Snowflake¶

UNREGISTERURLHANDLER¶

Oracle¶

Snowflake¶

Known Issues¶

Related EWIs¶

XMLType¶

Description¶

Sample Source Patterns¶

XMLType in Create Table¶

Oracle¶

Snowflake¶

Insert data in the XML column¶

Oracle¶

Snowflake¶

Known Issues¶

1. XMLType manipulation and query functions are not recognized¶

Related EWIs¶

URI Data Types¶

Description¶

DBURIType¶

Description¶

Sample Source Patterns¶

DBURIType in create table¶

Oracle¶

Snowflake¶

Retrieving data from DBURIType column¶

Oracle¶

Result¶

Snowflake¶

Known Issues¶

Related EWIs¶

HTTPURIType¶

Description¶

Sample Source Patterns¶

HTTPURIType in create table¶

Oracle¶

Snowflake¶

Retrieving data from HTTPURIType column¶

Oracle¶

Result¶

Snowflake¶

Known Issues¶

Related EWIs¶

XDBURIType¶

Description¶

Sample Source Patterns¶

XDBURIType in create table¶

Oracle¶

Snowflake¶

Retrieving data from XDBURIType column¶

Oracle¶

Result¶

Snowflake¶

Known Issues¶

Related EWIs¶