How do I get an array into a resultset

[Harrie K.]

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_arr ay.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.

[Frank]

Re: How do I get an array into a resultset

Harrie K. wrote:

<snip!>

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.

Normally, I would use a subselect - in clauses that large
should be tables, really.

--
Regards, Frank van Bortel