#

| Chapter 6 PLV: Top-Level Constants and Functions |

## 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 pragma in the specification. With PLV, you change that SQL statement to:

>     SELECT errcode, PLV.errm (errcode)
>       FROM error_log
>      WHERE create_ts < SYSDATE;

and get the information you need to analyze and fix your problems.

### 6.4.3 Retrieving Date and Time

The `now` function is simply a quick way to display the current date and time. Its header is:

>     FUNCTION now RETURN VARCHAR2;

I built `PLV.now` because I got tired of typing:

>     SELECT TO_CHAR (SYSDATE, 'HH:MI:SS') FROM dual;

just to see the current time. With `PLV.now`, you can at any point see both the date and time from within SQL*Plus with either of these commands:

>     ------------------------------------------
>     SQL> SELECT PLV.now from DUAL;
>     ------------------------------------------

>     August 3, 1996 20:19:35

>     SQL> exec p.l(PLV.now);
>     August 3, 1996 20:20:48

### 6.4.4 Pausing Your Program

The `pause` procedure of the PLV package provides a cover for the DBMS_LOCK.SLEEP procedure; its header is:

>     PROCEDURE pause (seconds_in IN INTEGER);

Why bother providing this `pause` program, when it is nothing more than a call to the builtin SLEEP procedure? Most PL/SQL developers will never use the DBMS_LOCK package; few of us need to create and manipulate locks with the Oracle Lock Management services. Yet this package contains SLEEP because it is the context in which Oracle developers realized they needed this capability.

The `PLV.pause` procedure offers, at least within PL/Vision, a more rational location for this technology.

The following "infinite" loop uses `PLV.pause` to make sure that there is an hour's delay between each retrieval of data from a DBMS_PIPE named `hourly_production`.

>     LOOP
>        process_line_data ('hourly_production');
>        PLV.pause (60 * 60);
>     END LOOP;

6.3 Assertion Routines |  | 6.5 The Predefined Datatypes

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