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)