Run a stored procedure that populates a temporary table and then selects from the temporary table

Question

I have a stored procedure that populates a global temporary table.

CREATE GLOBAL TEMPORARY TABLE temptable
(
...
)
ON COMMIT DELETE ROWS;

I want to run a stored procedure and select rows added to a temporary table.

$conn = oci_connect(...);
$sql = "BEGIN POPULATETEMPTABLE END;"
$stmt = oci_parse($conn, $sql);
oci_execute($stmt);
oci_free_statement($stmt);

$sql = "SELECT ... FROM temptable";
$stmt = oci_parse($conn, $sql);
oci_execute($stmt);

while ($row = oci_fetch_assoc($stmt))
{
    ...
}

oci_free_statement($stmt);
oci_close($conn);

This code does not get any rows. So I decided to put the selection in the same round trip to the database:

$conn = oci_connect(...);
$sql = "
    BEGIN POPULATETEMPTABLE END;
    SELECT ... FROM temptable
"
$stmt = oci_parse($conn, $sql);
oci_execute($stmt);

while ($row = oci_fetch_assoc($stmt))
{
    ...
}

oci_free_statement($stmt);
oci_close($conn);

This gives me the error:

PLS-00103: Encountered the symbol "SELECT"

If I put the SELECT inside BEGIN ... END; then I get this error:

PLS-00428: an INTO clause is expected in this SELECT statement

How to run a stored procedure in PHP that populates a temporary table and then selects from it?

It works in SQL Developer but not in PHP.

use:

PHP 8.1.4
Oracle 19.0.0.0.0

Answer 1

The first problem is that oci_execute() defaults to commit, so any rows inserted by calling POPULATETEMPTABLE will be cleared by ON COMMIT DELETE ROWS; defined.

Fix this by changing to:

$sql = "BEGIN POPULATETEMPTABLE END;"
$stmt = oci_parse($conn, $sql);
oci_execute($stmt, OCI_NO_AUTO_COMMIT);

The second problem with putting a SELECT into a PL/SQL block is because this is invalid PL/SQL. Use reference cursors or implicit result sets. See the OCI8 documentation or the Underground PHP and Oracle Manual