SnowConvert AI - Oracle - COLLECTIONS AND RECORDS¶

CREATE OR REPLACE PROCEDURE associative_array
IS
    TYPE associate_array_typ IS TABLE OF INTEGER
        INDEX BY VARCHAR2(50);
        
    associate_array associate_array_typ := associate_array_typ();
    associate_index VARCHAR2(50);
BEGIN
    associate_array('abc') := 1;
    associate_array('bca') := 2;
    associate_array('def') := 3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array('abc'));
    associate_array('abc') := 4;
    --THROWS 'NO DATA FOUND'
    --DBMS_OUTPUT.PUT_LINE(associate_array('no exists'));
    
    DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
    
    associate_index := associate_array.FIRST;
    WHILE associate_index IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(associate_array(associate_index));
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;

CALL associative_array();

CREATE OR REPLACE PROCEDURE PUBLIC.associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index VARCHAR(50);
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, 'abc', 1, true);
      associate_array := OBJECT_INSERT(associate_array, 'bca', 2, true);
      associate_array := OBJECT_INSERT(associate_array, 'def', 3, true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['abc']);
      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['not found']);
      
      associate_array := OBJECT_INSERT(:associate_array, 'abc', 4, true);
    
      CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
      
      FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
      LOOP
         associate_index := OBJECT_KEYS(:associate_array)[:i-1];
         CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index]);
      END LOOP;
   END;
$$;

CALL PUBLIC.associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE PROCEDURE numeric_associative_array
IS
    TYPE numeric_associative_array_typ IS TABLE OF INTEGER
        INDEX BY PLS_INTEGER;
        
    associate_array numeric_associativ
    e_array_typ := numeric_associative_array_typ();
    associate_index PLS_INTEGER;
BEGIN
    associate_array(1) := -1;
    associate_array(2) := -2;
    associate_array(3) := -3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array(1));
    associate_array(1) := -4;
    
    DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
    
    associate_index := associate_array.FIRST;
    WHILE associate_index IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(associate_array(associate_index));
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;

CALL numeric_associative_array();

CREATE OR REPLACE PROCEDURE PUBLIC.numeric_associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index NUMBER;
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, '1', -1, true);
      associate_array := OBJECT_INSERT(associate_array, '2', -2, true);
      associate_array := OBJECT_INSERT(associate_array, '3', -3, true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']);
      
      associate_array := OBJECT_INSERT(:associate_array, '1', -4, true);
    
      CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
      
      FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
      LOOP
         associate_index := OBJECT_KEYS(:associate_array)[:i-1];
         CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index::VARCHAR]);
      END LOOP;
   END;
$$;

CALL PUBLIC.numeric_associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE PROCEDURE record_associative_array
IS
    TYPE record_typ IS RECORD(col1 INTEGER);
    TYPE record_associative_array_typ IS TABLE OF record_typ
        INDEX BY PLS_INTEGER;
        
    associate_array record_associati ve_array_typ := record_associative_array_typ();
    associate_index PLS_INTEGER;
BEGIN
    associate_array(1).col1 := -1;
    associate_array(2).col1 := -2;
    associate_array(3).col1 := -3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array(1).col1);
    associate_array(4).col1 := -4;
    
    DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
    
    associate_index := associate_array.FIRST;
    WHILE associate_index IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(associate_array(associate_index).col1);
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;
/

CALL record_associative_array();

CREATE OR REPLACE PROCEDURE PUBLIC.record_associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index NUMBER;
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -1, true), true);
      associate_array := OBJECT_INSERT(associate_array, '2', OBJECT_INSERT(NVL(associate_array['2'], OBJECT_CONSTRUCT()), 'col1', -2, true), true);
      associate_array := OBJECT_INSERT(associate_array, '3', OBJECT_INSERT(NVL(associate_array['3'], OBJECT_CONSTRUCT()), 'col1', -3, true), true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']:col1);
      
      associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -4, true), true);
    
      CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
      
      FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
      LOOP
         associate_index := OBJECT_KEYS(:associate_array)[:i-1];
         CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index::VARCHAR]:col1);
      END LOOP;
   END;
