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. :confused:

[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