9 Replies Latest reply on Jun 1, 2012 12:38 PM by Solomon Yakobson

    writing a log file form a plsql package

    896976
      I have this procedure I want to send a log file to a directory in unix
       
      PROCEDURE sortest_best_SAT_scores (
          p_insert_err_code      OUT VARCHAR2,
         p_ora_err_code         OUT NUMBER,
         p_ora_err_msg          OUT VARCHAR2)
       IS
         e_insert_zero    EXCEPTION;
         e_update_zero    EXCEPTION;
         v_count          NUMBER := 0;
         v_insert_count   NUMBER;
         v_out_path       VARCHAR2 (40) := '/u07/dev';
         v_file_handle    UTL_FILE.file_type;
         v_out_file       VARCHAR2 (140)
            :=  insert_best_test_scores' || TO_CHAR (SYSDATE, 'YYYYMMDDHH');
       
      BEGIN
         UTL_FILE.fclose_all;
         v_file_handle := UTL_FILE.fopen (v_out_path, v_out_file, 'a');
         UTL_FILE.put_line (v_file_handle,
                            CHR (10) || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
         UTL_FILE.put_line (v_file_handle, 'sortest_best_ACT_scores');
       
         SELECT COUNT (sortest_pidm)
           INTO v_count
           FROM saturn.sortest
          WHERE TO_CHAR (sortest_activity_date, 'MM/DD/RRRR') =
                   TO_CHAR (SYSDATE, 'MM/DD/RRRR')
                AND sortest_tesc_code IN ('S05', 'S06', 'S07');
      
         UTL_FILE.put_line (
            v_file_handle,
            'Records inserted for the appl_srbrecr_insert' || v_count);
      
         IF v_count = 0
         THEN
            RAISE e_insert_zero;
         END IF;
      
         p_insert_err_code := '0';
         UTL_FILE.put_line (v_file_handle, 'Successful Completion.');
      It is not writing a file to the directory
      what kind of permissions do I need in the directory?
      Any suggestions will be appreciated!
        • 1. Re: writing a log file form a plsql package
          sb92075
          893973 wrote:
          I have this procedure I want to send a log file to a directory in unix
          It is not writing a file to the directory
          what kind of permissions do I need in the directory?
          Any suggestions will be appreciated!
          chmod 777 -R <pathname>
          • 2. Re: writing a log file form a plsql package
            896976
            Here is another question this is how my dba set up the permissions directory

            I have set up a new group of mounts to service this request

            I have created a new mount point “/u07/<zone>” mounted on both <zone>-oraerp-a and <zone>-oraerp-b in addition;

            on prod-erpsoft the same mounts are mounted to /u07/<zone>

            let me know if you have any questions

            [prod-oracle@prod-erpsoft dev]$ pwd
            /u07/dev

            or just
            do I have to specify the all path in:
             
             v_out_path       VARCHAR2 (40) := '/u07/dev';
            • 3. Re: writing a log file form a plsql package
              sb92075
              893973 wrote:

              let me know if you have any questions
              I don't have any questions or problems.
              Do you?
              • 4. Re: writing a log file form a plsql package
                Solomon Yakobson
                what kind of permissions do I need in the directory?
                You didn't post complete procedure, so I have a feeling it has something like:
                WHEN OTHERS
                and as a result all errors are masked. Therefore you do not see error message and there is no file. If there is WHEN OTHERS - remove it. Now you are using deprecated way to specify UTL_FILE directory. You specify OS directory path instead of using Oracle directory object. I suggest you change that. To keep using OS directory path you MUST have init.ora parameter UTL_FILE_DIR listing all OS directories UTL_FILE is allowed to use. Keep in mind, UTL_FILE is database server side tool, therefore it can only work with directories accessible from database server. And again, since it is database server side tool it runs under oracle id and therefore oracle id must have proper permissions to directory.

                SY.
                • 5. Re: writing a log file form a plsql package
                  896976
                  I remove the hen others
                  I got this error

                  ORA-29280: invalid directory path
                  ORA-06512: at "SYS.UTL_FILE", line 41
                  ORA-06512: at "SYS.UTL_FILE", line 478
                  ORA-06512: at
                  here is directory
                  [prod-oracle@prod-erpsoft dev]$ pwd
                  /u07/dev
                  here is what I have in
                   
                   v_out_path       VARCHAR2 (40) := '/u07/dev';
                  do I need to put specify prod-oracle@prod-erpsoft
                  or the dba needs to do some kind of set up, I know you mentioned the init.ora
                  excuse my ignorance
                  Thank you
                  • 6. Re: writing a log file form a plsql package
                    sb92075
                    post results from following SQL

                    SELECT * FROM V$VERSION;
                    • 7. Re: writing a log file form a plsql package
                      Mark Malakanov (user11181920)
                      you should give read and execute permissions on /u07 to oracle execution account.
                      chmod 777 /u07
                      or the dba needs to do some kind of set up
                      your DBA needs to do some kind of training
                      • 8. Re: writing a log file form a plsql package
                        ShankarViji
                        Hi Friend,

                        As the directory you are accessing is not a DB Server Directory, You need to add it as an entry to the
                        init.ora File in the DB Server as

                        utl_file_dir=/u07/dev

                        You can Check it via DB using the Paramter File as
                        SELECT *
                          FROM V$PARAMETER
                         WHERE NAME LIKE '%utl%';
                        The Unix Directory Path is Displayed in the v$paramter.

                        The Path of the init.ora File in Windows is : %ORACLE_HOME%\database on Windows

                        In Unix :init.ora is $ORACLE_HOME/dbs

                        And Login as root in Unix to give Permissions to /u07/dev

                        CHMOD 777 <DIR_PATH>

                        And, this can be used to READ Files from the Directory, But I am not sure of Writing Files to the Directory.


                        Thanks,
                        Shankar

                        Edited by: Shankar Viji on May 31, 2012 10:42 PM

                        Edited by: Shankar Viji on Jun 1, 2012 6:18 AM

                        Edited by: Shankar Viji on Jun 1, 2012 6:19 AM
                        • 9. Re: writing a log file form a plsql package
                          Solomon Yakobson
                          893973 wrote:
                          I remove the hen others
                          I got this error

                          ORA-29280: invalid directory path
                          As I already mentioned, you are using deprecated way to specify UTL_FILE directory. So create oracle directory:
                          CREATE DIRECTORY GIVE_IT_A_NAME AS '/u07/dev';
                          Make sure OS user oracle can create files in /u07/dev. Now change your code:
                          v_file_handle := UTL_FILE.fopen ('GIVE_IT_A_NAME', v_out_file, 'a');
                          SY.