Oracle - Create and Write a file with Oracle trigger

Hello Guys,

I had a scenario where I wanted to write a file which will have data that was inserted into the Oracle tables.

Problem Statement -: Create and Write a file through a Trigger.

The following code shows how you can do that.

Prerequisites !

CREATE DIRECTORY DEFINED_DIRECTORYAS 'C:/Files/';
GRANT READ, WRITE ON DIRECTORY DEFINED_DIRECTORY TO HR;

Here I have used the user HR, it may be different in your case, so put it accordingly.

Now,
Create a Table - :

CREATE TABLE WRITE_TEST
(
id INT,
sDATA varchar2(50)
)
;
/

Now, We will create a Stored Procedure which should be called from a trigger. This Procedure will have the file generation and write code.

CREATE OR REPLACE PROCEDURE WRITE_TEST_PROC AS

vOut        UTL_FILE.FILE_TYPE;

BEGIN
    vOut := UTL_FILE.FOPEN( 'DEFINED_DIRECTORY', 'filename.txt', 'W' );

FOR cur IN (
select id,sDATA from WRITE_TEST
-- select '1' , '2' from Dual  -- this will write 1 & 2 in the file
) LOOP
    UTL_FILE.PUT( vOut, cur.id||cur.sDATA);
  --UTL_FILE.PUT( vOut, ''); this will keep the file blank (Just create a blank file when inserted on a table WRITE_TEST)

    UTL_FILE.NEW_LINE( vOut );
    END LOOP;
    UTL_FILE.FCLOSE( vOut );

END;
/

Here I have used oracle's default UTL_FILE package.

Here I am just writing contents of the table WRITE_TEST into a file record-by-record with a for loop. Contents are id & sDATA columns concatenated ( || ).
Just compile the Stored Procedure , currently there is no data in the WRITE_TEST table.

Now Lets create a trigger for the table WRITE_TEST.

CREATE OR REPLACE TRIGGER WRITE_TEST_TRIG
 BEFORE
  INSERT
 ON WRITE_TEST
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE


BEGIN

WRITE_TEST_PROC();
-- Here I am calling the stored procedure that we previously created. This means, every record that tries to get inserted, will call this procedure. 

END;
/

Testing time....  YaY !!!

Now Lets test it,

Now lets insert data into the table. Dont commit yet. I am inserting some random values into the column sDATA, you may put your required data there.

INSERT INTO WRITE_TEST (id,sDATA) VALUES (1,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO WRITE_TEST (id,sDATA) VALUES (2,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO WRITE_TEST (id,sDATA) VALUES (3,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO WRITE_TEST (id,sDATA) VALUES (4,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO WRITE_TEST (id,sDATA) VALUES (5,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));

Dont commit yet. Trigger is not yet Triggered

select * from WRITE_TEST;

Now commit

commit;


Now check the file in the location with the data in it.

You Got it ? Is the File generated ? Hell Yeah !
#WoW #winner :)
Done.
Dont forget to Thank ;)
Please like and share the link.
Liking and Sharing is the best Donation :)

Chao !