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.

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

-- 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

-- 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;
--/

-- STORED PROCEDURE DECLARATION
CREATE OR REPLACE FUNCTION get_square()
RETURNS NUMBER
AS
$$
SELECT c.column1:a1::NUMBER * c.column1:a1::NUMBER FROM table_member_function_demo c
$$;

-- TABLE
CREATE TABLE table_member_function_demo
(column1 VARIANT);

-- QUERYING DATA
SELECT get_square()

Results

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

Known Issues

No Known issues.

Related EWIs

No related EWIs.