Exporting Associative Arrays

You can export Oracle collections of associative arrays as collections of pg_variables using ora2pgpro. ora2pgpro converts calls to collection methods into calls to pg_variables functions emulating these methods.

This is an example of an Oracle package.

PACKAGE ASSOC IS
    TYPE NumList IS TABLE OF INTEGER INDEX BY PLS_INTEGER;

    n NumList := NumList();
    m NumList := NumList();

    PROCEDURE TestExists;
END;

PACKAGE BODY ASSOC IS

    PROCEDURE TestExists IS
        -- local variable with the same name as the global variable m
        m NumList := NumList();
    BEGIN
        -- make some data
        n(1) := 1 * 1;
        n(2 + 1) := 9 / 3;
        n(5) := 55;
        n(7) := 77;

        m(6) := 66;
        m(8) := 88;

        -- read the data
        dbms_output.put_line('Init values:');

        dbms_output.put_line('n(1) = ' || n(1));
        dbms_output.put_line('n(n.next(1)) = ' || n(n.next(1)));

        dbms_output.put_line('m(6) = ' || m(6));
        dbms_output.put_line('ASSOC.m(6) = ' || ASSOC.m(6));

        -- call the collections DELETE method
        n.DELETE(3);
        m.DELETE(6);

        dbms_output.put_line('Checks:');

        -- global n collection
        IF n.EXISTS(1) THEN
            dbms_output.put_line('OK, element n(1) exists.');
        END IF;
        IF n.EXISTS(3) = FALSE THEN
            dbms_output.put_line('OK, element n(3) has been deleted.');
        END IF;
        IF n.EXISTS(5) = TRUE THEN
            dbms_output.put_line('OK, element n(5) is in place.');
        END IF;
        IF n.EXISTS(99) = FALSE THEN
            dbms_output.put_line('OK, element n(99) does not exist at all.');
        END IF;

        -- local m collection
        IF m.EXISTS(6) = FALSE THEN
            dbms_output.put_line('OK, element m(6) has been deleted.');
        END IF;

        -- global m collection
        IF ASSOC.m.EXISTS(6) = TRUE THEN
            dbms_output.put_line('OK, element ASSOC.m(6) is in place.');
        END IF;

    END;
BEGIN
    -- put an element to the global m collection
    m(6) := 666;
END;

The resulting Postgres Pro package looks as follows.

BEGIN;
DROP SCHEMA IF EXISTS ASSOC CASCADE;
CREATE SCHEMA ASSOC;
    /*TYPE NumList IS TABLE OF INTEGER INDEX BY PLS_INTEGER;*/

    /*n NumList := NumList();*/
    /*m NumList := NumList();*/

CREATE OR REPLACE FUNCTION ASSOC.__INIT__() RETURNS VOID AS $$
#package
BEGIN
    -- put an element to the global m collection
    perform pgv_set_elem('ASSOC', 'm', 6, 666); /*m(6) := 666;*/
END;
$$ LANGUAGE plpgsql;

    CREATE OR REPLACE PROCEDURE ASSOC.TestExists() AS $$
    #package
    DECLARE
        -- local variable with the same name as the global variable m
        /*m NumList := NumList();*/
    BEGIN
        -- make some data
        perform pgv_set_elem('ASSOC', 'n', 1, 1 * 1); /*n(1) := 1 * 1;*/
        perform pgv_set_elem('ASSOC', 'n', 2 + 1, 9 / 3); /*n(2 + 1) := 9 / 3;*/
        perform pgv_set_elem('ASSOC', 'n', 5, 55); /*n(5) := 55;*/
        perform pgv_set_elem('ASSOC', 'n', 7, 77); /*n(7) := 77;*/

        perform pgv_set_elem('ASSOC', 'TESTEXISTS.m', 6, 66); /*m(6) := 66;*/
        perform pgv_set_elem('ASSOC', 'TESTEXISTS.m', 8, 88); /*m(8) := 88;*/

        -- read the data
        CALL dbms_output.put_line('Init values:');

        CALL dbms_output.put_line('n(1) = ' || pgv_get_elem('ASSOC', 'n', 1, NULL::INTEGER));
        CALL dbms_output.put_line('n(n.next(1)) = ' || pgv_get_elem('ASSOC', 'n', pgv_next('ASSOC', 'n', 1), NULL::INTEGER));

        CALL dbms_output.put_line('m(6) = ' || pgv_get_elem('ASSOC', 'TESTEXISTS.m', 6, NULL::INTEGER));
        CALL dbms_output.put_line('ASSOC.m(6) = ' || pgv_get_elem('ASSOC', 'm', 6, NULL::INTEGER));

        -- call the collections DELETE method
        PERFORM pgv_remove_elem('ASSOC', 'n', 3);
        PERFORM pgv_remove_elem('ASSOC', 'TESTEXISTS.m', 6);

        CALL dbms_output.put_line('Checks:');

        -- global n collection
        IF pgv_exists_elem('ASSOC', 'n', 1) THEN
            CALL dbms_output.put_line('OK, element n(1) exists.');
        END IF;
        IF pgv_exists_elem('ASSOC', 'n', 3) = FALSE THEN
            CALL dbms_output.put_line('OK, element n(3) has been deleted.');
        END IF;
        IF pgv_exists_elem('ASSOC', 'n', 5) = TRUE THEN
            CALL dbms_output.put_line('OK, element n(5) is in place.');
        END IF;
        IF pgv_exists_elem('ASSOC', 'n', 99) = FALSE THEN
            CALL dbms_output.put_line('OK, element n(99) does not exist at all.');
        END IF;

        -- local m collection
        IF pgv_exists_elem('ASSOC', 'TESTEXISTS.m', 6) = FALSE THEN
            CALL dbms_output.put_line('OK, element m(6) has been deleted.');
        END IF;

        -- global m collection
        IF pgv_exists_elem('ASSOC', 'm', 6) = TRUE THEN
            CALL dbms_output.put_line('OK, element ASSOC.m(6) is in place.');
        END IF;

    END; $$ LANGUAGE PLPGSQL;
/*END;*/

COMMIT;

Note that in the above package you have to replace all the calls CALL dbms_output to PERFORM dbms_output.

Now let's look at the result of calling the testexists procedure in Oracle:

call assoc.testexists();
/

Init values:
n(1) = 1
n(n.next(1)) = 3
m(6) = 66
ASSOC.m(6) = 666
Checks:
OK, element n(1) exists.
OK, element n(3) has been deleted.
OK, element n(5) is in place.
OK, element n(99) does not exist at all.
OK, element m(6) has been deleted.
OK, element ASSOC.m(6) is in place.

And compare it with the corresponding result in Postgres Pro:

test=# do $$begin perform dbms_output.enable(); call  ASSOC.TestExists(); end;$$ language plpgsql; select unnest(lines) from dbms_output.get_lines(15);
DO
                  unnest
------------------------------------------
 Init values:
 n(1) = 1
 n(n.next(1)) = 3
 m(6) = 66
 ASSOC.m(6) = 666
 Checks:
 OK, element n(1) exists.
 OK, element n(3) has been deleted.
 OK, element n(5) is in place.
 OK, element n(99) does not exist at all.
 OK, element m(6) has been deleted.
 OK, element ASSOC.m(6) is in place.
(12 rows)
pdf