Wondering if anyone can help with this - Database 9.2.0.4.
We have some legacy code that, among other things, has an array of id's and is looking to arrange them into the minimum number of consistent groups. Consistency can be determined by calling a function IS_MATCH (id(x), id(y)).
It ends up doing this over and over again as it loops round and round, and as a first cut to improve times on this core piece of code I'd like to hold the results in a two-dimensional array so I can just do a look-up on subsequent checks rather than call the function. To minimise disruption I just plan to amend the code as:
when I'm about to call the function I check if the result is in the array. If it is, I use that value and continue. If not, I call the function, put the result in the array, and continue.
However, I can't figure out how to get EXISTS to work with two-dimensional arrays. Here's my example code:
DECLARE
TYPE data_t IS TABLE OF VARCHAR2(1)
INDEX BY PLS_INTEGER;
TYPE array_t IS TABLE OF data_t
INDEX BY PLS_INTEGER;
v_2d_grid array_t;
v_row NUMBER;
v_col NUMBER;
BEGIN
FOR i IN 1..30 LOOP
v_row := TRUNC(dbms_random.VALUE(1,5));
v_col := TRUNC(dbms_random.VALUE(1,5));
IF v_2d_grid.EXISTS (v_row)(v_col) THEN
DBMS_OUTPUT.PUT_LINE('found '||v_row||','||v_col||' = '|| v_2d_grid (v_row) (v_col));
ELSE
v_2d_grid (v_row) (v_col) := dbms_random.string('x',1);
DBMS_OUTPUT.PUT_LINE('set '||v_row||','||v_col||' = '|| v_2d_grid (v_row) (v_col));
END IF;
END LOOP;
END;
But when I run it I get -
ORA-06550: line 16, column 9:
PLS-00224: object 'EXISTS' must be of type function or array to be used this way
ORA-06550: line 16, column 6:
PL/SQL: Statement ignored
And googling and searching the group hasn't turned up any examples of how to do it.
I know I can wrap it in a begin/end, catch the NO_DATA_FOUND exception when I access the value and do the function call there, but I'd rather use EXISTS so it's clearer what I'm doing.
Anyone know how to do it - or can say definitively that I can't use EXISTS here?