Home » SQL & PL/SQL » SQL & PL/SQL » How to use Bulk Collect with dbms_sql.varchar2a in 10g
How to use Bulk Collect with dbms_sql.varchar2a in 10g [message #338332] Mon, 04 August 2008 11:00 Go to next message
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.

Re: How to use Bulk Collect with dbms_sql.varchar2a in 10g [message #377904 is a reply to message #338332] Fri, 26 December 2008 03:51 Go to previous message
sathyam2627
Messages: 52
Registered: November 2006
Member
Please go thru this link...

http://www.dba-oracle.com/plsql/t_plsql_dynamic.htm

I hope this helps you..

Thanks
Previous Topic: Help needed for writing a procedure
Next Topic: update one table and compare with another table
Goto Forum:
  


Current Time: Sat Nov 09 16:39:32 CST 2024