A trigger to output to a text file

[jack2008]

Hi,

I've been using Oracle 9i now and want to know if the following is possible. I've got Customer table. I want to create a trigger that, when a new customer is added or update, will output the changes to a text file. For example, if Mr Smith's address is created, it will pick up his name from the Customer table and output the results to a text file in a '0001, Mr Smith, 12, format or similar.

Any examples or guides would be very helpful!!

Thanks in advance.

[debasisdas]

Try to use the UTL_FILE package for the purpose.

[amitpatel66]

Hi,

I've been using Oracle 9i now and want to know if the following is possible. I've got Customer table. I want to create a trigger that, when a new customer is added or update, will output the changes to a text file. For example, if Mr Smith's address is created, it will pick up his name from the Customer table and output the results to a text file in a '0001, Mr Smith, 12, format or similar.

Any examples or guides would be very helpful!!

Thanks in advance.

You can use UTL_FILE as suggested in the above post by either implementing a UTL_FILE operation in the trigger itself or create a seperate procedure and call that procedure from the trigger.

[subashsavji]

may this coding will be helpful to you. modify it and write under trigger

 
CREATE OR REPLACE PROCEDURE sal_status
(p_filedir IN VARCHAR2,  p_filename IN VARCHAR2)
IS
 v_filehandle UTL_FILE.FILE_TYPE;
 CURSOR emp_info IS
   SELECT ename, sal, deptno
    FROM emp
    ORDER BY deptno;
 v_newdeptno emp.deptno%TYPE;
 v_olddeptno emp.deptno%TYPE := 0;
BEGIN
 v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,'w');
 UTL_FILE.PUTF (v_filehandle,'SALARY REPORT: GENERATED ON
                             %s\n', SYSDATE);
 UTL_FILE.NEW_LINE (v_filehandle);
 FOR v_emp_rec IN emp_info LOOP
  v_newdeptno := v_emp_rec.deptno;
IF v_newdeptno <> v_olddeptno THEN
      UTL_FILE.PUTF (v_filehandle, 'DEPARTMENT: %s\n',
                     v_emp_rec.deptno);
    END IF;
    UTL_FILE.PUTF (v_filehandle,'  EMPLOYEE: %s earns: %s\n',
                   v_emp_rec.ename, v_emp_rec.sal);
    v_olddeptno := v_newdeptno;
  END LOOP;
  UTL_FILE.PUT_LINE (v_filehandle, '*** END OF REPORT ***');
  UTL_FILE.FCLOSE (v_filehandle);
EXCEPTION
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR (-20001, 'Invalid File.');
  WHEN UTL_FILE.WRITE_ERROR THEN
   RAISE_APPLICATION_ERROR (-20002, 'Unable to write to
                                     file');
END sal_status;
/
 
create OR REPLACE DIRECTORY emp_dir as 'c:\';
/
execute sal_status('EMP_DIR','YY.TXT'); 
/
execute sal_status('EMP_DIR','C:\YYY.DOC');  -- ONLY CREATES UNDER THE C:\ DRIVE
                                                                                                                                                                                                                             '