oracle associative array exists in case statement results in compilation failure

Question

This is a hypothetical example. The actual problem involves updating various columns based on values, if they exist in the associative array. The following throws ORA-06550 and ORA-01747. Please help in fixing the error.

declare
   type MONTH_TYPE is table of varchar2(20) index by binary_integer;

   month_table   MONTH_TYPE;
   mon varchar2(20);
 begin
   month_table(1) := 'Jan';
   month_table(2) := 'Feb';

   select case when month_table.exists(1) then 'found' else 'not found' end into mon from dual;
 end;

The actual statement goes like this: update sometable set appleQty case qtyArray.exists('apple') then qtyArray('apple') else myrec.applyQty end; — Suneel, May 23 '13 at 17:29
see if this helps: http://stackoverflow.com/questions/1715978/how-to-use-an-oracle-associative-array-in-a-sql-query — David Aldridge, May 23 '13 at 17:36

Tony Andrews · Answer 1 · 2013-05-23T17:55:54.680

3

You cannot call the PL/SQL exists function from a SQL statement. You can reference the values in the collection if you need to:

declare
   type MONTH_TYPE is table of varchar2(20) index by binary_integer;
   month_table   MONTH_TYPE;
   mon varchar2(20);
 begin
   month_table(1) := 'Jan';
   month_table(2) := 'Feb';
   select case when month_table(1)='Jan' then 'found' else 'not found' end
        into mon from dual;
end;

Or you can use exists within PL/SQL:

declare
   type MONTH_TYPE is table of varchar2(20) index by binary_integer;

   month_table   MONTH_TYPE;
   mon varchar2(20);
 begin
   month_table(1) := 'Jan';
   month_table(2) := 'Feb';

   mon := case when month_table.exists(1) then 'found' else 'not found' end;
end;

From your comments it sounds like a database type may be the way to go:

SQL> create type MONTH_TYPE is table of varchar2(20);

Then you can select from this in your SQL:

declare
   month_table   MONTH_TYPE := MONTH_TYPE();
   mon varchar2(20);
 begin
   month_table.extend;
   month_table(1) := 'Jan';
   month_table.extend;
   month_table(2) := 'Feb';

   update some_table
   set x = 1
   where month in (select column_value from table(month_table));
end;

edited May 23 '13 at 17:55

answered May 23 '13 at 17:25

Tony Andrews

129,880
21
220
259

Is there a way to use that in a update statement as i have to update various columns in a table based on the values in the associate array. Without that feature, I will have to write an update statement for each column based on the value in the associative array. – Suneel May 23 '13 at 17:33
The problem with referencing values in select without exists is that I get no_data_found error for values that don't exist in the associative array. – Suneel May 23 '13 at 17:35
No, you can't use `.exists` in any SQL statement, including `update`. – Tony Andrews May 23 '13 at 17:40
Can you not just populate them will NULLs? – Tony Andrews May 23 '13 at 17:40
Populuating with nulls only works if I know what all keys (index values) go into the asoc array before hand. What if there are some new keys or if the list of keys is huge like 30? I am actually using a varchar as the index in my actual plsql, which is the itemCode of a product. ( I do not have the itemId) – Suneel May 23 '13 at 17:45
The last example is using a nested table instead of an assoc array. I think he wants to update the column with the value using a key (my_qty_tab('apple') := 42 for example). So pull the value (42) from the assoc array itself. Although I'm not sure how to do that without some ugly looping ;-) – tbone May 23 '13 at 18:27
@TonyAndrews that last example helps but not exactly what I am looking for as tbone mentioned. Appreciate your help. – Suneel May 23 '13 at 19:47

oracle associative array exists in case statement results in compilation failure

1 Answers1