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
CURSOR AVG_CSR IS
SELECT ENAME, DEPTNO, SAL
F := UTL_FILE.FOPEN(DIR, FILENAME,'W');
UTL_FILE.PUT_LINE(F, 'REPORT GENERATED ON ' ||SYSDATE);
FOR EMP IN AVG_CSR
RPAD(EMP.ENAME, 30) || ' ' ||
LPAD(NVL(TO_CHAR(EMP.DEPTNO,'9999'),'-'), 5) || ' ' ||
LPAD(TO_CHAR(EMP.SAL, '$99,999.00'), 12));
UTL_FILE.PUT_LINE(F, '*** END OF REPORT ***');
COMMAND TO EXECUTE THE PROCEDURE IS : - >
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
URMIN GROUP OF COMPANIES
Well, for starters, what you are producing is not an XLS file.
Read the FAQ: Re: 5. How do I read or write an Excel file?
As for your error, have you created the directory object "UTL_FILE"? (not a good name for a directory object considering it's the name of a package)
Is that directory object granted with read and write permissions to the user running the code?
Does the directory path specified by the directory object actually exist on the database server?
first of all try to use international measures. Any time I see lakh I have to google to find what it is.
So you are trying to put in an Excel file 1.200.000 records (more than 1 million).
Actually even if you would produce such file Excel will not be able to load it.
Even in the latest version of Excel (2013) the maximum number of rows per sheet are 1,048,576 (source: Excel specifications and limits - Excel - Office.com).
So if you will try to load in Excel such a big file you will get an error.
And, as Blue has said, you are not producing an Excel file with your statement.
Can you log into DB server and check the directory path exists in DB (DB)..
I think you have created directory path using following DDL.. can you check below mentioned X path exists in DB server..as well as you are having edit access for that path
create or replace directory DIR as '/XX/XXX'