|
The DBMS_OUTPUT package is the standard way to “print” output in PL/SQL. The drawback is
that it is not asynchronous. The PL/SQL procedure or block runs to completion before any
output is returned.
DBMS_OUTPUT is like a buffer. Your code turns on buffering, puts some output in the
buffer, and then fetches from the buffer. Other database connections cannot access your
buffer.
Here are sample functions to enable and fetch DBMS_OUTPUT.
navioo_dbmsoutput.inc
<?php
// Turn dbms_output ON or OFF
function SetServerOutput($c, $p)
{
if ($p)
$s = "BEGIN DBMS_OUTPUT.ENABLE(NULL); END;";
else
$s = "BEGIN DBMS_OUTPUT.DISABLE(); END;";
$s = oci_parse($c, $s);
$r = oci_execute($s);
oci_free_statement($s);
return $r;
}
// Returns an array of dbms_output lines, or false.
function GetDbmsOutput($c)
{
$res = false;
$s = oci_parse($c, "BEGIN DBMS_OUTPUT.GET_LINE(:LN, :ST); END;");
if (oci_bind_by_name($s, ":LN", $ln, 255) &&
oci_bind_by_name($s, ":ST", $st)) {
$res = array();
while (($succ = oci_execute($s)) && !$st)
$res[] = $ln;
if (!$succ)
$res = false;
}
oci_free_statement($s);
return $res;
}
?>
DBMS_OUTPUT line size limit prior to Oracle 10g Release 10.2.
To use GetDbmsOutput(), enable buffering before executing the PL/SQL code that
creates output.
navioo_dbmsoutput.php
<?php
include("navioo_dbmsoutput.inc");
$c = oci_connect("hr", "hrpwd", "//localhost/XE");
// Turn on buffering of output
SetServerOutput($c, true);
// Create some output
$s = oci_parse($c, "call dbms_output.put_line('Hello, world!')");
oci_execute($s);
// Display the output
$output = GetDbmsOutput($c);
foreach ($output as $line)
echo "$line<br>";
?>
The output is the put_line() text:
Hello, world!
If you expect large amounts of output, you may want to write your own code to stream results
as they are fetched from the database instead of returning them in one array from
DbmsOutputFetch().
In Oracle 10g Release 10.2, the maximum line length was changed from 255 bytes to
32Kb and a theoretically infinite number of lines can now be returned. Avoid binding 32Kb,
especially if the database is running in Oracle’s shared server mode. If you bind this size,
then it is easy to slow performance or get memory errors.
If DBMS_OUTPUT does not suit your application, you can also get output by logging to
database tables or using packages like UTL_FILE and DBMS_PIPE to asynchronously display
output to a separate terminal window
|
