Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL Variable scope

Re: PL/SQL Variable scope

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Wed, 27 Oct 1999 11:47:20 +0200
Message-ID: <941017670.9766.0.pluto.d4ee154e@news.demon.nl>


short answer: Yes, definitely!

Hth,

--
Sybrand Bakker, Oracle DBA
<mikestreeton_at_my-deja.com> wrote in message news:7v6d51$h99$1_at_nnrp1.deja.com...
> 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 - 04:47:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US