| global temporary table not seen by autonomous transaction [message #572382] |
Tue, 11 December 2012 07:01  |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Hello,
I have a global temporary table (on commit preserve rows) that has some application user data stored (user_id, language, country, etc.) in it.
The data is filled once when the application user connects to DB.
The problem is that by Oracle design autonomous transactions do not see the data in GTT.
One possible workaround would be to modify all AUTONOMOUS_TRANSACTION procedures/functions that would take required data as parameters.
However this solution is somewhat not elegant - if another parameter gets added to the GTT, all procedures/functions will have to be changed again.
Is there some other workaround?
This is a test case to play with:
create global temporary table TEMP_SESSION_T
(
id NUMBER,
numeris1 NUMBER,
identity VARCHAR2(254)
)
on commit preserve rows;
CREATE OR REPLACE PROCEDURE AUTONOM IS
PRAGMA AUTONOMOUS_TRANSACTION;
NUM2 NUMBER;
BEGIN
SELECT NUMERIS1 INTO NUM2 FROM TEMP_SESSION_T WHERE IDENTITY = 'CURRENT_USER_IDENTITY';
DBMS_OUTPUT.PUT_LINE('NUM2:= '||NUM2);
END;
And now executing PLSQL block gives error ORA01403:
declare
num1 number;
begin
INSERT INTO TEMP_SESSION_T (
NUMERIS1,
IDENTITY
) VALUES (
6446240,
'CURRENT_USER_IDENTITY'
) ;
select NUMERIS1 into num1 from SETTINGS.TEMP_SESSION_T where IDENTITY = 'CURRENT_USER_IDENTITY'; --ok
dbms_output.put_line('num1:= '||num1);
autonom; --error ORA01403: no data found :((
end;
Is there a way to have a dbms_output to print lines
num1:= 6446240
num2:= 6446240
?
[Updated on: Tue, 11 December 2012 07:01] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: global temporary table not seen by autonomous transaction [message #572390 is a reply to message #572387] |
Tue, 11 December 2012 08:22  |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Quote:This doesn't actually explain why an autonomous_transaction is needed. Why wouldn't it work without one?
Storing exchange ratio into DB needs COMMIT, which could commit other data of the main transaction, which might be bad.
Quote:What do you mean by child sessions?
OK, I see it now:
"Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction."
I thought of autonomous transaction like a child of main transaction, but I was wrong. Thanks for pointing this out.
Quote:If the GTT is only ever loaded at the start of the session and not changed afterwards then just issue a commit after you finish inserting the data. That finishes the transaction and then any autonomous_transaction procedures in the same session will be able to see the data.
OK, if I change my test-script to
declare
num1 number;
begin
INSERT INTO TEMP_SESSION_T (
NUMERIS1,
IDENTITY
) VALUES (
6446240,
'CURRENT_USER_IDENTITY'
) ;
COMMIT;
select NUMERIS1 into num1 from SETTINGS.TEMP_SESSION_T where IDENTITY = 'CURRENT_USER_IDENTITY'; --ok
dbms_output.put_line('num1:= '||num1);
autonom; --error ORA01403: no data found :((
end;
It gives what I want 
Thanks!
|
|
|
|
| Re: global temporary table not seen by autonomous transaction [message #572391 is a reply to message #572382] |
Tue, 11 December 2012 08:39 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Buchas wrote on Tue, 11 December 2012 08:01The problem is that by Oracle design autonomous transactions do not see the data in GTT.
Well, it seems you are associating it with GTT. All poodles are dogs but not all dogs are poodles. Anyway, it has nothing to do with GTT. Autonomous transaction by definition doesn't see uncommitted changes made in main transaction. So it does not matter if you insert data into GTT or into a regular table. If you do not commit before initiating autonomous transaction it will not see inserted data. So all you need it commit after "the data is filled once when the application user connects to DB":
SQL> create global temporary table TEMP_SESSION_T
2 (
3 id NUMBER,
4 numeris1 NUMBER,
5 identity VARCHAR2(254)
6 )
7 on commit preserve rows;
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AUTONOM IS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 NUM2 NUMBER;
4 BEGIN
5 SELECT NUMERIS1 INTO NUM2 FROM TEMP_SESSION_T WHERE IDENTITY = 'CURRENT_USER_IDENTITY';
6 DBMS_OUTPUT.PUT_LINE('NUM2:= '||NUM2);
7
8 END;
9
10
11 /
Procedure created.
SQL> insert into TEMP_SESSION_T
2 values(1,999,'CURRENT_USER_IDENTITY')
3 /
1 row created.
SQL> commit;
Commit complete.
SQL> set serveroutput on
SQL> exec AUTONOM;
NUM2:= 999
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
