SMTP Mail Oracle 10g Database Configuration

Hi,

In my opinion, the application must have an error log. You should ask the user that try to perform the same problematic task and see the log again.

Regards,

118236

No, unfortunately the application does not capture this information in an error log. The error log simply reports what the error returned from oracle was and not the SQL code that generated the error, which is what I am looking for.

Thanks for your quick response anyway!

Satyaki_De

It is always better to use any log mechanism in order to track all these details. Else, you can enable audit trigger to track all these intricate details.

Regards.

Satyaki De.

86140

Unfortunately sql statements which fail to execute are hard to capture. One option is to capture it at the database driver level, for instance, if your application is using an ODBC driver then you can enable trace on the ODBC driver and look for the sql statement in the log generated by the trace.

118236

Yes I see what you are saying. This is our setup (I should have specified this before)... We have a Client app written in PowerBuilder version 6 that talks to a Server app also in Powerbuilder 6. The server app talks to the Oracle database using the Oracle driver that Powerbuilder provides. Our problem is that the Powerbuilder code is no longer accessible and I don't know if you can set up any type of logging on the Powerbuilder Oracle driver.

Satyaki_De

Well in that case, you may have to write one logger apps in power builder to get track of all the bugs from your application that may encounter during any transaction - i guess.

There might not be any easy solution from the database end to exactly track down the reason of the third party application's error - i guess.

Regards.

Satyaki De.

469753

Answer

I've used an ON SERVER ERROR trigger to capture offending SQL and log it to a file (via UTL_FILE) or send it via email (via UTL_SMTP).
Here is a verbose example that both emails and logs the offending SQL, the error stack and bunch of other diagnostic information.
You'll need to do a bunch of GRANTS to get it to compile, but it works nicely.

CREATE OR REPLACE TRIGGER EmailOnServerErr AFTER SERVERERROR ON DATABASE
DECLARE
   mail_conn       UTL_SMTP.connection;
   crlf            VARCHAR2(2) := chr(13)||chr(10);
   msg             VARCHAR2(32760);
   sid_name        VARCHAR2(16);
   bdump_dest      VARCHAR2(128);
   smtp_relay      VARCHAR2(32) := 'MyMailRelay';
   recipient_address  VARCHAR2(64) := 'recipient@MyCompany.com';
   sender_address     VARCHAR2(64) := 'oracle@MyCompany.com';
   mail_port       NUMBER := 25;

   log_file_handle UTL_FILE.FILE_TYPE;
   log_file_dir    VARCHAR2(256) := 'ERR_LOG_DIR';
   log_file_name   VARCHAR2(256) := 'OracleErrors.log';
   maxlinesize     NUMBER := 32767;
   session_rec     sys.v_$session%ROWTYPE;
   audit_rec       sys.dba_audit_trail%ROWTYPE;
   auditing        BOOLEAN;
   LinesOfSQL      BINARY_INTEGER;
   offending_sql   DBMS_STANDARD.ora_name_list_t;

   CURSOR bdump_cur IS
      SELECT TRIM(value)
      FROM v$parameter
      WHERE name = 'background_dump_dest'
   ;

   CURSOR sid_cur IS
      SELECT TRIM(instance_name)
      FROM v$instance
   ;

   CURSOR session_cur IS
      SELECT s.*
      FROM v$session s
      WHERE s.sid = dbms_support.mysid
   ;

   CURSOR audit_trail_cur(AUDSID IN NUMBER) IS
      SELECT *
      FROM dba_audit_trail
      WHERE sessionid = AUDSID
   ;

