Hussain Dholkawala Hussain Dholkawala - 9 days ago 5
PHP Question

I am getting below error running a PHP page running on PHP v5.4.16 (ZEND v2.0)

if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$sql = 'SQL Here';


Then, I fetch records and would like to display it into a simple
table as below. But get error while fetching the records. not sure what the issue is.

echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";

?>


Error message I get in the logs is:

[Tue Oct 25 10:41:24.186513 2016] [:error] [pid 16068] [client 10.170.33.235:59928] PHP Warning: oci_fetch_array(): ORA-24374: define not done before fetch or execute and fetch in /var/www/html/php/markitv7_1.php on line 45
[Tue Oct 25 10:42:09.508162 2016] [:error] [pid 16119] [client 10.170.33.235:59934] PHP Warning: oci_execute(): ORA-00904: &quot;TRADING_SYMBOL&quot;: invalid identifier in /var/www/html/php/markitv7_1.php on line 42
[Tue Oct 25 10:42:09.508319 2016] [:error] [pid 16119] [client 10.170.33.235:59934] PHP Warning: oci_fetch_array(): ORA-24374: define not done before fetch or execute and fetch in /var/www/html/php/markitv7_1.php on line 45

Answer

In Oracle you should use quotes for literals and double quotes for column_names . And you use double quotes in all places. Please change it and query become correct.

 if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }

    $sql = "SELECT
    e_ac.assetid as \"ASSET_ID\",
    e_ac.VALUE as \"ISIN\",
    e.CLUSTERID as \"CLUSTER_ID\",
    to_char(e.TRADEDATE,'DD-MON-YYYY HH24:MI') as \"TRADE_DATE\",
    to_char(e_c.PERSISTEDTIME,'DD-MON-YYYY HH24:MI') as \"PERSISTED_TIME\",
    to_char(e_c.SOURCELASTUPDATED,'DD-MON-YYYY HH24:MI') as \"SOURCE_LAST_UPDATED\" FROM tblEtp e
    JOIN tblAssetCode e_ac on e.EtpAssetID = e_ac.AssetID
    JOIN tblcluster e_c on e.CLUSTERID = e_c.CLUSTERID
    AND e_ac.CodeVendorID = (SELECT cv.CodeVendorID FROM tblCodeVendor cv WHERE Value = 'ISIN')
    AND NVL(e_ac.EffectiveStart,TO_DATE('1900-01-01','YYYY-MM-DD')) <= e.TradeDate
    AND NVL(e_ac.EffectiveEnd,TO_DATE('2099-12-31','YYYY-MM-DD')) >= e.TradeDate
    AND e_ac.MetaType = 'TRADING_SYMBOL' AND E_AC.VALUE in (select ins.isin_code from ire_etf_header hh
    join ire_etf_parent_company pp on pp.parent_company_id = hh.source_company_id
    join ire_dmz_storm_instruments ins on ins.tradeable_instrument_id = hh.etf_tradeable_instrument_id
    where hh.active != 'INACTIVE'
    and hh.data_source_type = 'MARKIT'
    and hh.source_company_id not in (1045, 1065, 1066)
    and hh.etf_tradeable_instrument_id in
    (select ss.etf_tradeable_instrument_id from ire_etf_status ss
    where ss.pcf_status_id = 660)
    and hh.etf_tradeable_instrument_id in
    (select etf_id from ire_etfpcf_migration where status_cd_id = 676))";

    $stid = oci_parse($conn, $sql);
    oci_execute($stid);