PHP/OCI - Unable to get results from Oracle procedure within a temporary table

Question

I am attempting to do two things using the PHP OCI Oracle functions:

1. Run a package procedure within an Oracle database.
2. Once the package has ran, query a temporary table to get the results of the procedure's operation.

I am able to do this successfully using the SQL Developer software provided from Oracle. My query is extremely basic and can been seen below:

BEGIN
    PKG_KTY_SEARCH.PR_PRICE_LIST();
END;
/
SELECT * FROM kty_web.KTY_PROD_PRICE_TEMP;

This code above works perfectly and I get a full table of results in SQL Developer.

I am attempting to do the same thing above in PHP using OCI. My code can be seen below:

<?php

// Load up the system.
require('../../system/init.php');

global $config;

$oracleDb = oci_new_connect($config['oracleDb']['username'], $config['oracleDb']['password'], $config['oracleDb']['connectionString']);

$firstStid = oci_parse($oracleDb, "BEGIN PKG_KTY_SEARCH.PR_PRICE_LIST(); END;");
oci_execute($firstStid);

$secondStid = oci_parse($oracleDb, "SELECT * FROM kty_web.KTY_PROD_PRICE_TEMP");
oci_execute($secondStid);

oci_fetch_all($secondStid, $result);

echo json_encode($result);

echo "<br />Import complete!";

?>

This however returns no errors, and an empty result set. I can't figure out why. Anybody seeing anything obvious here that I'm missing?

Result set returned from PHP

{"PRODUCT_ID":[],"CUST_ROLE":[],"MIN_QTY":[],"MAX_QTY":[],"PRICE":[]}

My connection string is as follows:

$config['oracleDb']['connectionString'] = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " . $config['oracleDb']['host'] . ")(PORT = " . $config['oracleDb']['port'] . ")))(CONNECT_DATA=(SID=" . $config['oracleDb']['sid'] . ")))";

I am using PHP7.1.22, and Oracle 11g database. I am able to query normal tables and get results without problems within PHP and get a full result set.

Answer 1

Is the temporary table defined as on commit delete rows or as on commit preserve rows?

By default, oci_execute will implicitly issue a commit after every successful call. Assuming your temporary table is defined as on commit delete rows, that will delete the rows before the subsequent query. You can change that behavior by passing an optional second parameter

oci_execute($firstStid, OCI_DEFAULT);

Assuming you do this, however, you'll want to do an explicit oci_commit in order to close the transaction you've opened.