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)
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
IN -> Oracle_02.sql
SELECT SYS.URIFACTORY.ESCAPEURI('http://www.<->') FROM dual;
SELECT!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.ESCAPEURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
''AS ESCAPEURIFROM dual;
UNESCAPEURI
Oracle
IN -> Oracle_03.sql
SELECT SYS.URIFACTORY.UNESCAPEURI('http://www.%24-%26-%3C-%3E-%3F') FROM dual;
SELECT!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.UNESCAPEURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
''AS UNESCAPEURIFROM dual;
REGISTERURLHANDLER
Oracle
IN -> Oracle_04.sql
CREATETABLEurl_table (urlcol varchar2(80));INSERT INTO url_table VALUES ('http://www.google.com/');CREATE OR REPLACETYPESCURIType UNDER SYS.URIType ( OVERRIDING MEMBER FUNCTION getClob RETURN CLOB, OVERRIDING MEMBER FUNCTION getBlob RETURN BLOB, OVERRIDING MEMBER FUNCTION getExternalURL RETURNVARCHAR2, OVERRIDING MEMBER FUNCTION getURI RETURNVARCHAR2,STATICFUNCTION createURI(urlINVARCHAR2) RETURN SCURIType);/CALL URIFACTORY.REGISTERURLHANDLER('sc://','HR','SCURITYPE');INSERT INTO url_table VALUES ('SC://company1/company2=22/comp');
Snowflake
OUT -> Oracle_04.sql
CREATE OR REPLACETABLEurl_table (urlcol VARCHAR(80))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;INSERT INTO url_tableVALUES ('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_tableVALUES ('SC://company1/company2=22/comp');
UNREGISTERURLHANDLER
Oracle
IN -> Oracle_05.sql
CALL URIFACTORY.UNREGISTERURLHANDLER('sc://');
Snowflake
OUT -> Oracle_05.sql
CALL URIFACTORY.UNREGISTERURLHANDLER('sc://');
Known Issues
1. Subprograms of URIFactory Package are not recognized
SnowConvert 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.