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: Oracle and ADO command.execute- Create Trigger Fails

Re: Oracle and ADO command.execute- Create Trigger Fails

From: Ted Knijff <knijff_at_bigfoot.com>
Date: Sat, 03 Mar 2001 15:40:01 GMT
Message-ID: <3aa10f9b.24695980@news.online.de>

Maybe I don't get the problem, but a create table like :  CREATE TABLE foo AS SELECT * FROM myschema.baa WHERE 1=2; will create a table structure foo from schema myschema, table baa.

Is that what you want to do ?

On Fri, 2 Mar 2001 10:45:53 -0500, "DODO" <dodo_at_Ihatespam.com> wrote:

>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
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>> >
>> >
>>
>>
>
>

EMail: knijff_at_bigfoot.com Received on Sat Mar 03 2001 - 09:40:01 CST

Original text of this message

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