So I have a package that makes a rest 'POST' request to an api to get a new random number and date only when the current_var is null and the current_date time has expired:
create or replace PACKAGE "MAIN_PKG" AS
g_current_var varchar2(4000);
g_expire_date date;
function foo(p_para in varchar2) return varchar2;
END MAIN_PKG;
create or replace PACKAGE BODY "MAIN_PKG" AS
function foo(p_para in varchar2) return varchar2 AS
l_reponse varchar2(4000);
BEGIN
IF g_current_var IS NOT NULL THEN
IF g_expire_date > SYSDATE THEN -- or g_expire_date < SYSDATE doesn't seem to change anything
--Do something
--Store new info
apex_json.parse(g_current_var);
g_expire_date := sysdate + numToDSInterval(apex_json.get_varchar2('new_time'), 'second');
RETURN g_current_var;
ELSE
RETURN g_current_var;
END IF;
END IF;
--Rest Request to get info if g_current_var is null and g_current_date is greater than sysdate
apex_json.parse(l_reponse);
g_current_var := l_reponse;
g_expire_date := sysdate + numToDSInterval(apex_json.get_varchar2('new_time'), 'second');
-- Return response output
return g_current_var;
END foo;
END MAIN_PKG;
I then call the package in a Ajax CallBack process called Get Main data, in a modal-dialog:
htp.p(MAIN_PKG.foo('Get Random Number'));
Which I then call in some javascript code:
Promise.all([
apex.server.process("Get Main data")
]).then(function(pData) {
var maindata = pData[0];
console.log(["Main data", maindata]);
})
.catch(error => {
apex.message.alert("Something went wrong.");
console.error(["Error", error])
});
The problem I'm having is that the g_current_var and g_current_date seem to be wiped and reset to null, every time Get Main data is called( which happens every time the modal dialog is opened ) even though my session number is still the same. So instead getting the current random number which should be stored in g_current_var that I have generated with an expire time of 1 hour into the future, I'm instead executing the rest request and getting back a new random number. i.e.
What I want to be happening :
1.Get a random number from the api when opening modal dialog
2.g_current_var := 223344
3.g_expire_date := 08-Mar-2023 15:30
4.sysdate is 08-Mar-2023 14:30
5.Opening modal dialog 5 minutes later
6.g_current_var := 223344
7.g_expire_date := 08-Mar-2023 15:30
8.sysdate is 08-Mar-2023 14:35
9.Opening modal dialog 1 hour later
10.sysdate is 08-Mar-2023 15:35 which is past the g_expire_date
11.Get a new random number from rest request
12.g_current_var := 556677
13.g_expire_date := 08-Mar-2023 15:35
What is actually happening:
1.Get a random number from the api when opening modal dialog
2.g_current_var := 223344
3.g_expire_date := 08-Mar-2023 15:30
4.sysdate is 08-Mar-2023 14:30
5.Opening modal dialog 5 minutes later
6.Get a new random number from rest request
7.g_current_var := 778899
8.g_expire_date := 08-Mar-2023 15:35
I was under the assumption that package global variables are stored per session. So g_current_var and g_current_date keep the same random number and date unless an hour has past or the session has timed out. I kind of want to avoid store this information in a table.
You're assuming that your application session is connected to one database session - that is not a correct assumption. Oracle APEX is a stateless application. The lifecycle of an application session will have many different database sessions. If you want variables to persist during the apex session, use application items defined in the APEX application, not database level variables.
Check the docs, it is clearly stated: Sessions are logically and physically distinct from Oracle database sessions used to service page requests. A user runs an application in a single APEX session from sign in to sign out with a typical duration measured in minutes or hours. Each page requested during that session results in the APEX engine creating or reusing an Oracle database session to access database resources. Often these database sessions last just a fraction of a second.