CODEWITHSUNDEEP
oracle-databaseplsql
vr552
vr552

Reputation: 301

Is there an Oracle RAISE_APPLICATION_ERROR alternative for information messages?

Currently there is one place in our code that uses RAISE_APPLICATION_ERROR:

IF v_cl_type_row.code = 'B_DOCUMENT' THEN
  RAISE_APPLICATION_ERROR(-20000, '*A message for the user*');
END IF;

It's not an error that happens here, the user just gets an informational message. Because this is currently raised as an error, it is then also logged as an error, which it is not. So I have been tasked with somehow returning this as an info message instead, to avoid unnecessary logging.

But is that even possible with Oracle inbuilt functions? I haven't been able to find a way how to do something like that.

We are using Oracle 19c.

Upvotes: 0

Views: 683

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

IF-THEN-ELSE means PL/SQL. Code written in that language are executed within the database. PL/SQL isn't "interactive".

If you use a tool like SQL*Plus (command line) or GUI (such as SQL Developer, TOAD, etc.) you could use DBMS_OUTPUT.PUT_LINE instead of RAISE_APPLICATION_ERROR because - as you already noticed - RAISE stops the execution; user sees the message, but there's nothing they can do to make the procedure continue its work.

In your example, it would be

IF v_cl_type_row.code = 'B_DOCUMENT' THEN
   DBMS_OUTPUT.PUT_LINE('*A message for the user*');
END IF;

Note that you'd have to wait until procedure finishes to see all messages at once; Oracle won't display them one-by-one, as code reaches that point.

However, as far as it is OK to use for debugging purposes, users most probably don't use any of tools I previously mentioned. If it were e.g. Oracle Forms application or Apex one, that code would work (i.e. wouldn't raise a compilation error), but nobody would see anything because these front-ends aren't capable of displaying such a message.

In Forms, for example, you could use message built-in (text would then be displayed in the status line, at the bottom of the screen) or alert (so you'd see a pop-up window which lets you acknowledge the message and move on, i.e. code would proceed its execution).

Therefore, it depends on which tool you actually use to develop that application and its capabilities of interaction with end users.

Upvotes: 1

Related Questions