Re: SQL Batch Script from MSSQL to Oracle 8i
From: Frank <fbortel_at_nescape.net>
Date: Wed, 17 Dec 2003 21:15:46 +0100
Message-ID: <brqd0k$cal$1_at_news4.tilbu1.nb.home.nl>
>
>
>
> 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
Date: Wed, 17 Dec 2003 21:15:46 +0100
Message-ID: <brqd0k$cal$1_at_news4.tilbu1.nb.home.nl>
Jeff Lambert wrote:
> Frank <fbortel_at_nescape.net> wrote in message news:<brl73j$80b$1_at_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
Yup - can do it. The other end of the scale would be WHEN OTHERS THEN NULL - ignore every error, just continue. Yours would rollback every statement since the last commit; even the ones correctly executed
-- Regards, Frank van BortelReceived on Wed Dec 17 2003 - 21:15:46 CET