Hi,
in my code I defined a constant through a custom function that fetches some data from the DB and creates an instance of a custom type. if the return value of the function changes over time, I'm wondering which event trigger a refresh on that constant.
i'd like that happen daily or something like that.
here is the constant definition:
tppag_convers CONSTANT SER_REDD.TB_CHAR3 := ser_redd.fetchChar3Config('UMBR_FUND_CAUS_CONVERS');tnkz
If you want to change something's value, then it's "not a constant"!
Anyway, you can define PL/SQL package variables. The database initializes these for each session when you call the package.
For example, you can create a function which returns a value based on a SQL statement. The assign this to a package level variable:
create table t (
x int
);
insert into t values (1);
commit;
create or replace function f
return t.x%type as
retval t.x%type ;
begin
select x into retval from t;
return retval;
end;
/
create or replace package pkg as
v pls_integer := f;
procedure p;
end pkg;
/
create or replace package body pkg as
procedure p as
begin
dbms_output.put_line('The value of v is: ' || pkg.v);
end p;
end pkg;
/
set serveroutput on
exec pkg.p;
The value of v is: 1
The package variable retains its value until the session ends. So if the function returns a new value, your variable stays the same:
update t set x = 2;
commit;
select f, x from t;
F X
2 2
exec pkg.p;
The value of v is: 1
Other than closing all your sessions and starting new ones (generally not a great idea), there are a few ways to re-initialize the package variables:
- Recompile the package
- Reinitailize the variables using dbms_session
Continuing the example above:
alter package pkg compile;
exec pkg.p;
The value of v is: 2
update t set x = 3;
commit;
exec dbms_session.modify_package_state(dbms_session.reinitialize);
select f, x from t;
F X
3 3
set serveroutput on
exec pkg.p;
The value of v is: 3
Recompiling your packages just to reset package state is a bad idea. And modify_package_state only affects the current session. So you'd need to find a way to call this in each session at the appropriate time.
Alternatively, you can declare the package as "serially_reusable". In this case, the package variables only persist for the duration of the call. So the code picks up any changes sooner:
delete t;
insert into t values (1);
commit;
create or replace package pkg as
pragma serially_reusable;
v pls_integer := f;
procedure p;
end pkg;
/
create or replace package body pkg as
pragma serially_reusable;
procedure p as
begin
dbms_output.put_line('The value of v is: ' || pkg.v);
end p;
end pkg;
/
set serveroutput on
exec pkg.p;
The value of v is: 1
update t set x = 2;
commit;
select * from t;
X
2
exec pkg.p;
The value of v is: 2
The problem here is the initialization will run for each new call. So if your package is "heavily executed" you're adding a lot of overhead executing the SQL providing the value over and over and over and over...