-- Simple debug output (logging) procedure that can be called from triggers
-- to write to an OS file.
-- Copyright (c) 2004, Caleb.com

-- Must create a directory (in oracle) first
CREATE OR REPLACE DIRECTORY debug AS '/home/oracle';

-- Simple procedure to append a line to OS file
CREATE OR REPLACE PROCEDURE debug1(p_str IN VARCHAR2)
IS
   v_file    UTL_FILE.FILE_TYPE;  -- File handle type
BEGIN
   -- Open file for APPEND, return file handle
   v_file := UTL_FILE.FOPEN( 'DEBUG', 'debug.txt', 'a', 1024 );
   -- Write one line
   UTL_FILE.PUT_LINE( v_file, p_str, TRUE );
   -- Close the file
   UTL_FILE.FCLOSE( v_file );
END;
/