Interrubted stored procedure

[h.o.o.k]

Hi all,

I have a stored procedure in my db (oracle 10g enteprise) with name "myPROC". This procedure have one parameter type text with name "table".
Procedure process perhaps 350 000 records by cursor. When i call procedure from etc. ORACLE developer by SQL command so operations pass correct. BUT when i do this:

Code: Select all

try
connection.StartTransaction;

qry.SQL.text := 'CALL myPROC('table name')';
qry.ExecSQL ;

connection.commit;

except on e:Exception do
begin
  writeErroLOg(e.message);
  connection.rollback;
end;

from my application so app throw NO ERROR, everthing look OK but procedure pass only etc 200 000 records. Just procedure is interrupt. i dont know what am i doing.

Please HELP.

note: I am so sorry for my English, i am junior developer from czech republic. Thaks for our understanding.

[Wild_Pointer]

Hello h.o.o.k,

I don't know how it is in Oracle, but in postgresql procedure either procedure is executed whole or not at all... It is hard to believe, that the Oracle server would interpret query differently just because it was issued by Zeos components . So maybe the procedure myPROC should be checked looking for the problem?..

One more thing - you should put line connection.StartTransaction; before the try statement, because it would not be right to rollback transaction if you get an error on starting it.

Good luck!

[h.o.o.k]

Thanks for reply!

On this i know too. But why from the ORACLE developer myPROC is pass success? I dont understand it. :0(( So im going search error in proc.

Thank you

[Wild_Pointer]

h.o.o.k, You can add ZSQLMonitor to your project so see what SQL is send to the server. Maybe this will help you.