$$;

CALL PUBLIC.record_associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE PROCEDURE collection_count
IS
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1);
    varray_variable varray_typ := varray_typ(1, 2, 3);
    nt_variable nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    DBMS_OUTPUT.PUT_LINE(associative_array.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable.COUNT);
END;

CALL collection_count();

CREATE OR REPLACE PROCEDURE PUBLIC.collection_count()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array OBJECT := OBJECT_CONSTRUCT('abc', 1, 'bca', 1);
    varray_variable ARRAY := ARRAY_CONSTRUCT(1, 2, 3);
    nt_variable ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
BEGIN
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associative_array)));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(:varray_variable));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(:nt_variable));
END;
$$;

CALL PUBLIC.collection_count();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE PROCEDURE collection_exists
IS
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1);
    nt_variable nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    IF associative_array.EXISTS('abc')
    THEN DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF NOT associative_array.EXISTS('not found')
    THEN DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
    
    IF nt_variable.EXISTS(1)
    THEN DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF NOT nt_variable.EXISTS(5)
    THEN DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
END;
/

CALL collection_exists();

CREATE OR REPLACE PROCEDURE PUBLIC.collection_exists()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array OBJECT := OBJECT_CONSTRUCT('abc', 1, 'bca', 1);
    nt_variable ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
BEGIN
    IF (ARRAY_CONTAINS('abc'::VARIANT, OBJECT_KEYS(associative_array)))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF (NOT ARRAY_CONTAINS('not found'::VARIANT, OBJECT_KEYS(associative_array)))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
    
    IF (ARRAY_CONTAINS(1, nt_variable))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF (NOT ARRAY_CONTAINS(5, nt_variable))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
END;
$$;

CALL PUBLIC.collection_exists();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE PROCEDURE collection_first_last
IS
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1);
    nt_variable nt_typ := nt_typ();
BEGIN
    DBMS_OUTPUT.PUT_LINE(associative_array.FIRST);
    DBMS_OUTPUT.PUT_LINE(associative_array.LAST);
    
    DBMS_OUTPUT.PUT_LINE(nt_variable.FIRST);
    DBMS_OUTPUT.PUT_LINE(nt_variable.LAST);
    nt_variable := nt_typ(1, 2, 3, 4);
    DBMS_OUTPUT.PUT_LINE(nt_variable.FIRST);
    DBMS_OUTPUT.PUT_LINE(nt_variable.LAST);
END;
/

CALL collection_first_last();

CREATE OR REPLACE PROCEDURE PUBLIC.collection_first_last()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array OBJECT := OBJECT_CONSTRUCT('abc', 1, 'bca', 1);
    nt_variable ARRAY := ARRAY_CONSTRUCT();
BEGIN
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_FIRST(:associative_array));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_LAST(:associative_array));
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_FIRST(:nt_variable));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_LAST(:nt_variable));
    nt_variable := ARRAY_CONSTRUCT(1, 2, 3, 4);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_FIRST(:nt_variable));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_LAST(:nt_variable));
END;
$$;

CALL PUBLIC.collection_first_last();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE FUNCTION ARRAY_FIRST(array_variable VARIANT)
RETURNS VARIANT
LANGUAGE SQL
AS
$$
    IFF (IS_OBJECT(array_variable),
        ARRAY_FIRST(OBJECT_KEYS(array_variable)),
        IFF (ARRAY_SIZE(array_variable) = 0, null, array_variable[0]))
$$;

