Oracle triggers error are not captured while using ADODB

Question

I have and application which uses Adodb to insert data in Oracle table(customers database).

1. Data is successfully inserted if there are no errors.
2. If there is any error like invalid datatype etc. Error is raised and captured by my application and dumped in log gile.
3. My customer has written their own triggers on this particular table. When a record is inserted few other checking are done be fore the data insertion

Now all fine until now.

But recently we found that many a times data is not inserted in the oracle table.

1. When checked in log file no error was found.
2. Then I logged the query which was executed.
3. Copied the query to oracle Sql prompt and executed it gave error of trigger.

My Issue is

1. Customer is not ready to share the details of trigger.
2. Error is not raised while inserting to oracle table so we are not able to log it or take any action.
3. The same qry when executed directly in oracle the trigger errors are show.

Help needed for

1. Why the error is not raised in ADODB
2. Do I have to inform customer to implement any error raising
3. Anything that you can suggest for resolving the issue

I have 0% to 10% knowledge of Oracle

Answer 1

I've never used adodb ( and I assume that is what you are using, not ADO.NET?).. But, a quick look at its references leads to this question.. Are you actually checking the return state of your query?

$ok = $DB->Execute("update atable set aval = 0");
if (!$ok) mylogerr($DB->ErrorMsg());

Answer 2

"Copied the query to oracle Sql prompt and executed it gave error of trigger." Since the ADO session doesn't report an error, it may be that the error from the trigger is misleading. It may simply be a check on the lines of "Hey, you are not allowed to insert into this table except though the application".

"Error is not raised while inserting to oracle table so we are not able to log it or take any action." If the error isn't raised at the time of insert, it MAY be raised at the time of committing. Deferred constraints and materialized views could give this.

Hypothetically, I could reproduce your experience as follows: 1. Create a table tab_a with a deferrable constraint initially deferred (eg val_a > 10) 2. The ADO session inserts a row violating the constraint but it dooesn't error because the constraint is deferred 3. The commit happens and the constraint violation exception fires and the transaction is rolled back instead of being committed.

So see if you are catering for the possibility of an error in the commit.

It may also be something else later in the transaction which results in a rollback of the whole transaction (eg a deadlock). Session tracing would be good. Failing that, look into a SERVERERROR trigger on the user to log the error (eg in a file, so it won't be rolled back)

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2153530