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;