Re: Trying again

You should be able to catch it with a ALTER SCHEMA trigger, and use DBMS_STANDARD to grab the current dictionary object, ie.:

create or replace trigger ... after create or alter or drop on schema ... -- Declare variables here
begin
  object_name := dbms_standard.dictionary_obj_name;   object_type := dbms_standard.dictionary_obj_type;   owner := dbms_standard.dictionary_obj_owner;   operation := dbms_standard.sysevent; -- CREATE/ALTER/DROP   ...
end;

HTH,
Gollum

"DODO" <dodo_at_Ihatespam.com> wrote in message news:leyn6.135579$Z2.1833228_at_nnrp1.uunet.ca...
> Andrew,
> The problem is that I actually need statements like CREATE
> TABLE(blah,blah... ).
> In sql_text there are only select statements or insert or updates.
> Any other idea?
> TIA,
> DODO
>
> "Andrew Velichko" <andrew_velichko_at_yahoo.com> wrote in message
> news:fvan6.135220$Z2.1822521_at_nnrp1.uunet.ca...
> > Hi DODO!
> >
> > I think you can try the following select statement:
> > select distinct
> > sid,
> > lockwait,
> > s.schemaname,
> > s.username,osuser,logon_time,machine,s.terminal,s.program,
> > status,q.sql_text
> > from v$session s
> > ,v$sql q
> > where audsid=(select userenv('sessionid') from dual)
> > and
> >
>

 q.hash_value=s.sql_hash_value --decode(status,'ACTIVE',sql_hash_value,prev_h
> > ash_value)
> > and
> >

 dress=s.sql_address --decode(status,'ACTIVE',sql_address,prev_sql_addr)
> > ;
> >
> > Andrew Velichko
> > Brainbench MVP for Oracle Developer 2000
> > http://www.brainbench.com
> > --------------------------------------------------------------
> >
> > "DODO" <dodo_at_Ihatespam.com> wrote in message
> > news:pP8n6.135180$Z2.1821593_at_nnrp1.uunet.ca...
> > > Hello everybody,
> > >
> > > I'd like to know if there is any way in pl/sql to get the sql
 statement
 that
> > > is executed. Say I am in a database trigger and the Oracle event is
 CREATE
> > > generated by a CREATE TABLE(....) statement. Can I get the create
> > > statement(the text) that was just executed? In Sybase you can turn
 auditing
> > > on and get it from there, but Oracle auditing doesn't do this. The
 text
> > > should be grabbed from a table or a function call.
> > > TIA,
> > > DODO
> > >
> > >
> >
> >
>
>
>
>
Received on Thu Mar 01 2001 - 16:01:30 CST