Join table-type object with a regular table in Oracle

Question

I have a regular table with entries and several types declared like that:

create TYPE entity as OBJECT ( attribute1 NUMBER);
create TYPE entity_array as OBJECT of entity;

And now I want using the sql worksheet join declared entity_array object with my table:

DECLARE
        entity_array_obj entity_array := entity_array(
          entity(11111),
          entity(22222)
          );
BEGIN
       select * from mytable as t1 
       inner join entity_array_obj as t2 on (t1.attribute1 = t2.attribute1);
END;

And I'm getting compilation error:

PL/SQL: ORA-00933: SQL command not properly ended

Please suggest

Answer 1

create type entity as object ( attribute1 number);
create type entity_array as table of entity;

create table mytable(attribute1 number);
insert into mytable values (11111);

declare
    entity_array_obj entity_array := entity_array(
        entity(11111),
        entity(22222)
    );
    v_count number;
begin
    select count(*)
    into v_count
    from mytable t1
    join table(entity_array_obj) t2
        on t1.attribute1 = t2.attribute1;

    dbms_output.put_line('Count: '||v_count);
end;
/

Here are the main changes made to the code:

1. Changed as OBJECT of entity; to as TABLE of entity;. You may not have noticed this error because some IDEs suppress code compilation errors.
2. Table aliasing in Oracle does not work with "as".
3. Selects in PL/SQL must always select INTO something.
4. The table() operator converts the collection into a row source.