From: fbortel@nescape.net   
      
   Jeff Lambert wrote:   
   > Frank  wrote in message news:...   
   >   
   >>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 Bortel   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)