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

Query

IN -> Oracle_01.sql

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

Result

|SYS.URIFACTORY.GETURI('HTTP://LOCALHOST/').GETURL()|
|---------------------------------------------------|
|http://localhost/                                  |

Snowflake

Query

OUT -> Oracle_01.sql

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

Query

IN -> Oracle_02.sql

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

Result

|SYS.URIFACTORY.ESCAPEURI('HTTP://WWW.<->')|
|------------------------------------------|
|http://www.%3C-%3E                        |

Snowflake

Query

OUT -> Oracle_02.sql

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

UNESCAPEURI

Oracle

Query

IN -> Oracle_03.sql

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

Query

OUT -> Oracle_03.sql

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

REGISTERURLHANDLER

Oracle

Query

IN -> Oracle_04.sql

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

Query

OUT -> Oracle_04.sql

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

Query

IN -> Oracle_05.sql

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

Snowflake

Query

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.

Related EWIs

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