|How to use Bulk Collect with dbms_sql.varchar2a in 10g [message #338332]
||Mon, 04 August 2008 11:00
Registered: May 2005
Location: Montreal, Quebec
Is there a way to use bulk collect with dynamic SQL when my variable type is dbms_sql.varchar2a in 10g
Here is what my code look like
TYPE cgc$rec IS RECORD
TYPE cgc$rec_tab IS TABLE OF cgc$rec INDEX BY BINARY_INTEGER;
type resultat_crs_type is ref cursor;
v_var1 boolean := true;
v_var2 boolean := false;
v_select(1) := 'SELECT
if v_var1 then
v_select(v_select.last+1) := 'some where clause here';
if v_var2 then
v_select(v_select.last+1) := 'some some other condition here';
v_cur_id := dbms_sql.open_cursor;
ret := DBMS_SQL.EXECUTE(v_cur_id);
-- This code is working in 11g but not in 10g?
resultat_crs := DBMS_SQL.TO_REFCURSOR(v_cur_id);
FETCH resultat_crs BULK COLLECT INTO cgc$rec_tab;
What should i do next if i want to use Bulk Collect to fetch my cursor. In 11g there is a new function that allow to convert number cursor to ref cursor and this should work, but since i am working in 10g I did not find any usefull exemple on how to complete my procedure. Is it possible to do this in 10g?
I was able to make my procedure work by using a single variable and concatenating all where clause in it but i was afraid than the 32k limit of my varchar2 could be a little short since all the dynamic code is generated by some choice made by the user in our application. So i was thinking that maybe if i was using a table of varchar it could be better but don`t know if it`s possible in 10g.
I hope my question is clear.
Thank for helping.