Re: File creation problem on production using procedure triggered by a dbms_scheduler

> 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

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