PHP Oracle Cursors
Posted: Mon Jan 12, 2009 1:22 am
I am trying to find out if it is possible to use 2 reference cursors. Using 1 works 100% but I cannot get 2 cursors to return anything yet alone getting the oracle procedure to execute successfully. When I execute the statement it does not return anything. Below is the main section of the code. Question is can I use 2 oracle cursors in the same function and am I declaring the sql statement correctly or anything I am not doing correctly? ($this->db->executeDBStatement() executes the oracle statement).
// SQL declaration
$sql = "DECLARE p_dummy_cur PM018.t_web_cur;p_excess_cur PM018.t_web_cur;BEGIN:ret:=PM055.EXCESS_WORDING_PL(:p_agr_line_no,:p_dummy_cur,:p_excess_cur,:p_message); END;";
// Create the database statement
$stmt = oci_parse ( $dbci, $sql );
$refcur1 = oci_new_cursor( $dbci );
$refcur2 = oci_new_cursor( $dbci );
// Now bind the values of the fields to the database statement using cursors
// Both these statements are return and need to be assigned in the following format
oci_bind_by_name ( $stmt, ":ret", $ret, 7 );
oci_bind_by_name ( $stmt, ":p_agr_line_no", $agr_line_no);
oci_bind_by_name ( $stmt, ":p_dummy_cur", $refcur2, -1, OCI_B_CURSOR);
oci_bind_by_name ( $stmt, ':p_excess_cur', $refcur1, -1, OCI_B_CURSOR);
oci_bind_by_name ( $stmt, ":p_message", $p_message, 2000);
// Execute the statement
$exec = $this->db->executeDBStatement( $stmt );
if( $exec ) {
// Only continue if the action in the package was successful
// If not then return the package error message i.e. p_message
oci_execute( $refcur1 );
oci_execute( $refcur2 );
}
// SQL declaration
$sql = "DECLARE p_dummy_cur PM018.t_web_cur;p_excess_cur PM018.t_web_cur;BEGIN:ret:=PM055.EXCESS_WORDING_PL(:p_agr_line_no,:p_dummy_cur,:p_excess_cur,:p_message); END;";
// Create the database statement
$stmt = oci_parse ( $dbci, $sql );
$refcur1 = oci_new_cursor( $dbci );
$refcur2 = oci_new_cursor( $dbci );
// Now bind the values of the fields to the database statement using cursors
// Both these statements are return and need to be assigned in the following format
oci_bind_by_name ( $stmt, ":ret", $ret, 7 );
oci_bind_by_name ( $stmt, ":p_agr_line_no", $agr_line_no);
oci_bind_by_name ( $stmt, ":p_dummy_cur", $refcur2, -1, OCI_B_CURSOR);
oci_bind_by_name ( $stmt, ':p_excess_cur', $refcur1, -1, OCI_B_CURSOR);
oci_bind_by_name ( $stmt, ":p_message", $p_message, 2000);
// Execute the statement
$exec = $this->db->executeDBStatement( $stmt );
if( $exec ) {
// Only continue if the action in the package was successful
// If not then return the package error message i.e. p_message
oci_execute( $refcur1 );
oci_execute( $refcur2 );
}