469,609 Members | 1,846 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

How do I get an array into a resultset

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.
Jul 19 '05 #1
1 4017
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

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.