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

user_1174795 0
Viewed 234

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.

oracle-call-interface oracle php
1 Answers

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.

Justin_Cave_103970
Related