BEGIN
   IF (USER = 'SYSTEM' OR USER = 'SYS') THEN
      -- Ignore this error
      NULL;
   ELSIF IS_SERVERERROR (1034) THEN
      -- Ignore this error
      NULL;
   ELSE
      -- get the sid
      OPEN sid_cur;
      FETCH sid_cur INTO sid_name;
      CLOSE sid_cur;

      -- get the location of the alert log
      OPEN bdump_cur;
      FETCH bdump_cur INTO bdump_dest;
      CLOSE bdump_cur;

      -- get the session information
      OPEN session_cur;
      FETCH session_cur INTO session_rec;
      CLOSE session_cur;

      -- get the audit_trail information if it exists
      OPEN audit_trail_cur(session_rec.audsid);
      FETCH audit_trail_cur INTO audit_rec;
      auditing := audit_trail_cur%FOUND;
      CLOSE audit_trail_cur;


      IF session_rec.program = 'MyProgram.exe' THEN
         NULL;  -- ignore actions from MyProgram - that's where I do maintenance
      ELSE
         -- compose the message
         msg := 'Subject: Oracle error '||' on '||sid_name||crlf;
         msg := msg||'To: '||recipient_address||crlf;
         msg := msg||'For more information see the alert log file located at:'||crlf;
         msg := msg||bdump_dest||'/alert_'||sid_name||'.log'||crlf;
         msg := msg||'or the error log file: $'||log_file_dir||'/'||log_file_name||crlf;
         msg := msg||'Error Time='||TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS')||crlf;
         msg := msg||DBMS_UTILITY.FORMAT_CALL_STACK||crlf;

         LinesOfSQL := sql_txt(offending_sql);
         msg := msg||'Offending SQL is:'||crlf;
         FOR loop_counter IN offending_sql.FIRST..offending_sql.LAST
         LOOP
            msg := msg||offending_sql(loop_counter);
         END LOOP;

         msg := msg||crlf||'----- PL/SQL Error Stack -----'||crlf;
         msg := msg||DBMS_UTILITY.FORMAT_ERROR_STACK||crlf;
         msg := msg||'V$SESSION.SADDR='   ||session_rec.saddr   ||crlf;
         msg := msg||'V$SESSION.SID='     ||session_rec.sid     ||crlf;
         msg := msg||'V$SESSION.SERIAL#=' ||session_rec.serial# ||crlf;
         msg := msg||'V$SESSION.AUDSID='  ||session_rec.audsid  ||crlf;
         msg := msg||'V$SESSION.PADDR='   ||session_rec.paddr   ||crlf;
         msg := msg||'V$SESSION.USER#='   ||session_rec.user#   ||crlf;
         msg := msg||'V$SESSION.USERNAME='||session_rec.username||crlf;
         msg := msg||'V$SESSION.COMMAND=' ||session_rec.command ||crlf;
         msg := msg||'V$SESSION.OWNERID=' ||session_rec.ownerid ||crlf;
         msg := msg||'V$SESSION.TADDR='   ||NVL(session_rec.taddr   ,'Null')||crlf;
         msg := msg||'V$SESSION.LOCKWAIT='||NVL(session_rec.lockwait,'Null')||crlf;
         msg := msg||'V$SESSION.STATUS='  ||NVL(session_rec.status  ,'Null')||crlf;
         msg := msg||'V$SESSION.SERVER='  ||NVL(session_rec.server  ,'Null')||crlf;
         msg := msg||'V$SESSION.SCHEMA#=' ||session_rec.schema#||crlf;
         msg := msg||'V$SESSION.SCHEMANAME=' ||NVL(session_rec.schemaname,'Null')||crlf;
         msg := msg||'V$SESSION.OSUSER='     ||NVL(session_rec.osuser    ,'Null')||crlf;
         msg := msg||'V$SESSION.PROCESS='    ||NVL(session_rec.process   ,'Null')||crlf;
         msg := msg||'V$SESSION.MACHINE='    ||NVL(session_rec.machine   ,'Null')||crlf;
         msg := msg||'V$SESSION.TERMINAL='   ||NVL(session_rec.terminal  ,'Null')||crlf;
         msg := msg||'V$SESSION.PROGRAM='    ||NVL(session_rec.program   ,'Null')||crlf;
         msg := msg||'V$SESSION.TYPE='       ||NVL(session_rec.type      ,'Null')||crlf;
         msg := msg||'V$SESSION.SQL_ADDRESS='    ||session_rec.sql_address  ||crlf;
         msg := msg||'V$SESSION.SQL_HASH_VALUE=' ||NVL(TO_CHAR(session_rec.sql_hash_value) ,'Null')||crlf;
         msg := msg||'V$SESSION.PREV_SQL_ADDR='  ||session_rec.prev_sql_addr||crlf;
         msg := msg||'V$SESSION.PREV_HASH_VALUE='||NVL(TO_CHAR(session_rec.prev_hash_value),'Null')||crlf;
         msg := msg||'V$SESSION.MODULE='     ||NVL(session_rec.module              ,'Null')||crlf;
         msg := msg||'V$SESSION.MODULE_HASH='||NVL(TO_CHAR(session_rec.module_hash),'Null')||crlf;
         msg := msg||'V$SESSION.ACTION='     ||NVL(session_rec.action              ,'Null')||crlf;
         msg := msg||'V$SESSION.ACTION_HASH='||NVL(TO_CHAR(session_rec.action_hash),'Null')||crlf;
         msg := msg||'V$SESSION.CLIENT_INFO='||NVL(session_rec.client_info         ,'Null')||crlf;
         msg := msg||'V$SESSION.FIXED_TABLE_SEQUENCE='||NVL(TO_CHAR(session_rec.fixed_table_sequence),'Null')||crlf;
         msg := msg||'V$SESSION.ROW_WAIT_OBJ#='  ||NVL(TO_CHAR(session_rec.row_wait_obj#)  ,'Null')||crlf;
         msg := msg||'V$SESSION.ROW_WAIT_FILE#=' ||NVL(TO_CHAR(session_rec.row_wait_file#) ,'Null')||crlf;
         msg := msg||'V$SESSION.ROW_WAIT_BLOCK#='||NVL(TO_CHAR(session_rec.row_wait_block#),'Null')||crlf;
         msg := msg||'V$SESSION.ROW_WAIT_ROW#='  ||NVL(TO_CHAR(session_rec.row_wait_row#)  ,'Null')||crlf;
         msg := msg||'V$SESSION.LOGON_TIME='     ||NVL(TO_CHAR(session_rec.logon_time,'DD-Mon-YYYY HH24:MI:SS'),'Null')||crlf;
         msg := msg||'V$SESSION.LAST_CALL_ET='   ||NVL(TO_CHAR(session_rec.last_call_et)   ,'Null')||crlf;
         msg := msg||'V$SESSION.PDML_ENABLED='   ||NVL(session_rec.pdml_enabled   ,'Null')||crlf;
         msg := msg||'V$SESSION.FAILOVER_TYPE='  ||NVL(session_rec.failover_type  ,'Null')||crlf;
         msg := msg||'V$SESSION.FAILOVER_METHOD='||NVL(session_rec.failover_method,'Null')||crlf;
         msg := msg||'V$SESSION.FAILED_OVER='    ||NVL(session_rec.failed_over    ,'Null')||crlf;
         msg := msg||'V$SESSION.RESOURCE_CONSUMER_GROUP='||NVL(session_rec.resource_consumer_group,'Null')||crlf;
         msg := msg||'V$SESSION.PDML_STATUS='    ||NVL(session_rec.pdml_status    ,'Null')||crlf;
         msg := msg||'V$SESSION.PDDL_STATUS='    ||NVL(session_rec.pddl_status    ,'Null')||crlf;
         msg := msg||'V$SESSION.PQ_STATUS='      ||NVL(session_rec.pq_status      ,'Null')||crlf;
         IF auditing THEN
            msg := msg||'DBA_AUDIT_TRAIL.OS_USERNAME='  ||NVL(audit_rec.os_username,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.USERNAME='     ||NVL(audit_rec.username   ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.USERHOST='     ||NVL(audit_rec.userhost   ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.TERMINAL='     ||NVL(audit_rec.terminal   ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.TIMESTAMP='    ||TO_CHAR(audit_rec.timestamp,'DD-Mon-YYYY HH24:MI:SS')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.OWNER='        ||NVL(audit_rec.owner      ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.OBJ_NAME='     ||NVL(audit_rec.obj_name   ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.ACTION='       ||audit_rec.action   ||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.ACTION_NAME='  ||NVL(audit_rec.action_name   ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.NEW_OWNER='    ||NVL(audit_rec.new_owner     ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.NEW_NAME='     ||NVL(audit_rec.new_name      ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.OBJ_PRIVILEGE='||NVL(audit_rec.obj_privilege ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.SYS_PRIVILEGE='||NVL(audit_rec.sys_privilege ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.ADMIN_OPTION=' ||NVL(audit_rec.admin_option  ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.GRANTEE='      ||NVL(audit_rec.grantee       ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.AUDIT_OPTION=' ||NVL(audit_rec.audit_option  ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.SES_ACTIONS='  ||NVL(audit_rec.ses_actions   ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.LOGOFF_TIME='  ||NVL(TO_CHAR(audit_rec.logoff_time)  ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.LOGOFF_LREAD=' ||NVL(TO_CHAR(audit_rec.logoff_lread) ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.LOGOFF_PREAD=' ||NVL(TO_CHAR(audit_rec.logoff_pread) ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.LOGOFF_LWRITE='||NVL(TO_CHAR(audit_rec.logoff_lwrite),'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.LOGOFF_DLOCK=' ||NVL(audit_rec.logoff_dlock  ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.COMMENT_TEXT=' ||NVL(audit_rec.comment_text  ,'Null')||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.SESSIONID='    ||audit_rec.sessionid   ||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.ENTRYID='      ||audit_rec.entryid     ||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.STATEMENTID='  ||audit_rec.statementid ||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.RETURNCODE='   ||audit_rec.returncode  ||crlf;
            msg := msg||'DBA_AUDIT_TRAIL.PRIV_USED='    ||NVL(audit_rec.priv_used,'Null')||crlf;
         END IF;
         msg := msg||'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-'||crlf||crlf;
         -- write the message to the error log file
         log_file_handle := UTL_FILE.FOPEN (log_file_dir, log_file_name, 'A',maxlinesize);
         UTL_FILE.PUT_LINE(log_file_handle,msg);
         UTL_FILE.FCLOSE(log_file_handle);

         -- send the message by Email
         mail_conn := UTL_SMTP.open_connection(smtp_relay, mail_port);
         UTL_SMTP.HELO(mail_conn, smtp_relay);
         UTL_SMTP.MAIL(mail_conn, sender_address);
         UTL_SMTP.RCPT(mail_conn, recipient_address);
         UTL_SMTP.DATA(mail_conn, msg);
         UTL_SMTP.QUIT(mail_conn);
      END IF; -- client_program = MyProgram.exe
   END IF;
END;
/

Marked as Answer by 118236 · Sep 27 2020

MichaelS

Don't know much about Powerbuilder, but isn't it running on windows?
Also if the connection is through SQL*Net you can easily plug in SQLMonitor (free), which records all SQL traffic between client and server.

118236

FredJMuggs Thanks for this reply. I have managed to get your script working and it is more than what I was looking for. Thanks so much for your help!

118236

I thought this was going to work, however, the trigger captures anything that is an error when using SQL Plus or worksheet but when the application (powerbuilder) is running and it tries to do something that returns an Oracle error the trigger is never fired.

Satyaki_De

Post your trigger error number here - that will help us to track down your problem.

Regards.

Satyaki De.

469753

The trigger has several places where errors are intentionally ignored:
-if the triggering user is SYS or SYSTEM
-if the error number is 1034 "ORACLE not available"
-if the triggering program is a specific binary

Have you tried tuning these ignore clauses to meet YOUR requirements?

The database WILL fire the trigger if the database raises an error. If your powerbuilder app is throwing an error that is not captured by the trigger in the log, it may not be raised by the database - or you may be ignoring the error by way of the several "ignore clauses"

What kind of error are you seeing that is not logged by your trigger?

Peter Gjelstrup

andersa99 wrote:
a user of the app receives an Oracle error reporting "more than 2 rows have been returned"

Does not sound much like an Oracle exception to me, more like that Oracle returns 2+n rows, the application expects 2 (or less) thus raises an error.

I would try trace the session to see the last sql(s) issued and then try fix the data.

Regards
Peter

118236

I am receiving the error (displayed in a PowerBuilder window) 'Select returned more than one row' so I am pretty sure Oracle is generating the error. There is a client executable 'claims.exe' that talks to a server executable 'server.exe' that request info from the Oracle database.

I had thought of the exclusion clauses in your trigger but I know the application does not use the SYS or SYSTEM account and it looked like the code excluded anything from the MyProgram.exe otherwise everything was captured. Are you suggesting that I replace the 'MyProgram.exe' be replace with 'server.exe'?

Thanks for you help, this trigger will be very useful in the future and I'm sure we can get it to do what I want. I'm just not all that familiar with this type of thing in Oracle.

Note - the trigger does work as expected when I log in to SQL plus and intentionally do a select on a non-existent table so I know the trigger has compiled properly and is catching the error.

118236

Sorry I typed in the error wrong, it should be 'Select has returned more that one row'. I know that it occurs during an update so i believe the offending statement to be something like a Select within the Update statement.

MichaelS

Again I'd remind you to download and run SQL*Monitor as proposed in my previous post. Since all your executables are exe's I assume you are on windows which would make it very easy to capture the SQL between either client - server or server - database. Only prerequisite is that SQL*Net is used for communication protocol.

Peter Gjelstrup

I'm sorry, too :-)

but still not convinced..

I know these:

ORA-01422: exact fetch returns more than requested number of rows
ORA-01427: single-row subquery returns more than one row

Not quite what the same. Either the application has translated the message, or put it in itself.

Of course, there might others, but I don't know of any with exact that message. With or without (your) typo.

Still, you need to trace the session.

Regards
Peter

1 person found this helpful

118236

I tried to download SQL Monitor. It installed OK but when I try and run it it gives me this message "This version of Quest SQL Monitor has expired. You must download a new version before proceeding. Please go to www.toadsoft.com/downld.htm"

That's it. So I'm not having much luck with that route either. I'm also not entirely sure that it is pure SQL*net that the app is using. I know it uses a native PowerBuilder driver for Oracle but the application also needs an entry in tnsnames.ora as well.

118236

Thank you for being persistent. You are correct, it was the application that was generating the error not the database. I figured out how to do a trace on a users session and use tkprof and got the answer I am looking for. All the information everyone here posted helped me in some way. Thanks All!

SQL & PL/SQL

SMTP Mail Oracle 10g Database Configuration

Comments

Post Details