I want to make an Stored Procedure on a Oracle 7.x database that fills
a resulset based on the values in an array:
CREATE OR REPLACE PACKAGE PCK_SET
IS
TYPE rec_set IS RECORD
( type_id NUMBER
, name VARCHAR2(30)
)
TYPE cur_set IS REF CURSOR
RETURN rec_set ;
TYPE arr IS TABLE OF NUMBER INDEX BY binary_integer ;
v_array arr ;
END PCK_SET ;
/
CREATE OR REPLACE PROCEDURE SP_GET_SET
( resultset IN OUT PCK_SET.cur_set
) AS
BEGIN
FOR x IN 1 ..PCK_SET.v_array.COUNT
LOOP
SELECT type_id
, name
INTO resultset
FROM SET_TABLE
WHERE arr_id = PCK_SET.v_array(x) ;
END LOOP ;
END SP_GET_SET ;
Normally I would do:
OPEN resultset FOR
SELECT type_id
, name
FROM SET_TABLE
where arr_id in (1,2,3, etc.)
Only the in-clause can be more than 254, so the ORA-01795 shows
This example does not give a resultset.