CREATE OR REPLACE FUNCTION ARRAY_LAST(array_variable VARIANT)
RETURNS VARIANT
LANGUAGE SQL
AS
$$
    IFF (IS_OBJECT(array_variable),
        ARRAY_LAST(OBJECT_KEYS(array_variable)),
        IFF (ARRAY_SIZE(array_variable) = 0, null, array_variable[ARRAY_SIZE(array_variable)-1]))
$$;

CREATE OR REPLACE PROCEDURE collection_delete
IS
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array1 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array2 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array3 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    
    varray_variable1 varray_typ := varray_typ(1, 2, 3, 4);
    
    nt_variable1 nt_typ := nt_typ(1, 2, 3, 4);
    nt_variable2 nt_typ := nt_typ(1, 2, 3, 4);
    nt_variable3 nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    varray_variable1.DELETE;--delete everything
    
    nt_variable1.DELETE;--delete everything
    nt_variable2.DELETE(2);--delete second position
    nt_variable3.DELETE(2, 3);--delete range
    
    associative_array1.DELETE;--delete everything
    associative_array2.DELETE('def');--delete second position
    associative_array3.DELETE('def', 'jkl');--delete range
    
    DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable2.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable3.COUNT);
    
    DBMS_OUTPUT.PUT_LINE(associative_array1.COUNT);
    DBMS_OUTPUT.PUT_LINE(associative_array2.COUNT);
    DBMS_OUTPUT.PUT_LINE(associative_array3.COUNT);
END;
/

CALL collection_delete();

CREATE OR REPLACE PROCEDURE PUBLIC.collection_delete()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array1 OBJECT := OBJECT_CONSTRUCT('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array2 OBJECT := OBJECT_CONSTRUCT('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array3 OBJECT := OBJECT_CONSTRUCT('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    
    varray_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    
    nt_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    nt_variable2 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    nt_variable3 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
BEGIN
    varray_variable1 := ARRAY_CONSTRUCT();--delete everything
    
    nt_variable1 := ARRAY_CONSTRUCT();--delete everything
    nt_variable2 := ARRAY_DELETE_UDF(nt_variable2, 2);--delete second position
    nt_variable3 := ARRAY_DELETE_UDF(nt_variable3, 2, 3);--delete range
    
    associative_array1 := OBJECT_CONSTRUCT();--delete everything
    associative_array2 := ASSOCIATIVE_ARRAY_DELETE_UDF('def');--delete second position
    associative_array3 := ASSOCIATIVE_ARRAY_DELETE_UDF('def', 'jkl');--delete range
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(varray_variable1));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable1);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable2);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable3);
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(associative_array1));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(associative_array2));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(associative_array3));
END;
$$;

CALL PUBLIC.collection_first_last();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE PROCEDURE collection_extend
IS 
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    
    nt_variable1 nt_typ := nt_typ(1, 2, 3, 4);
    varray_variable1 varray_typ := varray_typ(1, 2, 3);
    varray_variable2 varray_typ := varray_typ(1, 2, 3);
BEGIN
    nt_variable1.EXTEND;
    varray_variable1.EXTEND(2);
    varray_variable2.EXTEND(2, 1);
    
    DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable2.COUNT);
END;
/

CALL collection_extend();

CREATE OR REPLACE PROCEDURE PUBLIC.collection_first_last()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    nt_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    varray_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3);
    varray_variable2 ARRAY := ARRAY_CONSTRUCT(1, 2, 3);
BEGIN
    nt_variable1 := ARRAY_EXTEND_UDF(nt_variable);
    varray_variable1 := ARRAY_EXTEND_UDF(varray_variable1, 2);
    varray_variable2 := ARRAY_EXTEND_UDF(varray_variable2, 2, 1);
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable1);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(varray_variable1));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(varray_variable2));
END;
$$;

CALL PUBLIC.collection_first_last();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

CREATE OR REPLACE PROCEDURE collection_trim
IS 
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    
    varray_variable1 varray_typ := varray_typ(1, 2, 3);
    nt_variable1 nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    varray_variable1.TRIM;
    nt_variable1.TRIM(2);
    
    DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT);
