CODEWITHSUNDEEP
sqloracle-databaseplsqluser-defined-types
abhishek bhat
abhishek bhat

Reputation: 1

How to use object type data for a in clause in oracle sql?

I have a type declaration as below

CREATE TYPE ip_usr_lbl AS OBJECT (user_id     VARCHAR2(30));

I have a type table creates as below

CREATE  TYPE ip_lst_dtl IS TABLE OF ip_usr_lbl;

O_usr_lbl ip_lst_dtl;

I have 20 users id in O_usr_lbl;

How can I use this in the query as below.

select emp_id from
employee where emp_name in (select user_id from table(O_usr_lbl));

I tried creating a function to return object as an output and using 

select user_id from table(function(IN));

but it didn't work.

Expect to query employee table using USER_ID IN object O_usr_tbl

Upvotes: 0

Views: 1684

Answers (1)

XING
XING

Reputation: 9886

How can I use this in the query as below.

select emp_id from employee where emp_name in (select user_id from table(O_usr_lbl));

If your intention is to use the Object type in scope of SQL as plain Select statement as shown above then its not possible to do so.

You can use the object as table operator under the scope of PLSQL as shown below: 

declare
  --Created a list of users. 
  o_usr_lbl   ip_lst_dtl := ip_lst_dtl(ip_usr_lbl('SMITH'),ip_usr_lbl('ALLEN') );
  v_var       ip_lst_dtl;
begin

  select ip_usr_lbl(empno) 
  bulk collect into v_var -- Holding the result set to a variable
  from emp
  where ename in (select user_id from table ( o_usr_lbl ));

  --Displaying the result
  for i in 1..v_var.count loop
    dbms_output.put_line(v_var(i).user_id);
  end loop;

end;

Output:

SQL> /
7369
7499

PL/SQL procedure successfully completed.

--Function can be written as 

Create or replace function usr_dtls ( inpt IN ip_lst_dtl) 
return ip_lst_dtl is
  v_var   ip_lst_dtl;
begin
  select ip_usr_lbl(empno) 
  bulk collect into
    v_var
  from emp
  where ename 
  in (
    select user_id from table ( inpt )
  );
  return v_var;
end;

--Execution of function

SQL> select * from table (usr_dtls(ip_lst_dtl(ip_usr_lbl('SMITH'),ip_usr_lbl('ALLEN') )));

USER_ID
------------------------------
7369
7499

OR

declare
  o_usr_lbl   ip_lst_dtl := ip_lst_dtl(ip_usr_lbl('SMITH'),ip_usr_lbl('ALLEN') );
  v_var       ip_lst_dtl;
begin
   v_var := usr_dtls(inpt =>o_usr_lbl);
   --Displaying the result

  for i in 1..v_var.count loop
    dbms_output.put_line(v_var(i).user_id);
  end loop;
end;

Upvotes: 2

Related Questions