PL/SQL Variable scope

I have a problem where we need to audit changes to the database through an application. The application on connects to a single DB user and has its own login and password. We need to record insert/delete and update activities against the application user. The current suggested method is to modify the existing PL/SQL package that authenticates users to have a global variable that hold the current application user. This variable can then be accessed by triggers to produce the audit trail. e.g.

CREATE TABLE X (
  X NUMBER,
  Y VARCHAR2(20))
/

CREATE OR REPLACE package x_login as
myname varchar2(20);
procedure set_myname(mynewname varchar2); end mikes_test;
/

CREATE OR REPLACE package body x_login as procedure set_myname(mynewname varchar2) is begin
x_login.myname := mynewname;
end;
end mikes_test;
/

CREATE OR REPLACE TRIGGER x_trg before insert on x for each row
begin
:new.y:=x_login.myname;
end;
/

The question is is this okay will the contents of x_login.myname be valid for the entire session.

Many Thanks

Mike

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 03:29:23 CDT