Board index » delphi » No exception raised on error in Oracle Trigger

No exception raised on error in Oracle Trigger

Using Delphi 5 Enterprise, BDE 5.01 and Oracle 8i, I'm trying to
perform a tablecopy between two Oracle tables using a TQuery component
and insert statements.

There are about 8000 records to be copied, and during the process
there are no exceptions raised. But the destination-table only has
7000 records in it after the process. The counter tells me that there
are 8000 succesfull inserts. At first sight, this seems impossible,
but using SQL Plus I found out that a trigger causes this strange
behaviour. In some cases [about 1000 :-) ], the trigger produces an
error, which is not recognized by Delphi. Because of the error in the
trigger, the insert statement failes, but Delphi thinks everything is
OK.

Is there a way to display this server side error in Delphi?

[this piece of code is simplified, but shows the method used]

-=-=-
while not qrySelect.EoF do
begin
  qryInsert.SQL.Text :=
                'insert into TABLE (FieldA, FieldB, FieldC) values (1,
2, 3)';
  try
    qryInsert.ExecSQL;
    Inc(GoodCopy);
  except
    ShowMessage('Error');
  end;
  qrySelect.Next;
end;
-=-=-

after the process: GoodCopy => 8000; select count(*) from TABLE =>
7000 and no
'Error' messages have been shown!

I hope somebody can give me a hint on this problem!

Thanks in advance, Ries van der Velden

 

Re:No exception raised on error in Oracle Trigger


rvandervel...@bergler.nl (Ries van der Velden) wrote in message <news:33ae5b5b.0111271320.599b90c0@posting.google.com>...
Quote

> Because of the error in the
> trigger, the insert statement failes, but Delphi thinks everything is
> OK.

> Is there a way to display this server side error in Delphi?

> I hope somebody can give me a hint on this problem!

This is the text in trigger for Centura SQLBase

CREATE TRIGGER SYSADM.SET_RES BEFORE INSERT ON SYSADM.RES
(EXECUTE INLINE (RES.ID_RES)
PROCEDURE: RES_SET STATIC
PARAMETERS
 RECEIVE NUMBER: sID_RES
ACTIONS
 call sqlimmediate('select max(ID_RES)+1 from SYSADM.RES into
:sID_RES')
 return 0)
for each row

return 0 in the text above is a signal that insertion was successful.
if there was return some_value <> 0 then insertion produced an error
and this error is catched by delphi in try ..except block.
So in your trigger for you must use construction like
if (success...) then raise_application_error

Re:No exception raised on error in Oracle Trigger


Quote
gil...@mail.ru (curious) wrote in message <news:84b1a83c.0111272238.7c390a97@posting.google.com>...
> CREATE TRIGGER SYSADM.SET_RES BEFORE INSERT ON SYSADM.RES
> (EXECUTE INLINE (RES.ID_RES)
> PROCEDURE: RES_SET STATIC
> PARAMETERS
>  RECEIVE NUMBER: sID_RES
> ACTIONS
>  call sqlimmediate('select max(ID_RES)+1 from SYSADM.RES into
> :sID_RES')
>  return 0)
> for each row

> return 0 in the text above is a signal that insertion was successful.
> if there was return some_value <> 0 then insertion produced an error
> and this error is catched by delphi in try ..except block.
> So in your trigger for you must use construction like
> if (success...) then raise_application_error

Thanks for this solution, but part of the problem, is that the
database side of this application is not developed by me. So changing
something on the database side isn't the solution. I have already
found a very simple solution by performing a count on the source and
the dest. table, and if the results aren't the same, there's something
wrong. But I would like to know if there is a more generic solution to
this problem.

Other Threads