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)