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
INSERT INTO table_member_function_demo(column1) VALUES(type_member_function_demo(5));
Snowflake
INSERT INTO table_member_function_demo (column1)SELECT OBJECT_CONSTRUCT('a1', 5);
-- 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;
/
-- 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;
