Re: SQL Batch Script from MSSQL to Oracle 8i

Very helpful of you Frank. Thanks again.

Jeff Lambert

Frank <fbortel_at_nescape.net> wrote in message news:<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
Received on Thu Dec 18 2003 - 17:43:37 CET