| Problem with implicit cursors when used with Autonomous Transactions [message #7851] |
Sat, 12 July 2003 06:47  |
Parag
Messages: 10
Registered: July 2002
|
Junior Member |
|
|
To PL/SQL experts,
I am using two proc.s, one updates a table called p_update_tbl and another which writes debug msg to a debug table p_add_debug_msg. The second proc. is an autonomous transaction. I call these procedures for some 2000 records in a loop with p_update_tbl called first and the p_add_debug_msg called second. Now after p_add_debug_msg is over I am accessing SQL%ROWCOUNT to get the no. of records updated by p_update_tbl. This one works sometimes, and sometimes it doesn't!
I want to know if the implicit cursor is seperate for EACH transaction or is saved in some common area for all the transactions and hence the implicit cursor (SQL) is getting re-used sometimes for the autonomous transaction and hence I loose the SQL%ROWCOUNT value for the update statement.
According to my belief the two transactions should have seperate implicit cursors (SQL) which will serve to these transactions ONLY. And will be over-written with new values as and when any implicit cursor command is written in the same transaction and not in any other transaction. Untill I run another implicit cursor command in the same transaction, I should be able to retrieve all the attributes of the cursor, exceptions to this rule being commit & rollback.
Help me urgently as because of this I may be needed to change the code.
Thanks,
Parag.
|
|
|
|
| Re: Problem with implicit cursors when used with Autonomous Transactions [message #7875 is a reply to message #7851] |
Mon, 14 July 2003 13:33  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You will want to check the SQL%ROWCOUNT after the update but before the call to the autonomous transaction (AT). The commit in the AT will reset the SQL%ROWCOUNT value to 0, so it is not of any use to check this value after the AT.
It is really not a case of "sometimes it works/sometimes it doesn't" - you are just interpreting a value of 0 as a failed update when in fact it is always returning 0.
sql>create table t (c int);
Table created.
sql>create table log (c int);
Table created.
sql>insert into t select rownum from all_objects where rownum <= 5;
5 rows created.
sql>create or replace package pkg_test
2 is
3 procedure a(p_id in t.c%type);
4 procedure b(p_numrows in pls_integer);
5 end;
6 /
Package created.
sql>create or replace package body pkg_test
2 is
3 procedure a(p_id in t.c%type)
4 is
5 begin
6 update t
7 set c = c
8 where c = p_id;
9
10 dbms_output.put_line( 'Before b: ' || sql%rowcount );
11
12 b(p_id);
13
14 dbms_output.put_line( 'After b, before commit: ' || sql%rowcount );
15 commit;
16 dbms_output.put_line( 'After b, after commit: ' || sql%rowcount );
17 end;
18
19 procedure b(p_numrows in pls_integer)
20 is
21 pragma autonomous_transaction;
22 begin
23 insert into log
24 select object_id
25 from all_objects
26 where rownum <= p_numrows;
27
28 dbms_output.put_line( 'In b, before commit: ' || sql%rowcount );
29 commit;
30 dbms_output.put_line( 'In b, after commit: ' || sql%rowcount );
31 end;
32 end;
33 /
Package body created.
sql>exec pkg_test.a(2)
Before b: 1
In b, before commit: 2
In b, after commit: <b>0</b>
After b, before commit: <b>0</b>
After b, after commit: 0 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
