Wednesday, March 23, 2011

Seriously?

I haven't done any research on this yet, so I don't know if it is fixed in a subsequent patch or a later version.  But seriously, a space?
SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi                
PL/SQL Release 10.2.0.3.0 - Production                                          
CORE 10.2.0.3.0 Production                                                      
TNS for Linux: Version 10.2.0.3.0 - Production                                  
NLSRTL Version 10.2.0.3.0 - Production                                          

SQL> select trigger_name, triggering_event from user_triggers;

TRIGGER_NAME                   TRIGGERING_EVENT                                 
------------------------------ ------------------------------                   
DDL1                           DDL                                              
LT1                            LOGON                                            
LT2                            LOGON                                            

SQL> select trigger_name, triggering_event from user_triggers
  2  where triggering_event = 'LOGON';

no rows selected

SQL> select trigger_name, '^' || triggering_event || '^' triggering_event
  2  from user_triggers;

TRIGGER_NAME                   TRIGGERING_EVENT                                 
------------------------------ ------------------------------                   
DDL1                           ^DDL ^                                           
LT1                            ^LOGON ^                                         
LT2                            ^LOGON ^                                         

6 comments:

Gary Myers said...

Its part of the view definition (at least in XE) so they can convert a bunch of bits into a 'GRANT OR REVOKE OR ...' string.

Bet it is one of those things that they won't fix on the grounds it may break some scripts.

Craig Martin said...

Do you only get this on DDL and LOGON triggers?

Jeff Hunter said...

@Craig: Interestingly enough, no, I don't get this same condition on other types of triggers (INSERT, for example).

Craig Martin said...

@Jeff: Looks like I get the same thing on 10.2.0.4. And like Gary said, it is part of the _TRIGGERS view. Looking at the view definition, the DDL/database triggers are handled differently than the DML triggers in how they are built which explains why one has spaces and the other doesn't.

I will try to test on an 11gR2 db as well to see if it has been fixed.

Jeff Hunter said...

@craig: it's still in 11.2.0.2.1, at least on Linux.

Neil Chandler said...

I noticed this too. Mildly Annoying - unless you have just spend many many hours trying to solve a problem with the premise that there aren't any DDL triggers in the database. Then it's VERY annoying.