SQLERRM is being overwritten

Question

I'm using SQLERRM. But there are more procedures running parallel, so sometimes SQLERRM is overwritten by other ones. With running parallel I mean I run procedure a and procedure b in the same time.SQLERRM is a global function and it is my problem. If the exception is thrown in both procedures, it could happen, the procedure a modifies the error message in procedure b and procedure b gets the wrong message. These procedures are using simple construction:

BEGIN
--do something/cutted off
EXCEPTION
        WHEN OTHERS THEN
            write_log(SYSDATE, 'ERROR_1', SQLERRM);

END;

These procedures calls write_log , declared as

 CREATE OR REPLACE PROCEDURE WRITE_LOG
 (
        TS_START IN DATE DEFAULT SYSDATE,
        ERR_CODE IN VARCHAR DEFAULT NULL,
        ERR_DESC IN VARCHAR DEFAULT NULL
 ) 
 IS

 BEGIN
  INSERT INTO LOG
        ( LOG_TS_START, LOG_ERR_CODE, LOG_ERR_DESC)
    VALUES
        (TS_START, ERR_CODE, ERR_DESC);


   COMMIT;

END WRITE_LOG;

How can I avoid overwriting of SQLERRM ? The logs are being written, but the error message is wrong.

Do you mean the value processed by `write_log` isn't what you expect? How is `write_log` defined - is it referring directly to `SQLERRM` itself or the procedure parameter? By 'running parallel', do you mean `write_log` is declared with `pragma autonomous_transaction`? — Alex Poole, Oct 22 '14 at 16:14
Welcome to the `PL/SQL` world and the common bugs. `EXCEPTION WHEN OTHERS`? A real bug. — Lalit Kumar B, Oct 22 '14 at 16:34
@LalitKumarB - I was really hoping that was a simplification for posting, but I guess not reading it again. I don't think that's relevant to whatever the issue actually is though. It really isn't clear what that is... — Alex Poole, Oct 22 '14 at 16:37
You might be right, but, the error stack will never be the same with an exception block. — Lalit Kumar B, Oct 22 '14 at 16:41
To perfectly debug, it is better to remove the eception blocks from everywhere. — Lalit Kumar B, Oct 22 '14 at 16:43
Please edit your post and include the `write_log` procedure so we can see what's going on in there. Thanks. — Bob Jarvis - Слава Україні, Oct 22 '14 at 19:25
@user897237 - SQLERRM isn't a global variable, it's a function. (Unless you've spmehow tried to redefine it.) Are you actually seeing a problem, or do you just think it *could* happen? — Alex Poole, Oct 22 '14 at 21:24
@AlexPoole ok, corrected my mistake. The problem happens, it is not just a guess. — user897237, Oct 22 '14 at 22:32
Please include the *body* of the function. The definition is helpful, but doesn't help to explain the problem. Thanks. — Bob Jarvis - Слава Україні, Oct 22 '14 at 23:36
The problem might be that the "write_log" procedure needs to be defined as an autonomous transaction, otherwise the insert to the log will get rolled back with the main transaction. For [example](http://www.oracle-base.com/articles/misc/autonomous-transactions.php) — tbone, Oct 23 '14 at 01:12
@tbone: my problem is not, the fact, that the logs are not there. But the fact, that procedure a logs with the message from procedure b. — user897237, Oct 23 '14 at 06:34
What do you mean by "procedure a" and "procedure b"? All you have posted is a single anonymous block. Also, what do you mean by "parallel" in this case? — Jeffrey Kemp, Oct 23 '14 at 06:40
@JeffreyKemp: I run procedure a and procedure b in the same time. Both are using this anonymus block. — user897237, Oct 23 '14 at 07:32
It's impossible to tell why two sessions would conflict without more details. How are you determining that the messages being written are wrong? — Jeffrey Kemp, Oct 23 '14 at 07:38

score 1 · Answer 1 · answered Oct 22 '14 at 16:30

1

First of all, get rid of the common bug in PL/SQL code.

WHEN OTHERS THEN without a RAISE and without any rational logic is just uselss, and nothing less than a bug.

Remove all exception blocks, and try again. And, follow good coding practices.

answered Oct 22 '14 at 16:30

Lalit Kumar B

47,486
13
97
124

+1 from someone who's wasted weeks of his life debugging applications that silently fail and don't even record the line number of the error. – Jon Heller Oct 22 '14 at 19:12
1

Agreed 100% with your sentiment, but this doesn't answer the OP's question. – Jeffrey Kemp Oct 23 '14 at 06:38

Bob Jarvis - Слава Україні · Answer 2 · 2014-10-23T13:02:31.973

1

The procedure WRITE_LOG is not using the parameters passed in to perform the INSERT. Try it as:

 CREATE OR REPLACE PROCEDURE WRITE_LOG
   (TS_START IN DATE DEFAULT SYSDATE,
    ERR_CODE IN VARCHAR DEFAULT NULL,
    ERR_DESC IN VARCHAR DEFAULT NULL)
 IS    
 BEGIN
   INSERT INTO LOG
     (LOG_TS_START, LOG_ERR_CODE, LOG_ERR_DESC)
   VALUES
     (TS_START, ERR_CODE, ERR_DESC);

   COMMIT;
END WRITE_LOG;

In addition, in my opinion logging errors to a database table is absolutely wrong. Because you have a COMMIT statement in your logging procedure you're going to commit all changes up to the point of the error which is very likely NOT what you want to do. You can mess around with autonomous transactions if you want, but for my money the best thing to do is log to a flat file.

Share and enjoy.

edited Oct 23 '14 at 13:02

answered Oct 23 '14 at 10:15

Bob Jarvis - Слава Україні

48,992
9
77
110

The function uses the arguments, was a typo(I have changed some names for simplification). Edited the query. – user897237 Oct 23 '14 at 12:27
When you're posting code please copy-and-paste the code that's really being used rather than re-typing it, which will eliminate issues such as this. Thanks. – Bob Jarvis - Слава Україні Oct 23 '14 at 13:04

score 0 · Answer 3 · edited May 23 '17 at 10:33

Writing to a log table is very common, but it is standard practice to use an autonomous transaction for logging. This is because you don't want a rollback in the main transaction to affect the logging, or a commit in the logging to affect the main transaction. (@BobJarvis example is not autonomous).

A simple example is shown here.

There are some other things to consider with a log table. For one, you should have a log_id that is using either a sys_guid (defined on the table) or a sequence on a trigger. Also, use a context field that the write_log can use to differentiate what procedure (or session) is writing the log message.

Once you add this setup (including the context), then run procedure a and procedure b again (concurrently), and pass in the context the procedure name (a or b). You will be able to see both a and b log messages in the table.

SQLERRM is being overwritten

3 Answers3