Re: Trying again

Hello,

Sorry that I wasn't specific enough. What I am looking for is the whole create statement issued by the client, like CREATE TABLE(c number, ...). Using sysevents I have to write code that selects from the system tables the description of the table, construct on the fly the CREATE statement and execute it immediate. I was hoping to let Oracle do the work for me:-)) (or is it :-((?) .
Thanks for the responses. Them and the time spent writing them is really appreciated.

DODO "Gollum" <gollum_nospam_at_worldonline.dk> wrote in message news:JFzn6.613$jy5.36910_at_news010.worldonline.dk...
> 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
> > >
> >

 ess=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 Fri Mar 02 2001 - 09:45:53 CST