ORA-01453: SET TRANSACTION must be first statement of transaction

schreurs_roel · Post by **schreurs_roel** » Sun 13 Mar 2005 20:41

I am using the Corelab ORANET library 2.4 to access a Oracle 9.2 server. That server has a procedure that fetches its data using a DBLink. My client code starts a transaction before executing the procedure and commits it afterwards. This only works once on a connection. The second an next times, I get "ORA-01453: SET TRANSACTION must be first statement of transaction" on the BeginTransaction method of my connection.
My client code simulates nested transactions by setting savepoint. This makes everything a little complicated, but strange thing is, that when I execute another procedure that does not use the DB link, but is otherwise equivalent, the error does not occur!

Code: Select all

  PROCEDURE TransactionOverDBLinkProblem(o_Cur   OUT UntypedCurType)
  IS
  BEGIN
  	  OPEN o_Cur FOR
	  SELECT TAK_ID FROM TSK_TAKEN@DbLink;
  END;

where DbLink is private to the schema.

Code: Select all

  PROCEDURE TransactionProblem(o_Cur   OUT UntypedCurType)
  IS
  BEGIN
  	  OPEN o_Cur FOR
	  SELECT CON_ID FROM KCS_CONTACTEN;
  END;

Below is a trace file from the oracle server I connect to. The lines added after the first execution are marked with !>

Does anybody have a clue?
--------------------------
Dump file d:\oracle\admin\ora9nt6\udump\ora9nt6_ora_3040.trc
Sun Mar 13 20:35:19 2005
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: ora9nt6

Redo thread mounted by this instance: 1

Oracle process number: 17

Windows thread id: 3040, image: ORACLE.EXE

