There is a procedure procedure some_procedure(id in integer, data_json out varchar2) , which takes a certain parameter and returns values ​​in a JSON format string.
I'm trying to get this line on php:

 $sql = "begin some_procedure(:ID, :JSON);end;"; try{ $i=0; //ID $arr[$i]['name']='ID'; $arr[$i]['val']=$id; $arr[$i]['len']=10; $i=0; $res[$i]['name']='JSON'; $res[$i]['len']=1000; $res=db_call_func($sql, $arr, $res, $conn); var_dump($res["JSON"]); $error = oci_error($res['stmt'])['message']; if($error){ var_dump('error = '); var_dump($error); } }catch(Exception $exc) { var_dump($exc->getMessage()); } function db_call_func($sql,$arr=null,$res=null, $conn){ $stmt = oci_parse($conn, $sql); foreach ($arr as $a){ oci_bind_by_name($stmt, ":".$a['name'], $a['val'], $a['len']); } $resarr = []; foreach ($res as $r){ oci_bind_by_name($stmt, ":".$r['name'], $resarr[$r['name']], $r['len']); } oci_execute($stmt); oci_commit($conn); $resarr['stmt']=$stmt; return $resarr; }

in the end, when var_dump ($ res ["JSON"]), I get NULL. Please tell me what I'm doing wrong and how to fix it?

  • What is db_call_func (this is clearly not a standard php function) and what it does in case of an error. it is necessary to check for a database error and display it if it exists. Especially since this begin seems to me strange, to say the least, oracle procedures are not so called - Mike
  • slightly corrected the code. db_call_func is just below the query. $ conn is a string connecting to the database. Other requests are processed by this function - Ann
  • oci_error ($ res ['stmt']) ['message']; does not return an error; the result of executing the function in the form in which it is presented in the question will be null, although when calling a function in TOAD, the result is not NULL, the string "{'value1': 12; 'value2': 21}". Could it have something to do with escaping the characters in the return value? - Ann
  • doubt it. data doesn't care what they contain; they must be put in a bound variable as is. but how the oci functions work with the returned parameters I am not aware of, I have never come across this - Mike
  • The solution to the problem could not be found, so the SQL procedure is rewritten so that it stores the data in a temporary table, and then with the usual query I take this data into PHP. The main thing to use for the procedure call and data request is the same connection (oci_connect) - Ann

0