Home » SQL & PL/SQL » SQL & PL/SQL » global temporary table not seen by autonomous transaction (Oracle 10.2.0.5.0)
global temporary table not seen by autonomous transaction [message #572382] Tue, 11 December 2012 07:01 Go to next message
Buchas
Messages: 81
Registered: March 2006
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 #572385 is a reply to message #572382] Tue, 11 December 2012 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there some other workaround?


No, hopefully, this would break the transaction principles.
Read ACID.

Now the question is, why do you need an autonomous transaction?

Regards
Michel
Re: global temporary table not seen by autonomous transaction [message #572386 is a reply to message #572385] Tue, 11 December 2012 07:39 Go to previous messageGo to next message
Buchas
Messages: 81
Registered: March 2006
Member
Quote:
why do you need an autonomous transaction?


In this particular case the AUTONOMOUS_TRANSACTION procedure needs to know the country of application user.
The application needs to show the amount of money in national currency.
The currency exchange ratio is obtained from external system using Oracle procedure.
As this ratio does not change during a day, it is obtained once and stored into DB using AUTONOMOUS_TRANSACTION...

Another requirement is that application user should see errors in national language.
RAISE_APPLICATION_ERROR(-20xxx,GET_MULTILANG_MSG(1234));
is used for that, where function GET_MULTILANG_MSG depends on the application user language.
It works fine in non AUTONOMOUS_TRANSACTION procedures, and fails to know which language should be used for error messages in AUTONOMOUS_TRANSACTION procedures.

Quote:
hopefully, this would break the transaction principles


What principle does it break? This data is filled only once when the application user connects to DB and is never changed again. The GTT can be set to read-only after that. How can a read-only table break something? Now if only read-only GTT could be shared to its child sessions.... Sad
Re: global temporary table not seen by autonomous transaction [message #572387 is a reply to message #572386] Tue, 11 December 2012 07:51 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Buchas wrote on Tue, 11 December 2012 13:39
Quote:
why do you need an autonomous transaction?


In this particular case the AUTONOMOUS_TRANSACTION procedure needs to know the country of application user.
The application needs to show the amount of money in national currency.
The currency exchange ratio is obtained from external system using Oracle procedure.
As this ratio does not change during a day, it is obtained once and stored into DB using AUTONOMOUS_TRANSACTION...

This doesn't actually explain why an autonomous_transaction is needed. Why wouldn't it work without one?

Buchas wrote on Tue, 11 December 2012 13:39

Quote:
hopefully, this would break the transaction principles


What principle does it break? This data is filled only once when the application user connects to DB and is never changed again.


It breaks the ACID principle by definition. You have one transcation putting data in the GTT and another transaction trying to read it. The 2nd transaction can't see the changes done by the first transaction, until the first transaction commits.
This is one of the basic points of ACID.


However there appears to be a simply solution to your problem.
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.


Buchas wrote on Tue, 11 December 2012 13:39

The GTT can be set to read-only after that. How can a read-only table break something? Now if only read-only GTT could be shared to its child sessions.... Sad


GTT's have no concept of read only, given their purpose It'd make no sense.
Also the last sentence makes no sense. What do you mean by child sessions?
Re: global temporary table not seen by autonomous transaction [message #572389 is a reply to message #572387] Tue, 11 December 2012 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

./fa/2115/0/ A far better answer than I could give.

Regards
Michel
Re: global temporary table not seen by autonomous transaction [message #572390 is a reply to message #572387] Tue, 11 December 2012 08:22 Go to previous messageGo to next message
Buchas
Messages: 81
Registered: March 2006
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 Smile
Thanks!
Re: global temporary table not seen by autonomous transaction [message #572391 is a reply to message #572382] Tue, 11 December 2012 08:39 Go to previous message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Buchas wrote on Tue, 11 December 2012 08:01
The 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.
Previous Topic: Version Wise Objects information
Next Topic: Get exact matching string
Goto Forum:
  


Current Time: Wed Oct 01 10:45:28 CDT 2014

Total time taken to generate the page: 1.14821 seconds