Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using EXISTS on 2-dimensional array

450441Nov 27 2008 — edited Nov 27 2008
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?
This post has been answered by Brian Camire on Nov 27 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 25 2008
Added on Nov 27 2008
4 comments
1,267 views