Frank <fb*****@nescape.net> wrote in message news:<br**********@news1.tilbu1.nb.home.nl>...
Jeff Lambert wrote:
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.
What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints, so no editorials please.
Thanks in advance. Jeff.
-- SQL batch script
SET NOCOUNT ON
SET XACT_ABORT ON --this is especially important
BEGIN TRANSACTION INDX_ADD_ADMIN
/* many stored procedure calls in a loop */
/* ie Str := 'BEGIN INDX_DELSRC ('''+DOS_NAME+'''); END;'; */
COMMIT TRANSACTION INDX_ADD_ADMIN
SET XACT_ABORT OFF
SET NOCOUNT OFF
Now, if you would at least supply what the SS2K statements do.
I can only guess: set transaction_abort off? As in "I don't care
that my transaction has errors, just go on"?
Not possible.
Begin tran/end tran: should probably become a stored procedure
with pragma autonomous_transaction
set nocount off (or on): don't bother - oracle will always be
able to tell you how many rows were affected - you normally
don't ask (in PL/SQL)
I'm sorry Frank, you are right. The set nocount is used to tell SQL
NOT to return row count everytime, it is used out of performance. Here
is the help for XACT_ABORT
SET XACT_ABORT (T-SQL)
Specifies whether Microsoft® SQL Server™ automatically rolls back the
current transaction if a Transact-SQL statement raises a run-time
error.
We don't want to (and cannot) make a whole stored procedure out of
this because the loop goes and fetch data from different sources. my
script on the Delphi side now looks like:
BEGIN
/* call lots of oracle stored procedures with various parameters */
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;
Fairly simple huh? Is there anything I should consider adding to it to
make it foolproof?
Thank you
jeff lambert