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

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

Result

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

Snowflake

Query

SELECT

/*** MSC-WARNING - MSCEWI1049 - SYS.URIFACTORY.GETURI FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.SYS.URIFACTORY.GETURI_UDF('SYS.URIFACTORY.GETURI(\'http://localhost/\')').GETURL()
FROM dual;

ESCAPEURI

Oracle

Query

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

Result

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

Snowflake

Query

SELECT

/*** MSC-WARNING - MSCEWI1049 - SYS.URIFACTORY.ESCAPEURI FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.SYS.URIFACTORY.ESCAPEURI_UDF('SYS.URIFACTORY.ESCAPEURI(\'http://www.<->\')')
FROM dual;

UNESCAPEURI

Oracle

Query

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

SELECT

/*** MSC-WARNING - MSCEWI1049 - SYS.URIFACTORY.unescapeuri FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.SYS.URIFACTORY.unescapeuri_UDF('SYS.URIFACTORY.unescapeuri(\'http://www.%24-%26-%3C-%3E-%3F\')')
FROM dual;

REGISTERURLHANDLER

Oracle

Query

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

CREATE OR REPLACE TABLE PUBLIC.url_table ( urlcol VARCHAR(80));

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

-- ** MSC-ERROR - MSCEWI1057 - CREATE TYPE SUBTYPE VARIANT IS NOT SUPPORTED **
--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 PUBLIC.url_table VALUES ('SC://company1/company2=22/comp');

UNREGISTERURLHANDLER

Oracle

Query

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

Snowflake

Query

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. MSCEWI1057: Create Type Variant Not Supported

2. MSCEWI1049: Not recognized function.