Advanced Oracle PL/SQL Programming with Packages

6.4. PLV Utilities

PL/Vision comes with a set of utility procedures and functions. These programs offer shortcuts to executing commonly needed operations or information in PL/SQL programs. In some cases, the utility exists simply to make it possible to access the information from within a SQL statement. These programs are described below.

6.4.1. Converting Boolean to String

The boolstg function translates a Boolean expression into a string describing that Boolean's value. The header for boolstg is:

FUNCTION boolstg 
      (bool_in IN BOOLEAN, stg_in IN VARCHAR2 := NULL)
   RETURN VARCHAR2;

The second argument allows you to pass a string that is prefixed to the string describing the Boolean (TRUE, FALSE, or NULL). The various ways to call PLV.boolstg are illustrated below:

SQL> exec p.l(PLV.boolstg (TRUE));
TRUE
SQL> exec p.l(PLV.boolstg (TRUE, 'This is'));
This is TRUE

6.4.2. Obtaining the Error Message

The errm function provides a PL/SQL function interface to the SQLERRM builtin function. You cannot call SQLERRM in a SQL statement, which is annoying when you have error information in a SQL database table and you want to display the corresponding error message text. You want to do something like this:

SELECT errcode, SQLERRM (errcode)
  FROM error_log
 WHERE create_ts < SYSDATE;

but the SQL layer returns this error message:

ORA-00904: invalid column name

The errm function allows you to use SQLERRM inside SQL by hiding that builtin behind the function interface and by using the RESTRICT_REFERENCES ...