how to view error

[elaine3839]

Hi,

During runtime, how can I know there is error happening during sql statement (insert/update/delete etc)? i.e. where should I look at the error message so I know what's going on?

Thanks.

[sree_sri]

Hi Elaine3839,
Could you please give us a example? Because in plsql we can pull the error by specifying in EXEMPTION, If it is not plsql then give us a sql(data) example.

[vbaskar]

if u r taking abt having it in the pl.sql block then..

after an DML statements u can do the following..

if sql%notfound then

else

end if;

[bmycroft]

I think you meant EXCEPTION!!! :-)

Ok Elaine3839 , what you can do is as mentioned above..

BEGIN
... code
... code
... code
EXCEPTION
WHEN named_exception_youchoose THEN
..handle it how you want.
WHEN OTHERS
..use SQLCODE and SQLERRM to get info and tell the user;
END;

[elaine3839]

No, I'm not talking about pl/sql.

What I am asking is that if the sql statement from application level generate a sql error, can we just look at the runtime database and figure out there is error being logged, what the error is etc. i.e. how to catch application level db error from db server side. Make sense?

Thanks for your help.

[sambavan]

IF it was some major corruption type of errors, they would be caught in the alert_SID.log file. If it were to be related to connection then it would be caught in the listerner log. If you are executing an application, then you could look for the errors in that applications' log(s).

If you are on a unix system, then you could write a script to scan these logs for the error and then report them to you via a mail. So, in short It Depends

Sam

[elaine3839]

Thanks Sam! Your answer is what I am looking for...

Just to make sure, if the error happens when running an application, then we can only find the error from the application's log instead of anywhere within database server(assuming this is not the corruption error).

I know when we handle exception in SQL Server, we could specify 'With Log' option which will log the user specified exception into sql log. Any similar syntax/utility available in Oracle?

Thanks a lot.

[sambavan]

Yes you could use the exception table to log your exceptions. This would only log the exceptions related to violation of intergrity constraints. To use this you would have to run the $ORACLE_HOME/rdbms/admin/utlexcpt.sql script.

On the other hand if you would like to view the errors on the current user's schema, you could use the user_errors, or dba_errors.

Another way to do this is that you can create a table and the when you encounter an error, you could insert the owner, error and the time into that log table and then you could later query that to find the information on the error. This is feasible if you have control on the application.

Hope this would put you in the right path
Sam