Member Function Definitions

This is a translation reference to convert the Member Functions of the Oracle Create Type Statements (UDT's) to Snowflake

SnowConvert still does not recognize type member functions nor type body definitions. This page is only used as a future reference for translation.

Some parts in the output code are omitted for clarity reasons.

Description

Like other Class definitions, Oracle's TYPE can implement methods to expose behaviors based on its attributes. MEMBER FUCTION will be transformed to Snowflake's Stored Procedures, to maintain functional equivalence due to limitations.

Since functions are being transformed into procedures, the transformation reference for PL/SQL also applies here.

Sample Source Patterns

Inserts for Simple square() member function

The next data will be inserted inside the table before querying the select. Please note these Inserts currently need to be manually migrated into Snowflake.

Oracle

Query

INSERT INTO table_member_function_demo(column1) VALUES
(type_member_function_demo(5));

Snowflake

Query

INSERT INTO table_member_function_demo (column1)
SELECT OBJECT_CONSTRUCT('a1', 5);

Simple square() member function

Oracle

Query

IN -> Oracle_01.sql

-- TYPE DECLARATION
CREATE TYPE type_member_function_demo AS OBJECT (
    a1 NUMBER,
    MEMBER FUNCTION get_square RETURN NUMBER
);
/

-- TYPE BODY DECLARATION
CREATE TYPE BODY type_member_function_demo IS
   MEMBER FUNCTION get_square
   RETURN NUMBER
   IS x NUMBER;
   BEGIN
      SELECT c.column1.a1*c.column1.a1 INTO x
      FROM table_member_function_demo c;
      RETURN (x);
   END;
END;
/

-- TABLE
CREATE TABLE table_member_function_demo (column1 type_member_function_demo);
/

-- QUERYING DATA
SELECT
    t.column1.get_square()
FROM
    table_member_function_demo t;
/

Results

T.COLUMN1.GET_SQUARE()|
----------------------+
                    25|

Snowflake

Query

OUT -> Oracle_01.sql

-- TYPE DECLARATION
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE type_member_function_demo AS OBJECT (
    a1 NUMBER,
    MEMBER FUNCTION get_square RETURN NUMBER
)
;

---- TYPE BODY DECLARATION
----** SSC-FDM-OR0039 - CREATE TYPE WITHOUT BODY IS NOT SUPPORTED IN SNOWFLAKE **
--CREATE TYPE BODY type_member_function_demo IS
--   MEMBER FUNCTION get_square
--   RETURN NUMBER
--   IS x NUMBER;
--   BEGIN
--      SELECT c.column1.a1*c.column1.a1 INTO x
--      FROM table_member_function_demo c;
--      RETURN (x);
--   END;
--END
   ;

-- TABLE
CREATE OR REPLACE TABLE table_member_function_demo (column1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'type_member_function_demo' USAGE CHANGED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE VIEW PUBLIC.table_member_function_demo_view
AS
SELECT
    column1:a1 :: NUMBER AS a1
FROM
    table_member_function_demo;

-- QUERYING DATA
SELECT
    t.column1.get_square() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 't.column1.get_square' NODE ***/!!!
FROM
    table_member_function_demo t;

Results

GET_SQUARE()|
------------+
          25|

Known Issues

No Known issues.

Related EWIs

1. SSC-EWI-0056: Create Type Not Supported.

2. SSC-EWI-0062: Custom type usage changed to variant.

3. SSC-EWI-0073: Pending Functional Equivalence Review.

4. SSC-FDM-OR0039: The cycle clause is not supported in Snowflake.