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.
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
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.
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
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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.