I/O in PL/SQL

From: Tim Smith <tssmith_at_netcom.com>
Date: Tue, 13 Oct 1992 08:39:59 GMT
Message-ID: <1992Oct13.083959.6957_at_netcom.com>

There were several questions within the past month about doing i/o from a PL/SQL program.

When ORACLE7 goes to production release, there will be several ways to do non-database input/output to/from PL/SQL V2.0.

The first is a general package, called dbms_output, that includes routines like put (overloaded by type for NUMBERs, VARCHAR2s, and DATES), put_line (like put but adding a newline), new_line, get_line, get_lines, and more. These routines put output to a buffer (stored in the SGA), or retrieve output from the buffer, within a single session.

The big win here is that SQL*DBA and SQL*Plus will have an option (SET SERVEROUTPUT ON/OFF) that can, when activated, automatically collect (get_line) buffered output and print it on the active client terminal. So, as you write and debug your stored procedures, you can see debug output as you execute them from within SQL*DBA (or SQL*Plus).

The second input/output mechanism involves a more generalized named pipes package. A PL/SQL program can put data to a named pipe, and that output can be read by a PL/SQL program in another session. This could be used for debugging. For example, the PL/SQL program you are debugging writes debug info to a named pipe. In another window on your terminal, you have a session involving an OCI or Pro*? program that calls PL/SQL routines to read the named pipe, and print the debug output.

There are many other uses for named pipes. For example, you can implement external routines written in C or any other supported host language that use pipes to receive parameters from stored PL/SQL

procedures in the database, and return information to stored
procedures.  By this means, an external server that does things
like retrieve stock prices, or read temperatures in a mixing vat,

can respond to and return info to a database stored procedure. The possibilities are endless.

--Tim (tssmith_at_oracle.com) or (tssmith_at_netcom.com) Received on Tue Oct 13 1992 - 09:39:59 CET