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

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

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.urowid_table (
urowid_column VARCHAR(18) /*** MSC-WARNING - MSCEWI1036 - UROWID DATA TYPE CONVERTED TO VARCHAR(18) ***/,
urowid_sized_column VARCHAR(18) /*** MSC-WARNING - MSCEWI1036 - UROWID DATA TYPE CONVERTED TO VARCHAR(18) ***/);

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

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

Result

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

Snowflake

Query

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

SELECT * FROM PUBLIC.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

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

SELECT
	rowid, 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. MSCEWI1036: Data type converted to another data type.