Using Function in Where Clause

[mjjrr1]

I have a function which returns a String of Numeric values. I chose a string variable as I can then return more than one set of values.

I want to then use the sets of values I returned as an IN statement the WHERE clause of a SELECT SQL.

However I keep getting Invalid Number. can someone show me how this be done?

For example:
FUNCTION F_FindOutstandingOrders(p_Date IN DATE)
RETURN VARCHAR2 AS

v_strReturnValue VARCHAR2(1000);

CURSOR Cur_Orders (p_TXN_Date DATE) IS
SELECT OrderNo
FROM ORDERS
WHERE Order_DATE BETWEEN p_TXN_Date - 14 AND p_TXN_Date + 14

Rec_Orders Cur_Order%ROWTYPE;
LOOP
BEGIN
FETCH Cur_Orders INTO Rec_Orders;
EXIT WHEN Cur_Orders%NOTFOUND;
IF Cur_Orders%ROWCOUNT = 1 THEN
v_strReturnValue := Rec_Orders.OrderNo;
ELSE
v_strReturnValue := v_strReturnValue||', '||Rec_Orders.OrderNo;
END IF;
END;
END LOOP;
CLOSE Cur_Orders;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION IN FUNCTION F_FindOutstandingOrders:'||SQLCODE||':'||SQLERRM);
RETURN 'Error';
END F_FindOutstandingOrders;

If the funtion returned 734, 745, 748 where selected from Dual;

But when I typed Select * from Payments Where Order_ID IN (F_FindOutstandingOrders(TO_DATE('22/01/2003', 'DD/MM'YYYY')
/

I get ERROR at line 1:
ORA-01722: invalid number

IF the fuction returned a single value then the query runs.

[Shestakov]

I think for ur problem more elegant desicion may be following:

1. create some type of numbers
2. create "pivot" function that has to return list of order ids.
3. use table(cast()) construction for return needed result
example:

SQL> create or replace type tb_numbers as table of number;
     /
Type created.

SQL> create or replace
function get_obj_ids( p_date_start date, p_date_end date ) return tb_numbers is
  tb tb_numbers := tb_numbers();
  j pls_integer := 0;
begin
  for r in (select object_id from all_objects
            where created between p_date_start and p_date_end)
  loop
     tb.extend;
     j := j + 1;
     tb(j) := r.object_id;
  end loop;
  return tb;
end;
/

Function created.

SQL> select o.object_id, o.object_name
from all_objects o,
     table(cast(get_obj_ids(to_date('12.10.02','mm.dd.yy'),
                            to_date('12.30.02','mm.dd.yy')) as tb_numbers)) t
where t.column_value = o.object_id;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     12359 GET_SHIFT_DATE
     12362 R_COUNTER
     12363 T_COUNTER