Skip to content
Advertisement

stored procedure with OUT variable not working with oracle & symfony2

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);
                                    ?>
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement