Home » SQL & PL/SQL » SQL & PL/SQL » Excel Report From SP - ORA 29285 - file write error
Excel Report From SP - ORA 29285 - file write error [message #402016] Thu, 07 May 2009 05:40 Go to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
Hi,

I have created SP to generate the excel file by fetching the records from table.

SP

CREATE OR REPLACE PROCEDURE PRM_CNR_EXCEL IS
M_FILEPATH VARCHAR2(100);
F1 UTL_FILE.FILE_TYPE;
LV_FILENAME VARCHAR2(30);
CURSOR C1 IS
SELECT * FROM PRM_CORPORATE_NAME_REPORT; --WHERE trunc(RUN_DATE)=TRUNC(SYSDATE-1);
BEGIN
M_FILEPATH := 'G:\ILIEN_ALERTMAILS';
LV_FILENAME := 'Rahultest' || TO_CHAR(SYSDATE, 'YYYY-MM-DD-HHMMSS') ||'.xls';
F1 := UTL_FILE.FOPEN(M_FILEPATH, LV_FILENAME, 'W', 32767);
UTL_FILE.PUT(F1,
'STCODE' || CHR(9) || 'CORPNUM' || CHR(9) || 'REGNTYP' ||
CHR(9) || 'CORPTYP' || CHR(9) || 'INCDATE' || CHR(9) ||
'INCSTATE' || CHR(9) || 'PURPOSE' || CHR(9) || 'CORPNAME' ||
CHR(9) || 'ADDRESS_TYPE' || CHR(9) || 'ADDR' || CHR(9) ||
'CITY' || CHR(9) || 'STATE' || CHR(9) || 'ZIP' || CHR(9) ||
'RUN_DATE' || CHR(9) || 'COMMENTS' || CHR(13));
FOR C1_CNR IN C1 LOOP
UTL_FILE.PUT(F1,
C1_CNR.STCODE || CHR(9) || C1_CNR.CORPNUM || CHR(9) ||
C1_CNR.REGNTYP || CHR(9) || C1_CNR.CORPTYP || CHR(9) ||
C1_CNR.INCDATE || CHR(9) || C1_CNR.INCSTATE || CHR(9) ||
C1_CNR.PURPOSE || CHR(9) || C1_CNR.CORPNAME || CHR(9) ||
C1_CNR.ADDRESS_TYPE || CHR(9) || C1_CNR.ADDR || CHR(9) ||
C1_CNR.CITY || CHR(9) || C1_CNR.STATE || CHR(9) ||
C1_CNR.ZIP || CHR(9) || C1_CNR.RUN_DATE || CHR(9) ||
C1_CNR.COMMENTS || CHR(13));
END LOOP;
UTL_FILE.FCLOSE(F1);
END PRM_CNR_EXCEL;

This SP is working fine but if there are around 1000 records in the table SP is throwing "ORA 29285 - file write error"

I googled this error but unable to find the answer how i can force the SP to generate all the records in excel.

please let me know how I can revise this SP to generate excel file containing all the records from the table.

Oracle - 9i 9.2.0.5.0
OS - Win 2003

Thankx,
Rahul.

[Updated on: Thu, 07 May 2009 05:47]

Report message to a moderator

Re: Excel Report From SP - ORA 29285 - file write error [message #402029 is a reply to message #402016] Thu, 07 May 2009 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-29285: file write error
 *Cause:  Failed to write to, flush, or close a file.
 *Action: Verify that the file exists, that it is accessible, and that
          it is open in write or append mode.

Regards
Michel
Re: Excel Report From SP - ORA 29285 - file write error [message #402056 is a reply to message #402016] Thu, 07 May 2009 07:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I don't have time to decipher your sloppy unformatted mess, but one thing I can see is that you are not using a directory object, rather you are using directory names. That's not how it is done. Search the documentation for CREATE DIRECTORY.
Re: Excel Report From SP - ORA 29285 - file write error [message #402266 is a reply to message #402016] Fri, 08 May 2009 04:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try using UTL_FILE.PUT_LINE rather than UTL_FILE.PUT
Previous Topic: Oracle_XML
Next Topic: Instead of CONTINUE
Goto Forum:
  


Current Time: Fri Dec 02 22:58:44 CST 2016

Total time taken to generate the page: 0.08920 seconds