Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to reset Package state variables in all connected sessions (without requiring a disconnect and a

Gaurav RMar 5 2015 — edited May 28 2015


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;

This post has been answered by Karthick2003 on Mar 5 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 25 2015
Added on Mar 5 2015
3 comments
1,924 views