CODEWITHSUNDEEP
oracle-databaseplsql
FrenkyB
FrenkyB

Reputation: 7207

Join and select from table variable

I would like to join table variable of type t_number with INV_SRV_ATT1.ATT_ID and select those results. Is something like this possible in ORACLE?

declare
  type t_number is table of number;
  v_numbers t_number;
begin
  select id
    bulk collect into v_numbers
    from inv_srv1;    

    select v_numbers.ID from v_numbers join INV_SRV_ATT1 a ON v_number.ID = a.ATT_ID;
end;

With query above I have an error:

PL/SQL: ORA-00942: table or view does not exist

Table definitions:

CREATE TABLE INV_SRV_ATT1
(
ATT_ID number,
SERVICE_ID number
)


INSERT INTO INV_SRV_ATT1(ATT_ID, SERVICE_ID)
SELECT 1, 1 FROM DUAL
UNION ALL SELECT 2, 1 FROM DUAL
UNION ALL SELECT 3, 999 FROM DUAL


CREATE TABLE INV_SRV1
(
    ID number,
    name nvarchar2(255),
    SRV_CATEGORY_ID number,
    PARENT_SRV_ID number
)

insert into inv_srv1(id, name, srv_category_id, parent_srv_id)
select 1, 'under root 1', 1, null from dual
union all select 2, 'under root 1', 1, null from dual
union all select 3, 'under root 1', 1, null from dual
union all select 4, 'under root 1', 1, null from dual

union all select 5, 'under srv 4', null, 4 from dual
union all select 6, 'under srv 4', null, 4 from dual
union all select 7, 'under srv 4', null, 4 from dual

union all select 8, 'under srv 7', null, 7 from dual
union all select 9, 'under srv 7', null, 7 from dual
union all select 10, 'under srv 7', null, 7 from dual

union all select 11, 'under cat 7', 7, null from dual
union all select 12, 'under cat 7', 7, null from dual

union all select 13, 'under srv 11', null, 11 from dual
union all select 14, 'under srv 11', null, 11 from dual

union all select 15, 'under srv 11', 5, null from dual
union all select 16, 'under srv 11', 5, null from dual

union all select 17, 'under cat 13', 13, null from dual
union all select 18, 'under cat 13', 13, null from dual
union all select 19, 'under srv 18', null, 18 from dual

Upvotes: 1

Views: 901

Answers (1)

Littlefoot
Littlefoot

Reputation: 143033

Here's how:

SQL> declare
  2    v_numbers sys.odcinumberlist;
  3  begin
  4    select id
  5      bulk collect into v_numbers from inv_srv1;
  6
  7    for cur_r in (select t.column_value
  8                  from table(v_numbers) t join inv_srv_att1 i
  9                          on i.att_id = t.column_value
 10                 ) loop
 11      dbms_output.put_line(cur_r.column_value);
 12    end loop;
 13  end;
 14  /
1
2
3

PL/SQL procedure successfully completed.

SQL>

If you want to return the result, then it is a function, not a stored procedure (OK, procedures can have OUT parameters, but - functions are designed for that). One option is to return ref cursor, e.g.

SQL> create or replace function f_test
  2    return sys_refcursor
  3  is
  4    v_numbers sys.odcinumberlist;
  5    rc        sys_refcursor;
  6  begin
  7    select id
  8      bulk collect into v_numbers from inv_srv1;
  9
 10    open rc for select t.column_value
 11                  from table(v_numbers) t join inv_srv_att1 i
 12                          on i.att_id = t.column_value;
 13
 14    return rc;
 15  end;
 16  /

Function created.

SQL> select f_test from dual;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

COLUMN_VALUE
------------
           1
           2
           3


SQL>

Or, return an array:

SQL> create or replace function f_test
  2    return sys.odcinumberlist
  3  is
  4    v_numbers sys.odcinumberlist;
  5    v_retval  sys.odcinumberlist;
  6  begin
  7    select id
  8      bulk collect into v_numbers from inv_srv1;
  9
 10    select t.column_value
 11      bulk collect into v_retval
 12      from table(v_numbers) t join inv_srv_att1 i
 13              on i.att_id = t.column_value;
 14
 15    return v_retval;
 16  end;
 17  /

Function created.

SQL> select * From table(f_test);

COLUMN_VALUE
------------
           1
           2
           3

SQL>

Upvotes: 2

Related Questions