*** 2005-03-13 20:35:19.000
*** SESSION ID:(16.3694) 2005-03-13 20:35:19.000
=====================
PARSING IN CURSOR #1 len=46 dep=0 uid=66 oct=48 lid=66 tim=18446744071157693890 hv=2936887273 ad='65d87904'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END OF STMT
PARSE #1:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071157693883
EXEC #1:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071157696634
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #1 len=315 dep=0 uid=66 oct=47 lid=66 tim=18446744071157708065 hv=3987356949 ad='64e320b4'
Begin Declare v_SavepointName VARCHAR2(50); Begin if transactions.TRANCOUNT = 0 then Commit; end if; transactions.TRANCOUNT := transactions.TRANCOUNT + 1; v_SavepointName := 'PCGC_SAVEPOINT_' || transactions.TRANCOUNT; Savepoint v_SavepointName; :trancount := transactions.TRANCOUNT; End; End;
END OF STMT
PARSE #1:c=0,e=1010,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=18446744071157708059
=====================
PARSING IN CURSOR #3 len=48 dep=2 uid=0 oct=3 lid=0 tim=18446744071157711525 hv=1005331575 ad='65d76f24'
select user# from sys.user$ where name = 'OUTLN'
END OF STMT
PARSE #3:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744071157711519
EXEC #3:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744071157712211
FETCH #3:c=0,e=25,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=18446744071157712373
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ '
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 '
=====================
PARSING IN CURSOR #2 len=6 dep=1 uid=66 oct=44 lid=66 tim=18446744071157712928 hv=3615375148 ad='65d77744'
COMMIT
END OF STMT
PARSE #2:c=0,e=1556,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=4,tim=18446744071157712923
XCTEND rlbk=0, rd_only=1
EXEC #2:c=0,e=145,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18446744071157713651
=====================
PARSING IN CURSOR #3 len=25 dep=1 uid=66 oct=46 lid=66 tim=18446744071157714003 hv=808680499 ad='65d74740'
SAVEPOINT v_SavepointName
END OF STMT
PARSE #3:c=0,e=155,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=18446744071157713997
EXEC #3:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18446744071157714650
EXEC #1:c=0,e=6138,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=18446744071157714827
=====================
PARSING IN CURSOR #1 len=64 dep=0 uid=66 oct=47 lid=66 tim=18446744071157726440 hv=2723593693 ad='64f2fbc8'
BEGIN
kcs_ontl.TEST.TransactionOverDBLinkProblem(:O_CUR);
END;
END OF STMT
PARSE #1:c=0,e=127,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071157726435
=====================
PARSING IN CURSOR #3 len=55 dep=2 uid=0 oct=3 lid=0 tim=18446744071157769819 hv=3975029548 ad='64f4b8ac'
select value$ from props$ where name = 'GLOBAL_DB_NAME'
END OF STMT
PARSE #3:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744071157769813
EXEC #3:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744071157770482
FETCH #3:c=0,e=42,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=18446744071157770663
FETCH #3:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744071157770813
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=101 op='TABLE ACCESS FULL PROPS$ '
=====================
PARSING IN CURSOR #2 len=35 dep=1 uid=66 oct=3 lid=66 tim=18446744071157776543 hv=3246653908 ad='64f4b138'
SELECT TAK_ID FROM TSK_TAKEN@DbLink
END OF STMT
PARSE #2:c=0,e=49140,p=0,cr=4,cu=1,mis=0,r=0,dep=1,og=4,tim=18446744071157776536
EXEC #2:c=0,e=1363,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18446744071157778520
EXEC #1:c=0,e=51735,p=0,cr=4,cu=1,mis=0,r=1,dep=0,og=4,tim=18446744071157779010
=====================
PARSING IN CURSOR #3 len=293 dep=0 uid=66 oct=47 lid=66 tim=18446744071157783171 hv=729512018 ad='65d699bc'
Begin Declare v_orig_i_transactionCount number; Begin If :i_transactionCount = 1 then Commit; end if; v_orig_i_transactionCount := :i_transactionCount; :i_transactionCount := transactions.TRANCOUNT; transactions.TRANCOUNT := v_orig_i_transactionCount - 1; End; End;
END OF STMT
PARSE #3:c=0,e=578,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=18446744071157783165
=====================
PARSING IN CURSOR #4 len=6 dep=1 uid=66 oct=44 lid=66 tim=18446744071157786358 hv=3615375148 ad='65d77744'
COMMIT
END OF STMT
PARSE #4:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18446744071157786352
XCTEND rlbk=0, rd_only=1
EXEC #4:c=0,e=1499,p=0,cr=0,cu=9,mis=0,r=0,dep=1,og=4,tim=18446744071157788488
EXEC #3:c=15625,e=4995,p=0,cr=0,cu=9,mis=0,r=1,dep=0,og=4,tim=18446744071157788800
XCTEND rlbk=0, rd_only=1
FETCH #2:c=0,e=1747,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=4,tim=18446744071157795995
!> *** 2005-03-13 20:36:39.000
!> =====================
!> PARSING IN CURSOR #1 len=46 dep=0 uid=66 oct=48 lid=66 tim=18446744071237106010 hv=2936887273 ad='65d87904'
!> SET TRANSACTION ISOLATION LEVEL READ COMMITTED
!> END OF STMT
!> PARSE #1:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071237106005
!> EXEC #1:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071237106709
!> ERROR #1:err=1453 tim=18722093

schreurs_roel · Post by **schreurs_roel** » Mon 14 Mar 2005 17:07

If I test the following directly on the database (using e.g. Toad)

Code: Select all

BEGIN
DECLARE cursor cur is 
SELECT COUNT(*) FROM RemoteTable@DbLink;
--SELECT COUNT(*) FROM LocalTable;
BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
OPEN cur;
--close cur;
COMMIT;
END;
END;

I get the same error. If I close the cursor, or select from a local cursor, all works fine. I will test this with the newest corelab version.

schreurs_roel · Post by **schreurs_roel** » Mon 14 Mar 2005 18:16

CoreLab.Oracle.dll 2.50.0.0 does not solve the problem.

Paul · Post by **Paul** » Wed 16 Mar 2005 08:39

It is not a problem of components. Try to reproduce it using SQL Plus from Oracle client.
You must execute COMMIT before the line SET TRANSACTION ISOLATION LEVEL READ COMMITTED;or before executing your PL SQL block

Devart Forums