UROWID Data Type

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

Oracle uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids.(Oracle SQL Language Reference UROWID Data Type)

UROWID [(size)]

Sample Source Patterns

UROWID in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE urowid_table 
(
    urowid_column UROWID,
    urowid_sized_column UROWID(40)
);

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE urowid_table
    (
        urowid_column VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - UROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!,
        urowid_sized_column VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - UROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

Insert data in the UROWID column

Just like ROWID, it is possible to insert data in UROWID columns if the insert has a valid UROWID, but retrieving from a table is not allowed (see Related EWIs).

Oracle

Query

IN -> Oracle_02.sql

INSERT INTO urowid_table VALUES ('*BAMAAJMCVUv+','*BAMAAJMCVUv+');

SELECT * FROM urowid_table;

Result

Snowflake** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "urowid_table" **

Query

OUT -> Oracle_02.sql

INSERT INTO urowid_table
VALUES ('*BAMAAJMCVUv+','*BAMAAJMCVUv+');

SELECT * FROM
urowid_table;

Result

Known Issues

Since the result set is too large, Row Limiting Clause was added. You can remove this clause to retrieve the entire result set.

1. Retrieving UROWID from a table that does not have an explicit column with this data type

The following query displays an error in Snowflake since hr.countries do not contain a ROWID (as mentioned in Oracle's documentation UROWID is accessed with SELECT ... ROWID statement) column.

Oracle

Query

IN -> Oracle_03.sql

SELECT
    rowid,
    country_name
FROM
    hr.countries FETCH NEXT 10 ROWS ONLY;

Result

Snowflake

Query

OUT -> Oracle_03.sql

SELECT
        --** SSC-FDM-OR0030 - ROWID PSEUDOCOLUMN IS NOT SUPPORTED IN SNOWFLAKE, IT WAS CONVERTED TO NULL TO AVOID RUNTIME ERRORS **
        '' AS rowid,
        country_name
FROM
        hr.countries
FETCH NEXT 10 ROWS ONLY;

Result

2. EWI should be displayed by SnowConvert

EWI should be displayed when trying to select UROWID column. There is a work item to add the corresponding EWI.

This issue has been marked as critical and will be fixed in the upcoming releases.

Related EWIs

1. SSC-EWI-0036: Data type converted to another data type.

2. SSC-FDM-OR0030: ROWID pseudocolumn is not supported in Snowflake.