HTTPURIType

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

Query

IN -> Oracle_01.sql

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

Query

OUT -> Oracle_01.sql

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":1, "minor":0},"attributes":{"component":"oracle"}}'
	;

	CREATE OR REPLACE VIEW PUBLIC.httpuritype_table_view
	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

Query

IN -> Oracle_02.sql

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

Query

OUT -> Oracle_02.sql

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;

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