Re: SQL Batch Script from MSSQL to Oracle 8i

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 Received on Wed Dec 17 2003 - 15:02:47 CET