-- notes:  
-- 1.	Stored Procedure to write the log files for the Lawson_etl process
-- 2.	Takees 3 input parameters -> filename, message to be written and Mode (read or write)
--	in which the log file is opened.
--	Output -> written to OS file.
-- 3.	init parameter utl_file_dir = c:report (in 8i) or
        create an oracle directory using create directory (9i) and file permsisions are given -->look docs
-- 4.	The Most common exceptions are handled, will add any more.
-- 5.	Initally the file is to be opened in Write mode ( to write the timestamp) 
--	If you want to append data into the same file open the file in apppend mode.
-- WHO=	rajendran

CREATE OR REPLACE PROCEDURE write_log
(
	fname	IN VARCHAR2 ,
	message IN VARCHAR2 ,
	mo	IN VARCHAR2 
)
IS 
log_file  UTL_FILE.FILE_TYPE;         -- file handle for the log file
BEGIN 
log_file := UTL_FILE.FOPEN('c:report',fname,mo);
UTL_FILE.PUTF(log_file,'%sn',message);
UTL_FILE.FCLOSE(log_file);
EXCEPTION
        WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('no_data_found');
	WHEN UTL_FILE.INVALID_PATH THEN
		DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
	WHEN UTL_FILE.WRITE_ERROR THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
      	WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('other stuff');
END; 
/
 
-------------------------------------------------------------------------------------------------------------------------
-- USAGE
-------------------------------------------------------------------------------------------------------------------------
SQL> ed
Wrote file afiedt.buf

  1  begin
  2  write_log('sampelfile.txt','hello world!','W');
  3  write_log('sampelfile.txt','hello world again!','A');
  4* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> get c:reportsampelfile.txt
  1  hello world!
  2* hello world again!
SQL>