Thanks Jesse and Stphane.
This script works, and is more elegant than my workaround.
Tks.
Ramon
-----Original Message-----
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> on behalf of "Jesse, Rich"
<[EMAIL PROTECTED]>
Sent: Thursday, April 04, 2002 5:28 PM
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Subject: RE: DB Trigger to keep record of DDL
It's your SELECT that's causing the ORA-1422. If you're using 8i or 9i, you
can avoid the SELECT and use the SYS_CONTEXT function like this:
CREATE OR REPLACE TRIGGER CONTROL_DDL
BEFORE DDL ON DATABASE
BEGIN
INSERT INTO CONTROL_OBJETOS
(USUARIO, BASE_DATOS,
HORA, OPERACION,
ESQUEMA, TIPO_OBJETO,
OBJETO, MAQUINA,
USUARIO_OS)
VALUES
(ORA_LOGIN_USER, ORA_DATABASE_NAME,
SYSDATE, ORA_SYSEVENT,
ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME, SYS_CONTEXT('USERENV','TERMINAL'),
SYS_CONTEXT('USERENV','OS_USER'));
END;
(Note: I didn't test this code!!!!)
HTH! GL! :)
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, April 04, 2002 2:58 PM
> To: Multiple recipients of list ORACLE-L
> Subject: DB Trigger to keep record of DDL
>
>
> I did that trigger, it works if the only is logged in just once.
> It gives me the errors of more rows returned 1422.
>
> How can I know in what session he is.
>
> CREATE OR REPLACE TRIGGER CONTROL_DDL
> BEFORE DDL ON DATABASE
> DECLARE
> NOMBRE_MAQUINA VARCHAR2(20);
> USUARIO_MAQUINA VARCHAR2(20);
> BEGIN
> SELECT MACHINE, OSUSER INTO NOMBRE_MAQUINA, USUARIO_MAQUINA
> FROM V$SESSION
> WHERE
> USERNAME = ORA_LOGIN_USER;
>
> INSERT INTO CONTROL_OBJETOS (USUARIO, BASE_DATOS,
> HORA, OPERACION,
> ESQUEMA, TIPO_OBJETO,
> OBJETO, MAQUINA,
> USUARIO_OS)
> VALUES
> (ORA_LOGIN_USER, ORA_DATABASE_NAME,
> SYSDATE, ORA_SYSEVENT,
> ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_TYPE,
> ORA_DICT_OBJ_NAME, NOMBRE_MAQUINA,
> USUARIO_MAQUINA);
> END;
>
>
> TIA
>
> Ramon E. Estevez
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
