#

| Chapter 9 PLVmsg: Single-Sourcing PL/SQL Message Text |

## 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;

9.3 Retrieving Message Text |  | 9.5 Integrating PLVmsg with Error Handling

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.