0 Replies Latest reply: Nov 6, 2012 4:10 PM by 960546 RSS

    Export info to excel. (spool ? )

    960546
      Hi experts..


      I have this problem...

      I am running oracle 10g and need to export a big query to an excel file...

      Im using the spool technique... but.. this is not working when i Have a really big query ( like about 6000 characters....)

      I always get an empty excel file..

      and the sql file i create, with plsql instrucctions doesnt contain the query to export....


      Any oher idea, to create an excel file, and compatible with java 1.4.2 (if posible) will be very well apreciated).


      The example only works well whe the query is small....

      Here is an example of what I am doing :


      datos_xls := ' BIG SQL QUERY WITH ALOT OF concatenations using || ';

      xplsql:=' set pagesize 5000 ; set echo off ; set termout off; set feedback off; set feed off markup html on spool on; '
      ||' spool '||:EXCEL.PATH||'\' ||excel_filename||'; '||datos_xls||'; set markup html off; spool off; exit;';


      -- SEND ALL INSTRUCCTIONS TO AN SQL FILE

      xpos:=1;
      x:=1;
      while x<50 and instr(xplsql,';',1,x) <> 0 loop     
           sqlstr:=substr(xplsql,xpos,instr(substr(xplsql,xpos),';',1,1));     
           sqlstr:='echo '||sqlstr||' >> '||:EXCEL.PATH||'\'||file_sql;
           HOST(sqlstr);
           
           x:=x+1;
           xpos:=instr(xplsql,';',1,x)+1;
      end loop;



      -- NOW EXECUTE THE INSTRUCCTIONS IN THE SQL FILE -----

      sqlstr:='sqlplus username/pwd@server @'||:EXCEL.PATH||'\'||file_sql;
      HOST(sqlstr);


      -- SHOW THE RESULT XLS FILE ON BROWSER--------------

      sqlstr:='http://192.168.1.x/'||excel_filename;
      WEB.SHOW_DOCUMENT(sqlstr, '_blank');

      MESSAGE('<ENTER> DOCUMENT DONE...');
      PAUSE;

      EXCEPTION
           WHEN OTHERS THEN
           MESSAGE('***ERROR: '||SQLERRM);
           PAUSE;
      END;



      Thanks in advance.