4 Replies Latest reply: Jan 12, 2014 8:03 PM by moslee RSS

    Ask about UTL_FILE in Stored Procedure

    moslee

      Hi

       

      I was trying to do an insert in a table which then trigger a stored procedure to write the inserted data to a text file. However, I have some issues here in the Stored Procedure. Thanks for trying to help... [Insert -> Table -> Trigger -> Stored Procedure -> Text File]

       

      ----User will execute this sql

      ----insert into tester.test_table values ('ab');

      CREATE TABLE TESTER.TEST_TABLE

      (

        LINECODE  VARCHAR2(2 BYTE)

      )

       

      ----Just for testing purpose

      CREATE TABLE TESTER.TEST_REC_TABLE

      (

        LINECODE  VARCHAR2(2 BYTE)

      )

      CREATE OR REPLACE TRIGGER TESTER.TRIGGER_AUDIT

      AFTER INSERT OR UPDATE

      ON TESTER.TEST_TABLE REFERENCING OLD AS OLD NEW AS NEW

      FOR EACH ROW

      DECLARE

        sLineCode VARCHAR2(2);

      BEGIN

         sLineCode := '';

          IF UPDATING THEN     

            sLineCode := :NEW.LINECODE;

            TRIGGER_PACKAGE.WRITE_FILE(sLineCode);

          END IF;  

          IF INSERTING THEN

            sLineCode := :NEW.LineCode;

            TRIGGER_PACKAGE.WRITE_FILE(sLineCode);

          END IF;

      EXCEPTION

          WHEN others then null;

      END TRIGGER_AUDIT;

      /

      CREATE OR REPLACE PACKAGE BODY TESTER.TRIGGER_PACKAGE

      is

         procedure WRITE_FILE(in_LineCode in varchar2)

         is

           sLineCode varchar2(2);  

           v_FileHandle UTL_FILE.FILE_TYPE;

        

      ----some checking of the input data for NULL 

         begin  

          if ((rtrim(in_LineCode) IS NULL) or (in_LineCode is null)) then

              sLineCode := 'XX';

          else

              sLineCode := in_LineCode;          -------->PROBLEM HERE?

          end if;

          if (length(sLineCode) > 2) then

            sLineCode := substr(sLineCode,1,2);

          end if;

            

          Insert into TEST_REC_TABLE (LineCode) values (sLineCode); ----insert data to another table for testing

         

          EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY FILEDIR AS ' || '''' || 'c:\' || '''';

          v_FileHandle := UTL_FILE.FOPEN('FILEDIR','Test_Table.txt','w'); ----write to text file in C:\Test_Table.txt

          UTL_FILE.PUT_LINE(v_FileHandle, 'Inserted ' || sLineCode || ' @ ' || TO_CHAR(SYSDATE,'DD-MM-YYYY HH:MI:SS AM'));

          UTL_FILE.FCLOSE(v_FileHandle);           

               

         exception

            when others then null;

         end;

      end TRIGGER_PACKAGE;

      /

      1) The problem I faced is that when I perform INSERT sql for the new data, it can be successfully be inserted into both tables TEST_REC_TABLE and TEST_TABLE, but it just does not write to the text file.

      2) However, when I just execute the procedure I will get XX for the input data as it is NULL. This XX data will then be seen in TEST_REC_TABLE as "XX" record and Test_Table.txt as "Inserted XX @ 10-01-2014 04:56:06 PM". UTL_FILE did write to textfile so my guess is that it could be due to my poor understanding of logic in the checking of the input data for NULL. Tried but I am still clueless, thanks for pointing out the issue.

        • 1. Re: Ask about UTL_FILE in Stored Procedure
          _Karthick_

          Few things

           

          1.

           

          INSERT statement is transactional but Writing to a file is not. So don't use trigger to write to a file. Because if the INSERT statement is rollbacked in the subsequent transaction you are still left with the data written to the file.

           

          2.

           

          EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY FILEDIR AS ' || '''' || 'c:\' || '''';

           

          Why are you creating directory object dynamically? When you package code runs for the second time its will error out saying object already exist.

           

          3.

           

          exception

             when others then null;

          end;


          This is the root cause for all your confusing. This is completely wrong. It suppresses all the error. You will never know what's happening.

          • 2. Re: Ask about UTL_FILE in Stored Procedure
            moslee

            Hi Thanks for pointing out...

            1) I'm just testing and learning here with trigger insert...

            2) How should I do then?

            3) How should I do then? Use this ?

            EXCEPTION

                  WHEN OTHERS THEN

                       DBMS_OUTPUT.PUT_LINE

                            ('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);

                       NULL;

            • 3. Re: Ask about UTL_FILE in Stored Procedure
              _Karthick_

              > 1) I'm just testing and learning here with trigger insert...

               

              Good. In that case its just a wrong choice that you have decided to write to a file from trigger. Oracle base is a good site. They have examples too. Please check that ORACLE-BASE - Database Triggers Overview.

               

              > 2) How should I do then?

               

              Just create the object one time

               

              CREATE OR REPLACE DIRECTORY FILEDIR AS <path>

               

              The path should be a location in your server where oracle is installed.

               

              > 3) How should I do then? Use this ?

              EXCEPTION

                    WHEN OTHERS THEN

                         DBMS_OUTPUT.PUT_LINE

                              ('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);

                         NULL;

               

              Don't do any thing. Just remove the exception handling. Don't handle unknown exceptions. If at all you want to handle them for logging purpose RAISE it at the end like this.

               

              exception

                when others then

                  <log your messages>

                  raise;

               

              And about exception handling one of the forum member BluShadow has written a nice article PL/SQL 101 : Exception Handling. That would be a nice read.

              • 4. Re: Ask about UTL_FILE in Stored Procedure
                moslee

                Wonderful advice! Many thanks! Learned alot more!