10 Replies Latest reply on Sep 4, 2017 4:34 PM by rp0428

    generate excel file using cursor in anonymous block in oracle

    user12251389

      I have below anonymous block where i am using cursor to generate the resultset and save it into TEST_REPORT.csv file. But i am getting error on the line where i am using spool as:

      PLS-00103: Encountered the symbol "H" when expecting one of the following:

         := . ( @ % ;

      I believe i cannot use spool in PL/SQL but not sure so i have tried below code. And also i cannot use UTL_FILE or UTL_FILE_DIR logic because of security restriction on the Production.  As employees works on different department i want to generate separate csv file for each employee with respect to their department.

       

      Is there anyway where i can break this code and use spool to generate csv file or any other logic ? Also if it is not possible to use spool in anonymous block then can i use it during execution of this code to generate files ?

       

      If its not possible using spool then is it possible if i can organize my query result in such a way that it will be easy to export the result into single csv file after executing this anonymous block and then i can separate the single csv file into multiple files depending on the employee with their department manually ?

       

      DECLARE
        a      varchar2(100);
        b      varchar2(100);
        c      varchar2(100);
        
      CURSOR c1
            IS 
      
      
      select 'id'         ||','||
             'department_name'         ||','||
             'employee'                  
      result from dual
      union all
      select id    ||','||
             department_name    ||','||
             employee      
      from EMPLYOEE_MARKET;
      
      
      BEGIN
        OPEN c1;
        LOOP
        spool H:\TEST_REPORT.csv  ;
          FETCH c1 
           INTO a, b, c;
          EXIT WHEN c1%NOTFOUND;
         spool off;
        END LOOP;
        CLOSE c1;
      END;