Nested Table Array Type Definition
This is a translation reference to convert the Oracle Nested Table Array Declaration to Snowflake
This section is a work in progress, information may change in the future.
Description
In the database, a nested table is a column type that stores an unspecified number of rows in no particular order.
When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is
variable_name(index)
. The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.
For the translation, the type definition is replaced by an ARRAY Semi-structured Data Type and then its usages are changed accordingly across any operations. Please note how the translation for Nested Tables and Varrays are the same.
In order to define a Nested Table Array type, the syntax is as follows:
type_definition := TYPE IS TABLE OF datatype;
To declare a variable of this type:
variable_name collection_type;
Sample Source Patterns
Nested Table Array definitions
This illustrates how to create different nested table arrays, and how to migrate the definitions for the variables.
Oracle
CREATE OR REPLACE PROCEDURE nested_table_procedure
IS
TYPE nested_table_array_typ IS TABLE OF INTEGER;
TYPE nested_table_array_typ2 IS TABLE OF DATE;
nested_table_array nested_table_array_typ;
nested_table_array2 nested_table_array_typ2;
BEGIN
NULL;
END;
Snowflake
CREATE OR REPLACE PROCEDURE nested_table_procedure()
RETURNS INTEGER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
-- NO LONGER NEEDED
/*
TYPE associative_array_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(30);
TYPE associative_array_typ2 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
*/
associative_array ARRAY;
associative_array2 ARRAY;
BEGIN
NULL;
END;
$$;
Nested Table iteration
Oracle
CREATE OR REPLACE PROCEDURE nested_table_iteration
IS
TYPE nested_table_typ IS TABLE OF INTEGER;
nested_table_variable nested_table_typ := nested_table_typ (10, 20, 30);
BEGIN
FOR i IN 1..nested_table_variable.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(nested_table_variable(i));
END LOOP;
nested_table_variable (1) := 40;
FOR i IN 1..nested_table_variable.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(nested_table_variable(i));
END LOOP;
END;
/
CALL nested_table_iteration();
Snowflake
CREATE OR REPLACE PROCEDURE PUBLIC.nested_table_iteration()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
nested_table_variable ARRAY := ARRAY_CONSTRUCT(10, 20, 30);
BEGIN
FOR i IN 1 TO ARRAY_SIZE(nested_table_variable)
LOOP
CALL DBMS_OUTPUT.PUT_LINE(:nested_table_variable[:i-1]);
END LOOP;
nested_table_variable:= INSERT_REPLACE_COLLECTION_ELEMENT_UDF(nested_table_variable, 1, 40);
FOR i IN 1 TO ARRAY_SIZE(nested_table_variable)
LOOP
CALL DBMS_OUTPUT.PUT_LINE(:nested_table_variable[:i-1]);
END LOOP;
END;
$$;
CALL PUBLIC.nested_table_iteration();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;
Known Issues
1. They are currently not being converted
SnowConvert does not support translating these elements.
2. Indexing needs to be modified
Oracle's indexes start at 1, on Snowflake they will begin at 0.
Related EWIs
No EWIs related.
Last updated