I am attempting to retrieve data from an oracle stored procedure. Read only.
The retrieved data will be inserted into a separate mysql db.
This is uncharted water for me. Reading many examples via google, but still can not get it
to work.
I have tried multiple variations of the code below, and usually get this type of error:
oci_bind_by_name(): ORA-01036: illegal variable name/number
on this line of code:
[php]oci_bind_by_name($stmt,’:p_results’, $recordset,-1) or die (‘Can not bind variable’);[/php]
The stored procedure takes 2 input variables: p_startdate, p_enddate
and has one output variable: p_results
For testing purposes, I have just hardcoded the input variables.
[php]
<?php include('../config/cat_dbconfig.php'); $p_startdate = 04/21/2012; $p_enddate = 03/22/2012; $query = "begin TRIAGE_CIT1.GET_CHANGES('$p_startdate', '$p_enddate', p_results ); end;"; echo "Connection is " . $conn; echo ''; $recordset = ':p_results'; $stmt = oci_parse($conn, $query) or die ('Can not parse query'); oci_bind_by_name($stmt,':p_results', $recordset,-1) or die ('Can not bind variable'); oci_execute($stmt) or die ('Can not Execute statment'); oci_execute($recordset) or die ('Can not execute recordset'); $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS); foreach ($row as $item) { print $item."
\n"; } oci_free_statement($stmt); oci_close($conn); ?>[/php]
Here is the stored procedure:
[code]CREATE OR REPLACE procedure TRIAGE_CIT1.Get_changes(p_startdate IN DATE,p_enddate IN DATE,p_results OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_results FOR SELECT Changeno Changeno,
NULL domid,
CASE
WHEN (Riskpercentile) >= 80 THEN
'RED'
WHEN (Riskpercentile) IS NULL THEN
'GREY'
ELSE
'GREEN'
END Failurealert,
NVL(Riskpercentile, -1) Riskpercentile,
Decode(Targetstart,
NULL,
'NA',
to_char(Targetstart, 'Dy Mon dd rrrr hh:mi') || ' EST') Startdate,
Decode(Targetend,
NULL,
'NA',
to_char(Targetend, 'Dy Mon dd rrrr hh:mi') || ' EST') Enddate,
Title Title,
Progress Phase,
Status Changestatus,
oig_names OIG,
lab_names ApprovingLab,
NULL Reviewed,
NULL Heightenedawarness,
NULL Labreview,
NULL Hamarkedby,
NULL Labrevmarkedby,
NULL Heightenedawarnessmaillist,
NULL Labreviewmaillist,
CASE
WHEN Targetstart IS NULL THEN
'NA'
WHEN substr(to_char(Targetstart, 'dd/mm/rrrr hh:mi:ss'), 12, 17) IS NULL THEN
To_Char(Targetstart, 'Dy Mon dd rrrr ') || '12:00 AM EST'
ELSE
To_Char(Targetstart, 'Dy Mon dd rrrr hh:mi AM') || ' EST'
END Startdatenew,
CASE
WHEN Targetend IS NULL THEN
'NA'
WHEN substr(to_char(Targetend, 'dd/mm/rrrr hh:mi:ss'), 12, 17) IS NULL THEN
To_Char(Targetend, 'Dy Mon dd rrrr ') || '12:00 AM EST'
ELSE
To_Char(Targetend, 'Dy Mon dd rrrr hh:mi AM') || ' EST'
END Enddatenew
FROM Cat_Changedtls a
WHERE a.targetend BETWEEN TO_DATE(p_startdate, ‘mm/dd/rrrr hh24:mi’) AND
TO_DATE(p_enddate, ‘mm/dd/rrrr hh24:mi’)
AND lower(CHNAME) LIKE ‘emxech%’;
end Get_changes;
/[/code]
Thank you