Re: ORA-01453 SET TRANSACTION ERROR

[Ray Ontko <rayo@ONTKO.COM>]

Duncan,

Consider using -s (statistics) on your little test program.
There may be another cursor which gets opened as a result
of something else in your program.  -S should reveal it.

Ray

> Duncan J Berriman
> 07/07/98 14:28
>
> Hi,
>
> SQR code we've had running for ages has just started failing with
>
> ORA-01453 SET TRANSATION must be first statement of transation.
>
> The following code reproduces the problem, as you can see the SET
> TRANSACTION statement is the ONLY statement and therefore the first
> statement. Putting a ROLLBACK or COMMIT before the do get_rollback stops
> the error.
>
> The only thing I can think that can have caused this is turning on database
> auditing on the database (AUDIT_TRAIL=TRUE in INIT.ORA and AUDIT SESSION in
> SQLPLUS). This will be causing a record to be inserted into SYS.AUD$ on
> logon.
>
> This doesn't cause any problem with other products (such as SQL*PLUS). Has
> anyone come across this before ? Is it a bug/ known feature of SQR and is
> there a fix ? Removing the SET TRANSATION cures the problem.
>
> I am turning off auditing this evening to see if it stops the error
> occuring.
>
> Any info greatly appreciated
> Thanks
> Duncan Berriman
>
> begin-report
>   do get_rollback
> end-report
>
> begin-procedure get_rollback
>
> begin-sql
> set transaction
> use rollback segment RBS1
> end-sql
>
> end-procedure
>
> SQR: Structured Query Report Writer V3.0.7.0.2
> Copyright (C) MITI, 1994, 1995.  All Worldwide Rights Reserved.
>
> (SQR 5528) ORACLE OEXEC error -1453 in cursor 1:
>    ORA-01453: SET TRANSACTION must be first statement of transaction
>
> Error on line 7:
>    (SQR 3735) Couldn't execute SQL.
>
> SQR: Program Aborting.
>

----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/