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

On Nov 10, 6:25*am, dhanlakshmi <dbpati... (AT) gmail (DOT) com> wrote:
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

ddf <oratune (AT) msn (DOT) com> writes:

I think David is on the right track. However, you also state that the
procedure runs if it is called directly in the production environment,
but not if called via the scheduler.

My suspicion is very much a permissions/grant problem. A couple of things to
note

1. The permissions of all the directories in the path are relevant. for
example, if you have a path

/a/b/c/d/e

and directory c does not have read and execute permission, then
everything from that point down is invisible. Setting the permissions
on, for example, 'e' won't help as you won't get there.

Get the dba to login as the user that the scheduler runs as and see if
they can cd to te target directory. Then see if they can 'touch
filename.xls'. If they can't do either, then, there is a file permission
problem somewhere in the path. If they can, then the problem is likely a
mising grant in the database.

BTW, the permissions you have set at present are VERY bad. Anyone or any
process can write/read/delete or whatever. Not good, especially if this
system has a public web interface. You mention you have a UAT/DEV
environment. Lock down to the minimum file permissions you need in that
environment. DO NOT experiment or do random shots in the dark in your
production environment.

BTW, the fact you say this all works in dev/uat, but not in production
means one of two things.

1. You have forgotten to promote some config/change from your uat
environment to your production environment

2. Your uat and prod environments are not the same.

Both indicate you have weaknesses or failures in your configuration and
change management.

Given that when the procedure is run by the schema owner, it executes
and the file is created, but it fails when run by the scheduler, my
guess would be that you have not given the appropriate grants to the
scheduler to enable it to write to the directory in the prod
environment. Note, this is a database grant problem and not an OS file
system permission problem. As you said it works in uat, the first
diagnostic step would be to find out how the grants differ between the
two environments.

David's point about using directory objects is also very important.
Using these objects give you lots more flexibility and unlike the old
mechanism, does not require global oracle parameter changes i.e. restart
after a change and is more secure as it gives more fine grained control.

Tim


--
tcross (at) rapttech dot com dot au