Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Temporary Table Scope in OAE
Temporary Table Scope in OAE [message #195994] Tue, 03 October 2006 09:24 Go to next message
tkstock
Messages: 28
Registered: April 2006
Location: Richmond
Junior Member
Hi,

I've created an application process that creates a temporary table and stores some values in it. This appears to work correctly.

When I attempt to get stuff out of the table in a page process, it appears that the table contains no data. Temporary tables are supposed to be scoped to the session - is there some behind-the-scenes session stuff going on that I don't understand? Has anyone else tried this?

Thanks in advance!
Re: Temporary Table Scope in OAE [message #196012 is a reply to message #195994] Tue, 03 October 2006 12:47 Go to previous messageGo to next message
tkstock
Messages: 28
Registered: April 2006
Location: Richmond
Junior Member
OK, for anyone who is interested...

I've figured out why this thing isn't working. Because OAE makes each page submit as a separate HTTP request and OAE maintains a set amount of Oracle sessions and shares them among all the OAE sessions, the data was being lost as soon as it was being submitted. OAE / Oracle sessions are a many to many relationship with no set mapping.

Ultimately, I had to make a permanent table to hold the data.
Re: Temporary Table Scope in OAE [message #196492 is a reply to message #195994] Thu, 05 October 2006 10:35 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
I've tried it and run into the same problem as you. Mine seemed to work intermittently. I ended up using a permanent table which was ok for me (not preferred) given that part of the application isn't used that much.

I'm wondering if this might depend on how long it is from the time the page is rendered until it is processed. I created a little test rig using the PL/SQL web toolkit that just displays the session id in the browser window. If I let the page sit there a while and refresh it, I'll see a different session ID. I suspect that's what's happening but am not sure why. I didn't dig into it. Wonder if there's an Apache setting or something that is causing the session to expire.

create or replace procedure testsid
is
  l_sid pls_integer;
begin

  select userenv('SESSIONID') 
   into l_sid
   from dual;

  htp.p('<html>');

   htp.p('Current session id is ' || to_char(l_sid) );
   htp.p('Current time is ' || to_char(sysdate, 'hh:mi:ss') );
   htp.p('<a href="http://localhost/pls/aportal/testsid">Do it again</a>');

  htp.p('</html>');


end;
Re: Temporary Table Scope in OAE [message #196500 is a reply to message #196492] Thu, 05 October 2006 11:36 Go to previous messageGo to next message
tkstock
Messages: 28
Registered: April 2006
Location: Richmond
Junior Member
If you look at my previous post, I explain why it doesn't work. Since your page render is a separate HTTP event from the page submission, the session will be destroyed between, so the concept of a temporary table won't work. The data would be eliminated as soon as the page finishes rendering and the http request is done.

OAE sessions and Oracle sessions are two different things - one doesn't always map to the other.
Re: Temporary Table Scope in OAE [message #197047 is a reply to message #196500] Mon, 09 October 2006 13:14 Go to previous message
tkstock
Messages: 28
Registered: April 2006
Location: Richmond
Junior Member
More info on the topic:

"Oracle HTML DB sessions are logically and physically distinct from the Oracle database sessions used to service page requests. A user runs an application in a single Oracle HTML DB session from log in to log out with a typical duration measured in minutes or hours. Each page requested during that session results in the HTML DB engine creating or reusing an Oracle database session to access database resources. Each of these sessions lasts just a fraction of a second."

http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10992/mvl_fund.htm#BEIHBFJJ
Previous Topic: HTTP Server
Next Topic: PL/SQL create SQL based on user choices
Goto Forum:
  


Current Time: Thu Mar 28 17:58:14 CDT 2024