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

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

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

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

Related EWIs

  1. SSC-EWI-0036: Data type converted to another data type.

  2. 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()

¶ <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 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

  1. SSC-EWI-0028: Type not supported.

  2. SSC-EWI-0062: Custom type usage changed to variant

  3. 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 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

  1. SSC-EWI-0028: Type not supported.

  2. SSC-EWI-0062: Custom type usage changed to variant.

  3. 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 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

  1. SSC-EWI-0028: Type not supported.

  2. SSC-EWI-0062: Custom type usage changed to variant

  3. SSC-EWI-0073: Pending functional equivalence review