Advanced Oracle PL/SQL Programming with Packages

9.4. The Restriction Toggle

Use the restriction toggle to determine whether messages for errors numbers that are legitimate Oracle error numbers will be retrieved from the PLVmsg PL/SQL table (unrestricted) or from the SQLERRM function (restricted). A legitimate Oracle error number is an integer that is negative or zero or 100 (equivalent to -1403 or "no data found").

The restriction toggle is composed of three programs:

PROCEDURE restrict;
   PROCEDURE norestrict;
   FUNCTION restricting RETURN BOOLEAN;

When you call the PLVmsg.restrict procedure (and this is the default setting), PLVmsg will rely on SQLERRM whenever appropriate to retrieve the message for a legitimate Oracle error number.

If you call norestrict , PLVmsg will first check the PL/SQL table of PLVmsg to see if there is an error message for that error. In unrestricted mode, therefore, you can automatically substitute standard Oracle error messages with your own text—and be as selective as you like about the substitutions.

The restricting function will let you know the status of the restrict toggle in PLVmsg. It returns TRUE if you are restricting error messages to SQLERRM; otherwise, it will return FALSE.

Here are examples of the toggle in use:

1. In a SQL*Plus script, direct all error messages to be retrieved from the PL/SQL table, if present.

   PLVmsg.norestrict;
   transfer_data;

2. At the start of a SQL*Plus session, make sure that Oracle messages will be used whenever possible.

   SQL> exec PLVmsg.restrict;