Function or procedure for IN clause

Question

I want to write a funcion or procedure that can be used in the IN clause of another procedure. The function or procedure will return the identification numbers.

The main procedure will say something like

SELECT * FROM EMPLOYERS WHERE OFFICE_ID IN (GET_OFFICE_IDS); - GET_OFFICE_IDS does not require parameters

GET_OFFICE_IDS returns VARCHAR2 with an identifier separated by commas. When I start the main procedure, I get the error "ORA-01722: invalid number", which makes sense, but I do not know where I need to go from here.

Do I need GET_OFFICE_IDS to create a temporary table used by the main procedure? If so, will there be a penalty for enforcement?

Answer 1

, EMP:

create type t_ids is table of integer
/

create or replace function get_office_ids return t_ids
is
   l_ids t_ids := t_ids();
   l_idx integer := 0;
begin
   for r in (select empno from emp where deptno=10)
   loop
      l_ids.extend;
      l_idx := l_idx+1;
      l_ids(l_idx) := r.empno;
   end loop;
   return l_ids;
end;
/

select ename from emp where empno in (select * from table(get_office_ids));


ENAME
----------
CLARK
KING
TEST
MILLER
BINNSY
FARMER

Answer 2

:

WHERE ','||GET_OFFICE_IDS||',' LIKE '%,'||OFFICE_ID||',%'

GET_OFFICE_IDS, - :

OFFICE_ID IN (SELECT * FROM TABLE(GET_OFFICE_IDS))

Answer 3

oracle SQL, IN select, ?

SELECT * FROM EMPLOYEES WHERE OFFICE_ID IN (SELECT ID FROM tbl_X WHERE x = y);

... - ?

Answer 4

, ref_cursor ( c: = 'select' ||....)

. :

create or replace type type_varchar2 as table of varchar2(100);

create or replace function GET_OFFICE_IDS return TYPE_varchar2 PIPELINED
is
  retval VARCHAR2(100);
begin
  -- put some sql here which results in statements as below
 retval := '135';
 PIPE ROW (retval);
 retval := '110';
 PIPE ROW (retval);
end GET_OFFICE_IDS;


select *
from entries
where id in (SELECT COLUMN_VALUE FROM TABLE(GET_OFFICE_IDS));

. .

Answer 5

: SO, OP. , .

, SQL PL/SQL. 2 . SQL PL/SQL. .

, . . , , . , , .

, .

create or replace function my_Date (p_Date in date)
return varchar
as
begin

    return to_char(p_Date, 'yyyy/mm/dd');

end;

.

autotrace

.

select to_char(created, 'yyyy/mm/dd'), to_char(last_ddl_time, 'yyyy/mm/dd')  from all_objects


select my_date(created), my_Date(last_DDL_TIME) From all_objects

. 1 1 2 2 .

... , , . , , . .

, , .

IN, , . , . IN JOIN. , , . FORCED , - .

, , - rowcount. 1 . . .

, , , , ? ? -, , , .

SQL. . . .