what i need
i need to fetch json data from stored procedures
i have google a lot but cannot solve problem
source code
$param1 = 'abc'; $param2 = '79'; $param3 = 'Y'; $param4 = 121221; $sql1='CALL web_demo.wwv_json_data.dashboard(:param1, :param2 , :param3, :param4,@p_json)'; $qb = $em->getConnection()->prepare($sql1); $qb->bindParam('param1', $param1, PDO::PARAM_INPUT_OUTPUT, 4000); $qb->bindParam('param2', $param2, PDO::PARAM_INPUT_OUTPUT, 4000); $qb->bindParam('param3', $param3, PDO::PARAM_INPUT_OUTPUT, 1); $qb->bindParam('param4', $param4, PDO::PARAM_INPUT_OUTPUT, 4000); $st=$qb->execute(); $st->closeCursor(); $stmt = $qb->query("SELECT @p_json as json"); $result = $qb->fetchAll(PDO::FETCH_ASSOC);
procedure
PROCEDURE dashboard (p_customer_id IN VARCHAR2 DEFAULT NULL, p_user_id IN VARCHAR2 DEFAULT NULL, p_staff IN VARCHAR2 DEFAULT NULL,p_random IN VARCHAR2 DEFAULT NULL,out p_json clob) END dashboard
oracle sql developer code
DECLARE v clob; BEGIN web_demo.wwv_json_data.dashboard('abc','79','Y',121221,v); dbms_output.put_line(v); END;
return json
{"data":[{"open_log":46,"to_m":34,"to_clien":12,"to_m":0}]}
Error
An exception occurred while executing 'CALL web_demo.wwv_json_data.dashboard(:param1, :param2 , :param3, :param4,@p_json)': SQLSTATE[HY000]: General error: 936 OCIStmtExecute: ORA-00936: missing expression (extpdo_ocioci_statement.c:148)
- as i have googled & find that OUT can access cursor data.
what i need
- i need to fetch json data from stored procedure.
im accesing out variable like this
$stmt = $qb->query("SELECT @p_json as json"); $result = $qb->fetchAll(PDO::FETCH_ASSOC);
can anyone help me how fetch data through use of out variable in oracle and symfony2.
Advertisement
Answer
finally i got solution after long struggle
code snippet
<?php $userName = ""; $password = ""; $dtabasePort = "1521"; $serverName = "someip"; $c = oci_connect($userName, $password, '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME =dbname) (SID =dbname)))'); $sql = 'BEGIN web_demo.wwv_json_data.dashboard(:param1, :param2 , :param3, :param4,:param5); END;'; $stmt = oci_parse($c,$sql); $id; oci_bind_by_name($stmt,":param1",$param1,32); oci_bind_by_name($stmt,":param2",$param2,500); oci_bind_by_name($stmt,":param3",$param3,32); oci_bind_by_name($stmt,":param4",$param4,200); oci_bind_by_name($stmt,":param5",$id,200); // Explained in the next example... (use an empty value for now) //$param5 = oci_new_collection($c,'LIST_OF_NUMBERS'); //oci_bind_by_name($stmt,':param5',$param5,32,OCI_B_SQLT_NTY); // Create a new lob descriptor object // Execute the statement but do not commit oci_execute($stmt, OCI_DEFAULT); print_r($id); // Everything OK so commit oci_commit($c); if (!$c) { $m = oci_error(); echo $m['message'], "n"; exit; } else { print "Connected to Oracle!"; } print_r($c); ?>