DBURIType

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

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

CREATE OR REPLACE TABLE PUBLIC.dburitype_table (
db_uritype_column VARIANT /*** MSC-WARNING - MSCEWI1055 - REFERENCED CUSTOM TYPE 'DBURITYPE' NOT FOUND ***/,
-- ** MSC-ERROR - MSCEWI1028 - TYPE NOT SUPPORTED **
--sys_db_uritype_column SYS.VARIANT /*** MSC-WARNING - MSCEWI1055 - REFERENCED CUSTOM TYPE 'DBURITYPE' NOT FOUND ***/

INSERT INTO PUBLIC.dburitype_table (db_uritype_column) VALUES (/*** MSC-WARNING - MSCEWI1049 - dburitype.createUri FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.dburitype.createUri_UDF('dburitype.createUri(\'/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/FIRST_NAME \')'));

Retrieving data from DBURIType column

Oracle

Query

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

SELECT
/*** MSC-WARNING - MSCEWI1049 - dt.db_uritype_column.getclob FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.dt.db_uritype_column.getclob_UDF('dt.db_uritype_column.getclob()')
FROM PUBLIC.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. MSCEWI1028: Type is not supported.

2. MSCEWI1055: Referenced Custom Type not found.

3. MSCEWI1049: Not recognized function.