REF Data Types

Description

An object identifier (represented by the keyword OID) uniquely identifies an object and enables you to reference the object from other objects or relational tables. A data type category called REF represents such references. A REF data type is a container for an object identifier. REF values are pointers to objects. (Oracle SQL Language Reference REF Data Types)

REF Data types are not supported in Snowflake, and there is no current workaround to implement a similar component.

As of now, they are currently being recognized as user-defined functions and "DANGLING" clauses are not being recognized. Finally, the OID clause in view is being removed, as there is no workaround for them.

CREATE VIEW generic_view AS
SELECT REF(type) AS ref_col, MAKE_REF(type, identifier_column) AS make_ref_col
FROM generic_table;

SELECT v.ref_col, v.make_ref_col
FROM generic_view v
WHERE v.ref_col IS NOT DANGLING AND v.make_ref_col IS NOT DANGLING

Sample Source Patterns

Types and Tables for References

Please consider the following types, tables, inserts and view. They will be used for the next pattern section.

Oracle

Query

CREATE TYPE email_typ_demo AS OBJECT
	( email_id INTEGER
	, email VARCHAR2(30)
	);

CREATE TYPE customer_typ_demo AS OBJECT
    ( customer_id        INTEGER
    , cust_first_name    VARCHAR2(20)
    , cust_last_name     VARCHAR2(20)
    , email_id			 INTEGER
    ) ;

CREATE TABLE email_table_demo OF email_typ_demo;
CREATE TABLE customer_table_demo OF customer_typ_demo;

INSERT INTO customer_table_demo VALUES
(customer_typ_demo(1, 'First Name 1', 'Last Name 1', 1));

INSERT INTO customer_table_demo VALUES
(customer_typ_demo(2, 'First Name 2', 'Last Name 2', 2));

INSERT INTO email_table_demo VALUES 
(email_typ_demo(1, '[e-mail]'));

CREATE VIEW email_object_view OF email_typ_demo WITH OBJECT IDENTIFIER (email_id) AS
SELECT * FROM email_table_demo;

Selects and Views using REFs

Oracle

Query

CREATE VIEW email_object_view OF email_typ_demo WITH OBJECT IDENTIFIER (email_id) AS
SELECT * FROM email_table_demo;

CREATE VIEW customer_view AS
SELECT REF(ctb) AS customer_reference
     , MAKE_REF(email_object_view, ctb.email_id) AS email_ref
FROM customer_table_demo ctb;

SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM customer_view c;

SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM customer_view c
WHERE c.email_ref IS NOT DANGLING;

Result with danglings

CUSTOMER_REFERENCE.CUST_FIRST_NAME|EMAIL_REF.EMAIL|
----------------------------------+---------------+
First Name 1                      |[e-mail]    |
First Name 2                      |               |

Result with no danglings

CUSTOMER_REFERENCE.CUST_FIRST_NAME|EMAIL_REF.EMAIL|
----------------------------------+---------------+
First Name 1                      |[e-mail]    |

Snowflake

Query

CREATE OR REPLACE VIEW PUBLIC.email_object_view
AS
SELECT * FROM PUBLIC.email_table_demo;

CREATE OR REPLACE VIEW PUBLIC.customer_view
AS
SELECT
/*** MSC-WARNING - MSCEWI1049 - REF FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.REF_UDF('REF(ctb)') AS customer_reference,
/*** MSC-WARNING - MSCEWI1049 - MAKE_REF FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.MAKE_REF_UDF('MAKE_REF(email_object_view, ctb.email_id)') AS email_ref
FROM PUBLIC.customer_table_demo ctb;

SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM PUBLIC.customer_view c;

SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM PUBLIC.customer_view c
WHERE c.email_ref;
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 20 OF THE SOURCE CODE. **
--                   IS NOT DANGLING

Known Issues

1. REF and MAKE_REF are not being recognized

Instead they are currently being marked as user-defined functions.

2. DANGLING clause is not being recognized

DANGLING clauses are causing parsing errors when running SnowConvert.

3. OID Clauses in view are not supported by SnowConvert, but there is no EWI related to them

The OID clause is not supported by either SnowConvert, nor Snowflake but there should be an EWI related to them.

Related EWIs

1. MSCEWI1001: Unrecognized token on the line of the source code.

2. MSCEWI1049: Not recognized function.