SnowConvert AI - 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;

Copy

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;

Copy

ESCAPEURI¶

Oracle¶

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

Copy

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;

Copy

UNESCAPEURI¶

Oracle¶

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

Copy

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;

Copy

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');

Copy

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');

Copy

UNREGISTERURLHANDLER¶

Oracle¶

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

Copy

Snowflake¶

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

Copy

Known Issues¶

1. Subprograms of URIFactory Package are not recognized

SnowConvert AI does not transform subprograms of built-in packages. The must 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.

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

Copy

Sample Source Patterns¶

XMLType in Create Table¶

Oracle¶

CREATE TABLE xml_table(
    xml_column XMLTYPE
);

Copy

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"}}'
    ;

Copy

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>')
);

Copy

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>')
);

Copy

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 by SnowConvert.

Related EWIs¶

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¶

Bemerkung

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

Copy

Sample Source Patterns¶

Bemerkung

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 ')
);

Copy

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 ***/!!!
);

Copy

Retrieving data from DBURIType column¶

Oracle¶

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

Copy

Result¶

DT.DB_URITYPE_COLUMN.GETCLOB()
¶ <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>

Copy

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;

Copy

Warnung

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 by SnowConvert AI or as not supported type if it uses the prefix SYS, there is a work item to fix this issue

Related EWIs¶

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

HTTPURIType¶

Bemerkung

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

Copy

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')
);

Copy

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 ***/!!!
);

Copy

Retrieving data from HTTPURIType column¶

Oracle¶

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

Copy

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;

Copy

Warnung

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 by SnowConvert AI or as not supported type if it uses the prefix SYS, there is a work item to fix this issue

Related EWIs¶

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

XDBURIType¶

Bemerkung

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

Copy

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')
);

Copy

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 ***/!!!
);

Copy

Retrieving data from XDBURIType column¶

Oracle¶

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

Copy

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>

Copy

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;

Copy

Warnung

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 by SnowConvert AI or as not supported type if it uses the prefix SYS, there is a work item to fix this issue

Related EWIs¶

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