Is there any log in database when my procedure/trigger or any database object error occur?
ex: When someone insert a table with trigger but something wrong happened in the trigger, can I get any error message such as from database system view or database trace/alert log?
I found some ora errors in alert log but it seems like only logging the error which object execute by database job.
If exceptions are handled (with EXCEPTION handling in PL/SQL), then the program is in full control of what is done with the error message, whether to log it or take some other action. The client may never see anything. Out of the box, Oracle will not log errors (unless they are core errors - bugs in Oracle - like ORA-00600 or ORA-07445 which immediately terminate your process; those will be in the alert log and Oracle will create special incident files). Regular programming exceptions don't make it into the alert log.
If however an exception is raised that is not handled all the way to the outermost code block and thus raises and exception to the client, then yes, you can log these generically without modifying code, but you have to set it up at the database/system level. Look into create a system trigger of type SERVERERROR ON DATABASE. Here's how to write one that is safe and will not impact application sessions if something goes wrong with the logging:
CREATE OR REPLACE TRIGGER SYSTEM.TR_SERVERERROR
AFTER SERVERERROR
ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF ora_dict_obj_owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') OR -- too dangerous, we could use components that are being modified and self-lock
SYS_CONTEXT('USERENV','SESSIONID') = 0 OR -- background processes
(SYS_CONTEXT('USERENV','SESSIONID') = 4294967295 AND (ora_dict_obj_owner = 'SYS' OR SYS_CONTEXT('USERENV','CLIENT_PROGRAM_NAME') NOT LIKE 'sqlplus%')) -- direct attached binaries, but allow sqlplus
THEN
NULL;
ELSE
EXECUTE IMMEDIATE 'BEGIN P_LOG_SERVER_ERROR; END;';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
You can then create your logging procedure (here P_LOG_SERVER_ERROR) that gathers info using various variables and functions, some of which are only available in a system trigger:
server_error(1) -- error code, must reverse the sign
server_error_msg(1) -- error message
dbms_utility.format_error_stack -- full error stack
dbms_utility.format_error_backtrace -- backtrace
dbms_utility.format_call_stack -- call stack
utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2)) -- erroring code unit
original_sql_txt(tab_sql) -- erroring SQL broken up into pieces, you have to reassemble
And of course lots of information about the session can be obtained from SYS_CONTEXT('USERENV',....) calls and by looking at v$session, v$process, v$session_connect_info, etc. Then insert the info you want into a special table for this purpose.
This approach is however intended for DBA use. In order to create a trigger like this you need the ADMINISTER DATABASE TRIGGER privilege which is too powerful (and dangerous) to give to non-DBAs. If you are not a DBA but also don't own the code you want to monitor, you might discuss with your DBA(s) having them create something like this for you. If you are the owner of the code in question, however, then you should program your own proper exception handling in your code and leave triggers and database-wide logging like this to the DBAs.