ref cursor and bulk collect dynamic sql

[zapster]

Hi all

I'm using oracle 8i (8.1.6.1.0) and at the moment I cannot seem to get this dynamic sql to work with a bulk collect. ok the sql statement is currently hard coded but it will be made up on the fly, once I have got pass this problem. My objective is then to update a other tables using the collection I have generated.

Please can someone have a look at my code and tell me where I have gone wrong.

DECLARE

TYPE cat_varray IS TABLE OF promo_groups_expanded.VALUE%TYPE;
TYPE pgi_varray IS TABLE OF promo_groups_expanded.PROMO_GROUP_ID%TYPE;


TYPE aTyp is REF CURSOR;

b_cur aTyp;

catalog_nos 	cat_varray;
promo_group_ids	pgi_varray;

sql_stmt varchar2(2000);


BEGIN

sql_stmt := 'select promo_group_id from promo_groups_expanded';											 


open b_cur FOR sql_stmt;
FETCH b_cur BULK COLLECT into promo_group_ids,catalog_nos;
CLOSE b_cur;


END;

I get the following error
ORA-01001: invalid cursor
ORA-06512: at line 23

Thanks

Zapster

 

[sem]

How do you plan to fetch single-field cursor into 2 different collections?

Regards, Dima

http://seminihin.narod.ru

 

[zapster]

Sorry,

I missed the other column when typing it up to post..

DECLARE

TYPE cat_varray IS TABLE OF promo_groups_expanded.VALUE%TYPE;
TYPE pgi_varray IS TABLE OF promo_groups_expanded.PROMO_GROUP_ID%TYPE;


TYPE aTyp is REF CURSOR;

b_cur aTyp;

catalog_nos     cat_varray;
promo_group_ids    pgi_varray;

sql_stmt varchar2(2000);


BEGIN

sql_stmt := 'select promo_group_id, value from promo_groups_expanded';                                             


open b_cur FOR sql_stmt;
FETCH b_cur BULK COLLECT into promo_group_ids,catalog_nos;
CLOSE b_cur;


END;

 

[sem]

I'm sure that you need at least 8.1.7, because 8.1.6 had a lot of restrictions with weak cursors. In 8.1.6. you may define a number of parametrized cursors and then open ref cursor for one of them. In any case to use ref cursor with bulk clause your cursor should be describable.

Regards, Dima

http://seminihin.narod.ru