Inside of stored procedure, is it possible to write a message to the standard log file? Trace goes to a separate file, but I would like to note something that isn't really an error in the main log file for the database. It can be handled without raising an exception, but it would be handy to have the information in the log for later use.
Is it possible to do something like:
log "Note this event happened."
Although there is no existing SQL statement or function to write directly to the server's message log, the UTL_FILE package could perhaps be used to achieve something similar. The documentation for this may be slightly out of date so here is an example that was found to work in 14.10.FC10:
-- Register the datablade if this has not already been done:
execute function sysbldprepare("excompat.1.2", "create");
-- Open a file /tmp/example.log. Note that the file must exist for the append
-- option. The value returned is of type utl_file_file_type and was 0 for this
-- example. It would be better to use a stored procedure to capture the return
-- value and use it in the subsequent procedure calls.
execute function utl_file_fopen("/tmp", "example.log", "a", 1024);
-- Write a line of text that will be terminated with a newline
execute procedure utl_file_put_line(0::utl_file_file_type, "line 1 of text");
-- Write a line that does not have a trailing newline
execute procedure utl_file_put(0::utl_file_file_type, "line 2 of text w/o NL");
-- Write text that takes up to 5 varchar arguments. Only %s formats are
-- permitted. The output is terminated with a newline.
execute procedure utl_file_putf(0::utl_file_file_type,
"formatted text 2: %s %s", 2, "3rd arg");
-- Close the file
execute procedure utl_file_fclose(0::utl_file_file_type);
-- An alternative to close all open files.
execute procedure utl_file_fclose_all();
If writing to the server's message log there is unlikely to be any synchronization between the write activities so concurrent writes to the file may result in mangled text.