Oracle 11g PLSQL collections in SELECT statement

Question

I have a collection of type table in PLSQL begin block and I had to use it multiple time in my code.

I there any way to use the list (table type) in SELECT statement? My goal is to write less code.

I have included the array (TABLE) directly inside the SELECT statement

Bellow script is shown what I'm trying to do, but apparently, I can no do that, because we can't use collections in SQL statement (Please check the delete statement).

clear Screen
SET SERVEROUTPUT ON
SET linesize 300
SET timing ON

WHENEVER SQLERROR EXIT failure ROLLBACK;
WHENEVER oserror EXIT failure ROLLBACK;

DECLARE
    code_groupe            VARCHAR2 (50 CHAR);
    code_grp               VARCHAR2 (4 CHAR);
    TYPE arrayList is table of varchar2(50);
    site_code_ls arrayList;
BEGIN
    code_grp          := 'ABDE';
    site_code_ls      := arrayList ('D','C','B','A','L');
    DELETE CARP.PLACES_GROUPS
     WHERE CODE_GROUPE = code_grp AND CODE_PLACE NOT IN (SELECT S.CODE
                                                       FROM CARP.PLACE P
                                                      WHERE P.CODE_IMPLANTATION IN site_code_ls;
    COMMIT;
END;
/
exit;

Possible duplicate of [How to use a table type in a SELECT FROM statement?](https://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement) — XING, Apr 12 '19 at 11:27

score 1 · Answer 1 · answered Apr 12 '19 at 10:31

You cannot use local collections like that, but you can use forall which is fast, like here:

declare 
  type tt is table of varchar2(5);
  vt tt := tt('C', 'E', 'N', 'X');
begin 
  forall i in vt.first..vt.last 
    delete from test 
    where name = vt(i);
end;

Here you can see speed comparison of forall and for ... loop.

hotfix · Answer 2 · 2019-04-12T09:20:36.467

if you use oracle 12c, then you should be able use a local defined collection with a table operator. e.g.

Select  * 
  from table 
 where column in(select column_value from table(PLACE_code_ls))

or you can create a collection as object type

create or replace type arrayList is table of varchar2(100);

and than use it in your PLSQL block.

DECLARE
...
    PLACE_code_ls arrayList ; -- this is a new created global object type
...

BEGIN   
    code_grp          := '454D';
    PLACE_code_ls      := arrayList ('ABVDE','ADE','EFEF','JHGJ','JHGJH');


    DELETE CARP.assos_place_grp
     WHERE CODE_GROUPE = code_grp AND CODE_PLACE NOT IN (SELECT S.CODE
                                                       FROM CARP.PLACE S
                                                      WHERE S.CODE_IMPLANTATION IN 
                                            (select column_value from table(PLACE_code_ls)); 
                                             -- select from your collection



    COMMIT;
END;
/

Oracle 11g PLSQL collections in SELECT statement

2 Answers2