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 ...
Get Advanced Oracle PL/SQL Programming with Packages now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.