PHP with stored procedure - cursor output in Oracle

Question

I've been trying to get the result from a stored procedure in Oracle using PHP. This stored procedure has some input parameters and one output cursor which give me the result.

Here is my stored procedure:

CREATE OR REPLACE PROCEDURE "TEST1" (
  pPARAM1 CLOB,
  pPARAM2 VARCHAR2,
  p_record OUT SYS_REFCURSOR
)
AS  
BEGIN  
OPEN p_record FOR
  SELECT * FROM PARAMS  
  WHERE id_param = pPARAM1 AND id_param2 = pPARAM2;
END;

Here is my code:

$conexion = oci_connect($username, $password, $connection);
if (!$conexion) {
    $e = oci_error();
    echo $e['message'];
}
$params = array("pPARAM1" => 'apple', "pPARAM2" => 'banana');
$params_values = [];
$params_marks = [];
$marks = "";
$i = 0;

if(count($params) > 0) {
    foreach ($params as $key => $value) {
       $params_marks[$i] = ":" . $key;
       $params_values[$key] = $value;
       $i++;
    }

    $marks = implode(",", $params_marks);

    $query = 'BEGIN TEST1(' . $marks . ',:cursbv); END;';
} else {
    $query = 'BEGIN TEST1(:cursbv); END;';
}

$rs = oci_parse($conexion, $query) or die();

foreach ($params_values as $key => $value) {
    oci_bind_by_name($rs, $key, $params_values[$key]);
}
$cursor = oci_new_cursor($conexion);
oci_bind_by_name($rs, ":cursbv", $cursor, -1, OCI_B_CURSOR) or die();

oci_execute($rs);
oci_execute($cursor);
if ($rs) {
    while (($row = oci_fetch_array($cursor, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
        var_dump($row);
    }
}
oci_free_statement($rs);
oci_free_statement($cursor);

This give me a warning in oci_fetch_array but don't understand why because $cursor is a oci-resource. Need any suggestion. Thanks.

Note: My table have some CLOB columns and the stored procedure may have some CLOB input parameters.

Gerardo Charles Rojas Vega · Accepted Answer · 2015-06-17 18:09:50Z

2

If it's usefull for someone else: because of the use of CLOB columns and parameters I needed to add...

Binding variables:

$clob['apple'] = oci_new_descriptor($conexion, OCI_D_LOB);
oci_bind_by_name($rs, 'apple', $clob['apple'], -1, OCI_B_CLOB);
$clob['apple']->writeTemporary($params_values['apple']);

oci_bind_by_name($rs, 'banana', $params_values['banana']);

Fetching result:

while (($row = oci_fetch_array($cursor, OCI_ASSOC + OCI_RETURN_LOBS + OCI_RETURN_NULLS)) != false) {

answered Jun 17, 2015 at 18:09

Gerardo Charles Rojas Vega

6237 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP with stored procedure - cursor output in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related