UROWID Data Type

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

|UROWID_COLUMN|UROWID_SIZED_COLUMN|
|-------------|-------------------|
|*BAMAAJMCVUv+|*BAMAAJMCVUv+      |

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

|UROWID_COLUMN|UROWID_SIZED_COLUMN|
|-------------|-------------------|
|*BAMAAJMCVUv+|*BAMAAJMCVUv+      |

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

|ROWID        |COUNTRY_NAME|
|-------------|------------|
|*BAMAAJMCQVL+|Argentina   |
|*BAMAAJMCQVX+|Australia   |
|*BAMAAJMCQkX+|Belgium     |
|*BAMAAJMCQlL+|Brazil      |
|*BAMAAJMCQ0H+|Canada      |
|*BAMAAJMCQ0j+|Switzerland |
|*BAMAAJMCQ07+|China       |
|*BAMAAJMCREX+|Germany     |
|*BAMAAJMCREv+|Denmark     |
|*BAMAAJMCRUf+|Egypt       |

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 **
    null,
    country_name
FROM
    hr.countries
FETCH NEXT 10 ROWS ONLY;

Result

SQL compilation error: invalid identifier 'ROWID'

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.