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

Ask about UTL_FILE in Stored Procedure

moslee Newbie
Currently Being Moderated

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_Arp Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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_Arp Guru
    Currently Being Moderated

    > 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 Newbie
    Currently Being Moderated

    Wonderful advice! Many thanks! Learned alot more!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points