4 Replies Latest reply on Mar 3, 2010 9:26 AM by 728534

    ORA-01403: no data found

    Taj.
      all,

      below is the my trigger
      CREATE OR REPLACE TRIGGER "OEM"."ACCESS"
             AFTER logon ON DATABASE
      DECLARE
              v_module VARCHAR2(70);
              v_schemaname VARCHAR2(30);
              v_osuser VARCHAR2(50);
              v_terminal VARCHAR2(70);
              v_date DATE;
          BEGIN
            SELECT
            SYS_CONTEXT('USERENV','MODULE'),
            USER,
            SYS_CONTEXT('USERENV','OS_USER'),
            SYS_CONTEXT('USERENV','TERMINAL'),
            SYSDATE
                   INTO
                            V_MODULE,
                            v_schemaname,
                            v_osuser,
                            v_terminal,
                            v_date
             FROM dual
      WHERE SYS_CONTEXT('USERENV','OS_USER') NOT IN ('SYSTEM','NT AUTHORITY\SYSTEM');
                    SYS.UTL_MAIL.SEND(
                    SENDER=>'xxx@mailserver',
                    RECIPIENTS=>'xxx@mailserver',
                    SUBJECT=>'Access on DB',
                    MESSAGE =>'Access on Oracle Database through  '||UPPER(v_module)||' @ '||v_date||
      CHR(10)||'  DB Username: '||UPPER(v_schemaname)||
      CHR(10)||'  OS User:  '||UPPER(v_osuser)||CHR(10)||'  Terminal:  '||UPPER(v_terminal)              );
        END;
      /
      the trigger created successfully but after that i am getting below error message in the alert log file.
      Wed Mar 03 12:32:36 2010
      Errors in file d:\app\administrator\diag\rdbms\sid\trace\sid_j000_1156.trc:
      ORA-04088: error during execution of trigger 'OEM.ACCESS'
      ORA-01403: no data found
      ORA-06512: at line 8
      what should i need to amend in the trigger coding for avoid above err????
        • 1. Re: ORA-01403: no data found
          730428
          The query raises no_data_found because SYS_CONTEXT('USERENV','OS_USER') is 'SYSTEM' or 'NT AUTHORITY\SYSTEM'.
          Why did you add that where clause to the query from dual?

          Max
          http://oracleitalia.wordpress.com
          • 2. Re: ORA-01403: no data found
            Twinkle
            Hi,


            First check the select stamt by running it seperately.

            You can include exception block in your trigger.


            Twinkle
            • 3. Re: ORA-01403: no data found
              009
              CREATE OR REPLACE TRIGGER "OEM"."ACCESS"
                 AFTER LOGON ON DATABASE
              DECLARE
                 v_module       VARCHAR2 (70);
                 v_schemaname   VARCHAR2 (30);
                 v_osuser       VARCHAR2 (50);
                 v_terminal     VARCHAR2 (70);
                 v_date         DATE;
              BEGIN
                 FOR x IN (SELECT SYS_CONTEXT ('USERENV', 'MODULE') module, USER,
                                  SYS_CONTEXT ('USERENV', 'OS_USER') os_user,
                                  SYS_CONTEXT ('USERENV', 'TERMINAL') terminal, SYSDATE dt
                             FROM DUAL
                            WHERE SYS_CONTEXT ('USERENV', 'OS_USER') NOT IN
                                                         ('SYSTEM', 'NT AUTHORITY\SYSTEM'))
                 LOOP
                    v_module := x.module;
                    v_schemaname := x.USER;
                    v_osuser := x.os_user;
                    v_terminal := x.terminal;
                    v_date := x.dt;
                 END LOOP;
              
                 SYS.utl_mail.send (sender          => 'xxx@mailserver',
                                    recipients      => 'xxx@mailserver',
                                    subject         => 'Access on DB',
                                    MESSAGE         =>    'Access on Oracle Database through  '
                                                       || UPPER (v_module)
                                                       || ' @ '
                                                       || v_date
                                                       || CHR (10)
                                                       || '  DB Username: '
                                                       || UPPER (v_schemaname)
                                                       || CHR (10)
                                                       || '  OS User:  '
                                                       || UPPER (v_osuser)
                                                       || CHR (10)
                                                       || '  Terminal:  '
                                                       || UPPER (v_terminal)
                                   );
              END;
              
                END;
              /*not tested check for compilation errors*/
              Check the above code

              *009*
              • 4. Re: ORA-01403: no data found
                728534
                Hi,
                Try this.
                CREATE OR REPLACE TRIGGER "OEM"."ACCESS"
                       AFTER logon ON DATABASE
                DECLARE
                        v_module VARCHAR2(70);
                        v_schemaname VARCHAR2(30);
                        v_osuser VARCHAR2(50);
                        v_terminal VARCHAR2(70);
                        v_date DATE;
                    BEGIN
                    if (SYS_CONTEXT('USERENV','OS_USER') NOT IN ('SYSTEM','NT AUTHORITY\SYSTEM')) then
                      SELECT
                      SYS_CONTEXT('USERENV','MODULE'),
                      USER,
                      SYS_CONTEXT('USERENV','OS_USER'),
                      SYS_CONTEXT('USERENV','TERMINAL'),
                      SYSDATE
                             INTO
                                      V_MODULE,
                                      v_schemaname,
                                      v_osuser,
                                      v_terminal,
                                      v_date
                       FROM dual;
                --WHERE SYS_CONTEXT('USERENV','OS_USER') NOT IN ('SYSTEM','NT AUTHORITY\SYSTEM');
                              SYS.UTL_MAIL.SEND(
                              SENDER=>'xxx@mailserver',
                              RECIPIENTS=>'xxx@mailserver',
                              SUBJECT=>'Access on DB',
                              MESSAGE =>'Access on Oracle Database through  '||UPPER(v_module)||' @ '||v_date||
                CHR(10)||'  DB Username: '||UPPER(v_schemaname)||
                CHR(10)||'  OS User:  '||UPPER(v_osuser)||CHR(10)||'  Terminal:  '||UPPER(v_terminal)              );
                end if;
                  END;
                Cheers!!!
                Bhushan