Re: File creation problem on production using procedure triggered by a dbms_scheduler
From: ddf <oratune_at_msn.com>
Date: Wed, 10 Nov 2010 06:03:40 -0800 (PST)
Message-ID: <82ac9358-76da-40b1-a162-f6941d174085_at_37g2000prx.googlegroups.com>
On Nov 10, 6:25 am, dhanlakshmi <dbpati..._at_gmail.com> wrote:
> 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
Date: Wed, 10 Nov 2010 06:03:40 -0800 (PST)
Message-ID: <82ac9358-76da-40b1-a162-f6941d174085_at_37g2000prx.googlegroups.com>
On Nov 10, 6:25 am, dhanlakshmi <dbpati..._at_gmail.com> wrote:
> 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
The utl_file_dir parameter is deprecated in 10g; you should be creating directories in Oracle and passing a directory name to utl_file rather than a path:
create directory xls_dir as '/oracle/tata_aig_life/websales/dnld'; grant read, write on directory xls_dir to ...;
then pass XLS_DIR to utl_file. I'm not saying what you've done can't work but you do need the utl_file_dir parameter set to /oracle/ tata_aig_life/websales/dnld before Oracle can actually read from and write to this location.
David Fitzjarrell Received on Wed Nov 10 2010 - 08:03:40 CST