File creation problem on production using procedure triggered by a dbms_scheduler
Date: Wed, 10 Nov 2010 03:25:59 -0800 (PST)
Message-ID: <04c893b3-3601-4005-9879-cee99c7d5f8b_at_s11g2000prs.googlegroups.com>
This is regarding a problem we are facing during report(.xls) creation which is done using a procedure triggered by a job run.
The report file(.xls) file is not getting created when the job(using dbms_scheduler) calls.
The procedure uses utl_file to create an .xls file
We have a folder on the path /oracle/tata_aig_life/websales/dnld . This folder( dnld ) has the all the priviliges as below:
drwxrwxrwx
We have a job scheduler as below which in turn triggers a procedure(please check the code attachment for the scheduler and the procedure).
-----------------------------------------JOB CODE--------------------------------------BEGIN
- Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => 'OWS_INCOMPLETE_APP_REP_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN OWS_INCOMPLETE_APP_REP_PROC; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=23;BYMINUTE=59;BYSECOND=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job to inactivate the quotes and customer');
END;
CREATE OR REPLACE PROCEDURE OWS_INCOM_REP_TILL_DTE_PROC IS
fileHandle UTL_FILE.FILE_TYPE;
vc_err_msg VARCHAR2(4000);
e_skip_file_creation EXCEPTION;
v_strDataLine VARCHAR2(30000);
vc_programname VARCHAR2(50) := 'Gen_Report_Outputfile_N';
TYPE cursor_sql_id IS REF CURSOR;
cur_select_data cursor_sql_id;
pi_strPathName VARCHAR2(100); pi_strFileName VARCHAR2(25); v_statement VARCHAR2(32767); v_header VARCHAR2(1000);
po_error_code NUMBER;
po_error_msg VARCHAR2(1000);
BEGIN
po_error_code := SQLCODE; po_error_msg := SQLERRM; pi_strPathName := 'DNLD'; pi_strFileName := 'INCOMP_APP_REPT_TD.xls';
UTL_FILE.FREMOVE(pi_strPathName, pi_strFileName); -- Added on 28/10/2010 to remove the existing file before being created
v_statement := 'select sysdate from dual';
- Open file to create
BEGIN
- max_linesize, The maximum NUMBER OF characters per line, INCLUDING the newline CHARACTER, FOR this FILE. MINIMUM IS 1, maximum IS 32767 v_header := 'today date';
fileHandle := UTL_FILE.FOPEN(pi_strPathName, pi_strFileName, 'W');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
vc_err_msg := 'Error While Creating File : Path - ' ||
pi_strPathName || ', Output File: ' ||
pi_strFileName || ' ' ||
SQLERRM;
-- Code Added on 28/10/2010
po_error_code := SQLCODE;
po_error_msg := SQLERRM;
INSERT INTO OWS_EXCEPTION_HANDLING
(OWS_ERROR_CODE, OWS_ERROR_VALUES, OWS_DATE,
OWS_FUNCTIONALITY)
VALUES
(po_error_code,
po_error_msg,
SYSDATE,
'OWS_INCOM_REP_TILL_DTE_PROC');
-- Code Added on 28/10/2010 Ends Here
RAISE e_skip_file_creation;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
vc_err_msg := 'Error While Creating File : Path - ' ||
pi_strPathName || ', Output File: ' ||
pi_strFileName || ' ' ||
SQLERRM;
-- Code Added on 28/10/2010
po_error_code := SQLCODE;
po_error_msg := SQLERRM;
INSERT INTO OWS_EXCEPTION_HANDLING
(OWS_ERROR_CODE, OWS_ERROR_VALUES, OWS_DATE,
OWS_FUNCTIONALITY)
VALUES
(po_error_code,
po_error_msg,
SYSDATE,
'OWS_INCOM_REP_TILL_DTE_PROC');
-- Code Added on 28/10/2010 Ends Here
RAISE e_skip_file_creation;
WHEN UTL_FILE.READ_ERROR THEN
vc_err_msg := 'Error While Creating File : Path - ' ||
pi_strPathName || ', Output File: ' ||
pi_strFileName || ' ' ||
SQLERRM;
RAISE e_skip_file_creation;
-- Code Added on 28/10/2010
po_error_code := SQLCODE;
po_error_msg := SQLERRM;
INSERT INTO OWS_EXCEPTION_HANDLING
(OWS_ERROR_CODE, OWS_ERROR_VALUES, OWS_DATE,
OWS_FUNCTIONALITY)
VALUES
(po_error_code,
po_error_msg,
SYSDATE,
'OWS_INCOM_REP_TILL_DTE_PROC');
-- Code Added on 28/10/2010 Ends Here
WHEN OTHERS THEN
vc_err_msg := 'Error While Creating File : Path - ' ||
pi_strPathName || ', Output File: ' ||
pi_strFileName || ' ' ||
SQLERRM;
-- Code Added on 28/10/2010
po_error_code := SQLCODE;
po_error_msg := SQLERRM;
INSERT INTO OWS_EXCEPTION_HANDLING
(OWS_ERROR_CODE, OWS_ERROR_VALUES, OWS_DATE,
OWS_FUNCTIONALITY)
VALUES
(po_error_code,
po_error_msg,
SYSDATE,
'OWS_INCOM_REP_TILL_DTE_PROC');
-- Code Added on 28/10/2010 Ends Here
RAISE e_skip_file_creation;
END;
UTL_FILE.PUT_LINE(fileHandle, v_header); OPEN cur_select_data FOR v_statement;
LOOP
FETCH cur_select_data
INTO v_strDataLine;
EXIT WHEN cur_select_data%NOTFOUND;
BEGIN
UTL_FILE.PUT_LINE(fileHandle, v_strDataLine); EXCEPTION
WHEN OTHERS THEN
vc_err_msg := 'Error While Creating File : Path - ' ||
pi_strPathName || ', Output File: ' ||
pi_strFileName || ' ' ||
SQLERRM;
-- Code Added on 28/10/2010
po_error_code := SQLCODE;
po_error_msg := SQLERRM;
INSERT INTO OWS_EXCEPTION_HANDLING
(OWS_ERROR_CODE, OWS_ERROR_VALUES, OWS_DATE,
OWS_FUNCTIONALITY)
VALUES
(po_error_code,
po_error_msg,
SYSDATE,
'OWS_INCOM_REP_TILL_DTE_PROC');
-- Code Added on 28/10/2010 Ends Here
RAISE e_skip_file_creation;
END;
END LOOP;
CLOSE cur_select_data;
UTL_FILE.FCLOSE(fileHandle);
EXCEPTION
WHEN e_skip_file_creation THEN
DBMS_OUTPUT.PUT_LINE('vc_err_msg' || vc_err_msg);
DBMS_OUTPUT.PUT_LINE('SQLERRM' || SQLERRM);
UTL_FILE.FCLOSE(fileHandle);
- Code Added on 28/10/2010 po_error_code := SQLCODE; po_error_msg := SQLERRM;
INSERT INTO OWS_EXCEPTION_HANDLING
(OWS_ERROR_CODE, OWS_ERROR_VALUES, OWS_DATE, OWS_FUNCTIONALITY)
VALUES
(po_error_code,
po_error_msg,
SYSDATE,
'OWS_INCOM_REP_TILL_DTE_PROC');
- Code Added on 28/10/2010 Ends Here WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERRM' || SQLERRM); vc_err_msg := 'Error While Creating File : Path - ' || pi_strPathName || ', Output File: ' || pi_strFileName; UTL_FILE.FCLOSE(fileHandle);
- Code Added on 28/10/2010 po_error_code := SQLCODE; po_error_msg := SQLERRM;
INSERT INTO OWS_EXCEPTION_HANDLING
(OWS_ERROR_CODE, OWS_ERROR_VALUES, OWS_DATE, OWS_FUNCTIONALITY)
VALUES
(po_error_code,
po_error_msg,
SYSDATE,
'OWS_INCOM_REP_TILL_DTE_PROC');
- Code Added on 28/10/2010 Ends Here
END;
In the procedure we first remove the file(.xls) created in the path and then recreate a new .xls file.
The reason for first removing the file and recreating is that the existing file is not getting updated with a new file when the job is run.
We capture the exceptions in a table.
In the table the following exception is logged :ORA-29283: invalid file operation
The job is triggered and the files are created on the path mentioned in the procedure on the UAT Environment..
Also the files are created when we manually run the same procedure in the path mentioned above.
The Oracle version is
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
Production
The OS flavour is
SunOS otlpsr5cora01 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire-
V490 -------
Please help us with the issue..
Also do let me know in case you need any other details..
Regards Received on Wed Nov 10 2010 - 05:25:59 CST