Problem solve Get help with specific problems with your technologies, process and projects.

Refresh value of a global variable only after commit

Refresh a value of a global variable only after a commit.

Through any front-end application it's easy to control the value of any variable via reinitialization at any point. But what happens if you want to reinitialize the value at the SQL prompt or at the database level? You would need sort of ON COMMIT trigger that you execute only when whole transaction gets committed. Using the tip below, you can not only reinitialize the value of a variable after COMMIT at the backend or the SQL prompt, you can also execute specific functions or procedures only after COMMIT. It was tested on the Oracle 9i database, but it should be possible to run on 8i also.

A) Create transactional table T1:

create table T1 ( dep_no number, emp_name varchar2 (50) );
B) Create global temporary table:
create global temporary table gbl_tmptbl (c1 number);
C) Initialize or define global variable:
create or replace package refresh_var as 
  seq_no number;
D) Create materialized view:
Create materialized view mv_refresh refresh on commit as 
select c1, count(1) from GBL_TMPTBL group by c1;

E) Create a sequence or use a defined sequence:

create sequence txn_seq start with 1 increment by 1;
F) Create before insert trigger on materialized view:
create or replace trigger trg_aft_MV before insert on 
mv_refresh for each row
  refresh_var.seq_no :=0;
G) Create a before insert trigger on trnsactional table T1 in order to set the value of variable or to refresh the value of variable:
create or replace trigger trg_befins_T1  before insert on T1 for each row
 if refresh_var.seq_no = 0 or refresh_var.seq_no is NULL then 
    select seq_txn.nextval into refresh_var.seq_no from dual; 
 end if;
 insert into GBL_TMPTBL values (refresh_var.seq_no);
 :new.dep_no := refresh_var.seq_no;
Note: You can define the value of the global variable at the SQL prompt also.

Here is an example:

SQL> insert into T1 (emp_name) values ('Ravinder');
SQL> insert into T1 (emp_name) values ( 'John');
SQL> commit;
SQL> insert into T1 (emp_name) values ( 'Sanjay');
SQL> insert into T1 (emp_name) values ( 'Oliver');
SQL> commit;

Here are the results:

SQL> select * from T1;
SQL> -----     ---------
SQL> 1         Ravinder
SQL> 1         John
SQL> 2         Sanjay
SQL> 2         Oliver
The Dept_no is incremented only after the commit.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle and SQL