php + oracle stored procedure (multiline output)

Question

So I have this code:

<?php
    ... 
    $query = 'DECLARE rs_tm varchar2(100); rs_vl number(10); 
    BEGIN TM(:param1_in, :param2_in, :param3_in, :param4_out, :param5_out); 
    END;';

    ...
    $stid = oci_parse($conn, $query);

    oci_bind_by_name($stid, ':param1_in', $v_instance_name);
    oci_bind_by_name($stid, ':param2_in', $v_ts1);
    oci_bind_by_name($stid, ':param3_in', $v_ts2);
    oci_bind_by_name($stid, ':param4_out', $v_result_timestamp,14);
    oci_bind_by_name($stid, ':param5_out', $v_result_value,5);
    oci_execute($stid);

    echo "<td>".$v_result_timestamp."</td><td>".$v_result_value."</td>";
?>

And it works just fine with this code inside TM procedure:

CREATE OR REPLACE PROCEDURE TM(
                            v_ins IN varchar2,
                            v_t1 IN varchar2,
                            v_t2 IN varchar2,
                            res_ts OUT varchar2,
                            res_val OUT NUMBER
                            ) IS 

BEGIN
  SELECT to_char(timestamp, 'DD.MM.YY HH24:MI'), value 
  INTO res_ts, res_val 
  FROM table1
  WHERE ins = v_ins
  AND timestamp BETWEEN to_timestamp(v_t1, 'DD.MM.YY HH24:MI') AND to_timestamp(v_t2, 'DD.MM.YY HH24:MI') 
  AND rownum = 1
  ORDER BY timestamp;
END TM;

But the thing is, I need multiple rows, so now I have to delete AND rownum = 1 from the procedure, but I don't know how to adjust my php code. If I don't do anything to php file, just changing the procedure, I am getting this error:

Warning: oci_execute() [function.oci-execute]: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "TM", line 10 ORA-06512: at line 1 in .../tmout.php on line 36

When I am tryin to use next code instead of the last line (echo):

 while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
     foreach ($row as $item) {
         echo "<td>".$v_rs_tm."</td><td>".$v_rs_vl."</td>";
     }
 }

I am getting:

Warning: oci_fetch_array() [function.oci-fetch-array]: ORA-24374: define not done before fetch or execute and fetch in ...tmout.php on line 38.

That is probably awful syntax, but the thing is - I don't know the correct.

Any idea? Thanks in advance

I think this is the restriction of procedure, not php_oci. After all, res_ts is a value, what would you expect it to be if it's not rownum=1? — Passerby
In that case, what should I use, if not variable out parameter? Cursor and fetch into? — Alexander
Worth a try. Also, any chance you can directly query the statements in procedure? — Passerby
Well, actually, I could! Does it make the whole thing easier? — Alexander
If your procedure actually is just SELECT like you post, it would definitely make things easier: $stid=mysqli_prepare("SELECT...");oci_bind_by_name(...);oci_execute($stid);while($row=oci_fetch_assoc($std)){...}. — Passerby

Alexander Alexander · Accepted Answer · 2013-12-18T11:51:32

Thanks to Passerby, the solution has been found.

In my case, I don't really need to use procedure, so I replaced it with a query.

I used print_r to find out, $row array looked like ([TM] => ..., [VL] => ...)

The result code is here:

    $query = "SELECT to_char(timestamp, 'DD.MM.YY HH24:MI') TM, value VL FROM transmaster WHERE instance_name = :param1 AND timestamp BETWEEN to_timestamp(:param2, 'DD.MM.YY HH24:MI') AND to_timestamp(:param3, 'DD.MM.YY HH24:MI') ORDER BY timestamp";
...
    $stid = oci_parse($conn, $query);
    oci_bind_by_name($stid, ':param1', $v_instance_name);
    oci_bind_by_name($stid, ':param2', $v_ts1);
    oci_bind_by_name($stid, ':param3', $v_ts2);
    oci_execute($stid);

    while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
                    echo '<tr><td>'.($row['TM']).'</td><td>'.($row['VL']).'</tr>';
    }

php + oracle stored procedure (multiline output)

1 Answers