How to use Bulk Collect with dbms_sql.varchar2a in 10g [message #338332] |
Mon, 04 August 2008 11:00 |
Kaeluan
Messages: 179 Registered: May 2005 Location: Montreal, Quebec
|
Senior Member |
|
|
Hello,
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
declare
TYPE cgc$rec IS RECORD
(GENRE_AFFAIRE GAG_RESULTATS_RECH_CLIENT_V.GENRE_AFFAIRE%TYPE
,CLIENT_LANG_ID GAG_RESULTATS_RECH_CLIENT_V.CLIENT_LANG_ID%TYPE
,CLIENT GAG_RESULTATS_RECH_CLIENT_V.CLIENT%TYPE);
TYPE cgc$rec_tab IS TABLE OF cgc$rec INDEX BY BINARY_INTEGER;
type resultat_crs_type is ref cursor;
resultat_crs resultat_crs_type;
v_select dbms_sql.varchar2a;
v_cur_id number;
ret number;
v_var1 boolean := true;
v_var2 boolean := false;
begin
v_select(1) := 'SELECT
VRESRECCLI.GENRE_AFFAIRE GENRE_AFFAIRE,
VRESRECCLI.CLIENT_LANG_ID CLIENT_LANG_ID,
VRESRECCLI.CLIENT CLIENT,....'
if v_var1 then
v_select(v_select.last+1) := 'some where clause here';
end if;
if v_var2 then
v_select(v_select.last+1) := 'some some other condition here';
end if;
v_cur_id := dbms_sql.open_cursor;
dbms_sql.parse(v_cur_id,v_select,1,v_select.count,true,dbms_sql.native);
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;
close resultat_crs;
*/
end;
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.
|
|
|
|