Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 9.3 Retrieving Message TextChapter 9
PLVmsg: Single-Sourcing PL/SQL Message Text
Next: 9.5 Integrating PLVmsg with Error Handling
 

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;


Previous: 9.3 Retrieving Message TextAdvanced Oracle PL/SQL Programming with PackagesNext: 9.5 Integrating PLVmsg with Error Handling
9.3 Retrieving Message TextBook Index9.5 Integrating PLVmsg with Error Handling

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference