Associative array EXISTS() DML
193996Jun 13 2005 — edited Jun 14 2005Is there a way to include the EXISTS() method of an associative array in a DML statement? For example:
This is fine...
SQL> declare
2 type distTblTyp is table of number index by varchar2(8);
3 testTbl distTblTyp; testVar varchar2(30);
4 begin
5 testTbl('a') := 1;
6 if (testTbl.exists('a')=true) then
7 null;
8 end if;
9* end;
SQL> /
PL/SQL procedure successfully completed.
But this fails...
SQL> declare
2 type distTblTyp is table of number index by varchar2(8);
3 testTbl distTblTyp; testVar varchar2(30);
4 begin
5 testTbl('a') := 1;
6 select decode(testTbl.exists('a'),true,'a','b')
7 into testVar from dual;
8* end;
SQL> /
select decode(testTbl.exists('a'),true,'a','b')
*
ERROR at line 6:
ORA-06550: line 6, column 24:
PL/SQL: ORA-01747: invalid user.table.column, table.column, or column
specification
ORA-06550: line 6, column 2:
PL/SQL: SQL Statement ignored
PL/SQL: SQL Statement ignored
Since I won't know which index elements exist until execution time, I want to have an insert statement (ran within a loop) with ~100 field values like:
insert into table [tablename]
values (
decode(testTbl.exists('a'), true, testTbl('a'), null),
decode(testTbl.exists('b'), true, testTbl('b'), null)
...
)
Any suggestions appreciated.