I am trying to execute and get data from my procedure:
Here is the way my procedure is defined:
create or replace PROCEDURE SP_GET_MY_DATA(
IN_POP VARCHAR2,
IN_SEG VARCHAR2,
IN_DUR VARCHAR2,
IN_VIEW INTEGER,
IN_PAGE INTEGER,
VIEW_DATA_CUR OUT SYS_REFCURSOR) AS ...
Here is my PHP code to execute and get data from procedure:
$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = JXYX.com)(PORT = 1521)))(CONNECT_DATA=(SID=DHSJKS)))";
$conn = ocilogon("XXXXXX","XXXXXXXX",$db);
$sql = 'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;';
$stmt = oci_parse($conn,$sql);
oci_bind_by_name($stmt,':POP',$pop);
oci_bind_by_name($stmt,':SEG',$seg);
oci_bind_by_name($stmt,':DUR',$dur);
oci_bind_by_name($stmt,':VIEW',$view);
oci_bind_by_name($stmt,':PAGE',$page);
$OUTPUT_CUR = oci_new_cursor($conn);
oci_bind_by_name($stmt,":OUTPUT_CUR", $OUTPUT_CUR, -1, OCI_B_CURSOR);
oci_execute($stmt, OCI_DEFAULT);
while ($data = oci_fetch_assoc($OUTPUT_CUR)) {
print_r($data);
}
But in doing so I am getting this error:
oci_fetch_assoc(): ORA-24374: define not done before fetch or execute and fetch".
I am not able to figure out what I'm missing. Can you help?
To work with a cursor in PHP three additional steps are required, as compared to accessing rows directly from a SELECT
statement.
oci_new_cursor()
function, which you then use to bind to the appropriate parameter.oci_bind_by_name()
functionoci_execute()
on the cursor resource.The code:
//Connection does not change
$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = JXYX.com)(PORT = 1521)))(CONNECT_DATA=(SID=DHSJKS)))";
$conn = ocilogon("XXXXXX","XXXXXXXX",$db);
//Request does not change
$sql = 'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;';
//Statement does not change
$stmt = oci_parse($conn,$sql);
oci_bind_by_name($stmt,':POP',$pop);
oci_bind_by_name($stmt,':SEG',$seg);
oci_bind_by_name($stmt,':DUR',$dur);
oci_bind_by_name($stmt,':VIEW',$view);
oci_bind_by_name($stmt,':PAGE',$page);
//But BEFORE statement, Create your cursor
$cursor = oci_new_cursor($conn)
// On your code add the latest parameter to bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt,":OUTPUT_CUR", $cursor,-1,OCI_B_CURSOR);
// Execute the statement as in your first try
oci_execute($stmt);
// and now, execute the cursor
oci_execute($cursor);
// Use OCIFetchinto in the same way as you would with SELECT
while ($data = oci_fetch_assoc($cursor, OCI_RETURN_LOBS )) {
print_r($data);
}
I'm not very fluent with Oracle (and english) so you should read this tutorial. There is an interesting example, look at the Stored Procedures and Reference Cursors chapter!
Hope it helps!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With