3 Replies Latest reply: Jun 25, 2013 3:01 PM by user601042 RSS

    create XLS file of 12 lakhs rows stored into oracle database.

    focusharsh

      Respected All,

       

         My requirement is :

       

      Into my database , in one of my table has 12 lakhs rows. Now I want to create a PLSQL procedure which can convert all those rows into Microsoft .XLS file.

       

      So I tried out with Procedure below : -

       

      CREATE OR REPLACE PROCEDURE SCOTT.EMPLOYEE_REPORT(

      DIR IN VARCHAR2, FILENAME IN VARCHAR2) IS

      F UTL_FILE.FILE_TYPE;

          CURSOR AVG_CSR IS

          SELECT ENAME, DEPTNO, SAL

          FROM EMP;

      BEGIN

      F := UTL_FILE.FOPEN(DIR, FILENAME,'W');

      UTL_FILE.PUT_LINE(F, 'REPORT GENERATED ON ' ||SYSDATE);

      UTL_FILE.NEW_LINE(F);

      FOR EMP IN AVG_CSR

      LOOP

      UTL_FILE.PUT_LINE(F,

      RPAD(EMP.ENAME, 30) || ' ' ||

      LPAD(NVL(TO_CHAR(EMP.DEPTNO,'9999'),'-'), 5) || ' ' ||

      LPAD(TO_CHAR(EMP.SAL, '$99,999.00'), 12));

      END LOOP;

      UTL_FILE.NEW_LINE(F);

      UTL_FILE.PUT_LINE(F, '*** END OF REPORT ***');

      UTL_FILE.FCLOSE(F);

      END EMPLOYEE_REPORT;

      /


      COMMAND TO EXECUTE THE PROCEDURE IS : - >


      EXEC EMPLOYEE_REPORT('UTL_FILE','TEST.XLS')

       

         in this package , I used scott user table for the r & d.

      While I execute this package , I got the error :

       

      ORA-29280: invalid directory path

      ORA-06512: at "SYS.UTL_FILE", line 18

      ORA-06512: at "SYS.UTL_FILE", line 424

      ORA-06512: at "SCOTT.EMPLOYEE_REPORT", line 8

      ORA-06512: at line 1

       


         KINDLY HELP ME TO SOLVE THIS PROBLEM


      THANKS/ REGARDS

      HARSH SHAH

       

      URMIN GROUP OF COMPANIES