END;
/

CALL collection_trim();

CREATE OR REPLACE PROCEDURE collection_limit
IS 
    TYPE varray_typ1 IS VARRAY(5) OF INTEGER;
    TYPE varray_typ2 IS VARRAY(6) OF INTEGER;
    
    varray_variable1 varray_typ1 := varray_typ1(1, 2, 3);
    varray_variable2 varray_typ2 := varray_typ2(1, 2, 3, 4);
BEGIN
    DBMS_OUTPUT.PUT_LINE(varray_variable1.LIMIT);
    DBMS_OUTPUT.PUT_LINE(varray_variable2.LIMIT);
END;
/

CALL collection_limit();

CREATE OR REPLACE PROCEDURE collection_prior_next
IS 
    TYPE varray_typ1 IS VARRAY(5) OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    varray_variable1 varray_typ1 := varray_typ1(-1, -2, -3);
    associative_array1 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
BEGIN
    DBMS_OUTPUT.PUT_LINE(varray_variable1.PRIOR(1));
    DBMS_OUTPUT.PUT_LINE(varray_variable1.PRIOR(2));
    DBMS_OUTPUT.PUT_LINE(varray_variable1.NEXT(2));
    DBMS_OUTPUT.PUT_LINE(varray_variable1.NEXT(3));
    
    DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('abc'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('def'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.NEXT('ghi'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.NEXT('jkl'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('not found'));
END;
/

CALL collection_prior_next();

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;

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

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();

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;

CREATE OR REPLACE FUNCTION PUBLIC.INSERT_REPLACE_COLLECTION_ELEMENT_UDF(varray ARRAY, position INTEGER, newValue VARIANT)
RETURNS ARRAY
LANGUAGE SQL
AS
$$
    ARRAY_CAT(
        ARRAY_APPEND(ARRAY_SLICE(varray, 0, (position)-1), newValue),
        ARRAY_SLICE(varray, position, ARRAY_SIZE(varray)))
$$;

CREATE TABLE record_table(col1 FLOAT, col2 INTEGER);

CREATE OR REPLACE PROCEDURE record_procedure
IS
    TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT);
    record_variable record_typ := record_typ(1, 1.5);--initialization
BEGIN
    INSERT INTO record_table(col1, col2)
        VALUES (record_variable.col2, record_variable.col1);--usage
        
    --reassignment of properties
    record_variable.col1 := 2;
    record_variable.col2 := 2.5;
    
    INSERT INTO record_table(col1, col2)
        VALUES (record_variable.col2, record_variable.col1);--usage
END;

CALL record_procedure();
SELECT * FROM record_table;

CREATE OR REPLACE TABLE record_table (col1 FLOAT,
    col2 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE record_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
        TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT);
        record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - record_typ DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT('COL1', 1, 'COL2', 1.5);--initialization

    BEGIN
        INSERT INTO record_table(col1, col2)
        SELECT
            :record_variable:COL2,
            :record_variable:COL1;--usage

        --reassignment of properties
        record_variable := OBJECT_INSERT(record_variable, 'COL1', 2, true);
        record_variable := OBJECT_INSERT(record_variable, 'COL2', 2.5, true);

        INSERT INTO record_table(col1, col2)
        SELECT
            :record_variable:COL2,
            :record_variable:COL1;--usage

    END;
$$;

CALL record_procedure();

SELECT * FROM
    record_table;

CREATE TABLE record_table(col1 INTEGER, col2 VARCHAR2(50), col3 DATE);
CREATE OR REPLACE PROCEDURE insert_record
IS
    record_variable record_table%ROWTYPE;
BEGIN
    record_variable.col1 := 1;
    record_variable.col2 := 'Hello';
    record_variable.col3 := DATE '2020-12-25';
    
    INSERT INTO record_table VALUES record_variable;
END;

CALL insert_record();
SELECT * FROM record_table;