ROWID DataType

Description

The rows in heap-organized tables that are native to Oracle Database have row addresses called rowids. You can examine a rowid row address by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the data type ROWID. You can also create tables and clusters that contain actual columns having the ROWID data type. (Oracle SQL Language Reference ROWID Data Types)

ROWID

Sample Source Patterns

ROWID in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE rowid_table 
(
    rowid_column ROWID
);

Snowflake

Query

OUT -> Oracle_01.sql

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

Insert data in the ROWID column

It is possible to insert data in ROWID columns if the insert has a valid ROWID, as shown in the example below. Unfortunately retrieving ROWID from a table is not allowed (see Related EWIs).

Oracle

Query

IN -> Oracle_02.sql

INSERT INTO rowid_table VALUES ('AAATtCAAMAAAADLABD');

SELECT rowid_column FROM rowid_table;

Result

|ROWID_COLUMN      |
|------------------|
|AAATtCAAMAAAADLABD|

Snowflake

Query

OUT -> Oracle_02.sql

INSERT INTO rowid_table
VALUES ('AAATtCAAMAAAADLABD');

SELECT rowid_column FROM
rowid_table;

Result

|ROWID_COLUMN      |
|------------------|
|AAATtCAAMAAAADLABD|

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 ROWID from a table that does not have an explicit column with this data type

As mentioned in the Snowflake forum, ROWID is not supported by Snowflake. The following query displays an error in Snowflake since hr.employees do not contain a ROWID column.

Oracle

Query

IN -> Oracle_03.sql

SELECT
    ROWID
FROM 
    hr.employees 
FETCH NEXT 10 ROWS ONLY;

Result

|ROWID             |
|------------------|
|AAATtCAAMAAAADLABD|
|AAATtCAAMAAAADLABV|
|AAATtCAAMAAAADLABX|
|AAATtCAAMAAAADLAAv|
|AAATtCAAMAAAADLAAV|
|AAATtCAAMAAAADLAAD|
|AAATtCAAMAAAADLABL|
|AAATtCAAMAAAADLAAP|
|AAATtCAAMAAAADLAA6|
|AAATtCAAMAAAADLABg|

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
FROM
    hr.employees
FETCH NEXT 10 ROWS ONLY;

Result

SQL compilation error: invalid identifier 'ROWID'

Related EWIs

1. SSC-EWI-0036: Data type converted to another data type.
2. SSC-FDM-OR0030: ROWID pseudocolumn is not supported in Snowflake.