Hi Folks ,
Need help,The example requires that the package data value "g_var" should be refreshed across in the connected session/s without requiring the session/s to be disconnected and reconnected.
Here's the scenario:
I have created a table my_table having name and value pair.
A package with global variable "g_var" in its body and a getter method to access g_var variable from outside world.
This g_var variable is initialised within initialisation block of the package and fetches curent value of variable from my_table.
When I update the value in the table a trigger on my_table would reinitialise package state and thereby g_var to the newer value within the same session
All's well till now since the value change is reflected in the current session , however if I have already connected session/s
and I try to fetch "g_var" value , it is not refreshed in already connected sessions and remains the same before the update.
Can u please help how can I refresh this value in a connected session/s without having to disconnect and reconnect,
because if I disconnect and reconect the existing sessions, It seems to show updated value.
Thanks in Advance.
Regards,
Gaurav Rajput
Here are the Scripts:
drop table my_table;
create table my_table
(
my_variable_name varchar2(100),
my_variable_value number
);
create or replace package my_package as
function get_g_var return number;
end my_package;
/
create or replace package body my_package as
g_var number;
function get_g_var return number is
begin
return g_var;
exception
when others then
raise;
end get_g_var;
procedure init(my_passed_name varchar2) is
begin
select my_variable_value into g_var from my_table where my_variable_name = my_passed_name;
exception
when others then
raise_application_error(-20004,'I have an error during Init');
end init;
begin
init('DOLLAR') ; -- this is one time call when the package is loaded in the session memory
exception
when others then
raise_application_error(-20003,'Error in my_package');
end my_pACkage;
/
create or replace TRIGGER my_trigger
BEFORE DELETE OR UPDATE
ON my_table
FOR EACH ROW
declare
my_ret number;
begin
dbms_session.modify_package_state(DBMS_SESSION.REINITIALIZE);
exception
when others then
raise;
end my_trigger;
/
--intially it is null
insert into my_table values ('DOLLAR',62);
commit;
-- test the value for the first time load of package (Displays 62)
select * from my_table;
set serveroutput on
declare
my_var varchar2(100);
BEGIN
my_var:= my_package.get_g_var ;
dbms_output.put_line(my_var);
END;
-- run the same begin end block in another session it displays 62 . All's well till now
-- check the value after update in the same session(Displays 38),Additionally check in another before connected session as well (Displays 62 and not 38)
update my_table set my_variable_value = 38 where my_variable_name = 'DOLLAR';
commit;
select * from my_table;
set serveroutput on
-- it displays 38 in this session but in already connected session it is still 62 and only after connect disconnect it shows 38
declare
my_var varchar2(100);
BEGIN
--my_var:= my_package.g_var ;
my_var:= my_package.get_g_var ;
dbms_output.put_line(my_var);
END;