DBURIType

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

Check this section to set up the sample database.

DBURIType in create table

Oracle

Query

IN -> Oracle_01.sql

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

Query

OUT -> Oracle_01.sql

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

Query

IN -> Oracle_02.sql

SELECT dt.db_uritype_column.getclob() FROM dburitype_table dt;

Result

|DT.DB_URITYPE_COLUMN.GETCLOB()                          |
|--------------------------------------------------------|
|<?xml version="1.0"?>¶ <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

Query

OUT -> Oracle_02.sql

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;

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