Question 195487 in PHP Question posted in
Official manual can be found here.

Return output parameter of oracle stored procedure using PDO and PHP

Hi I’am trying to return a the value of output parameter of oracle stored procedure using PDO.

I have a class called Sql and in the constructor I am doing the connection to the database

then I am using those methods to call the procedure:

public function queryProc($rawQuery, $params = array())
{
    $stmt = $this->conn->prepare($rawQuery);
    foreach ($params as $key => $value) {
        $stmt->bindParam($key, $value[0], $value[1], $value[2]);
    }
    $stmt->execute();
    return $stmt;
}

public function callProc($rawQuery, $params = array())
{
    try {
        $stmt = $this->queryProc($rawQuery, $params);                   
    } catch (PDOException $e) {
        echo ($e);
    } finally {
        $this->conn = null;
    }
}

Then I call the class from my login file

$seller_id = 0;

$sql = new Sql();
$sql->callProc(
    'call PKG_VISCLI.LOGIN(:usuario,:senha,:token,:codusur)',
    array(
        ':usuario' => array($body_arr['username'],PDO::PARAM_STR,50),
        ':senha' => array($body_arr['password'],PDO::PARAM_STR,50),
        ':token' => array($token,PDO::PARAM_STR,50),
        ':codusur' => array($seller_id,PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT,50)
    )
);        
echo($seller_id);

when I print the $seller_id it is always 0 (not assigning any value to the variable).

the procedure work fine if call it directly from pl/sql

set serveroutput on

declare
x number(4,0);
begin
x := PKG_VISCLI.LOGIN('LEO','123','token');
DBMS_OUTPUT.PUT_LINE(A  => x);
end;

Any help please????

I tried to call the procedure using ‘begin procedure end; ‘ and still not working, I am stuck with this for 3 hours and keep not working for me.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So what I needed to do is just to bind the variable directly with out looping into parameters in array based on Barmar's answer.

    $sql = new Sql();
$stmt = $sql->getConnection()->prepare('call PKG_VISCLI.LOGIN(:usuario,:senha,:token,:codusur)');
    $stmt->bindParam(':usuario', $body_arr['username'], PDO::PARAM_STR, 50);
    $stmt->bindParam(':senha', $body_arr['password'], PDO::PARAM_STR, 50);
    $stmt->bindParam(':token', $token, PDO::PARAM_STR, 50);
    $stmt->bindParam(':codusur', $seller_id, PDO::PARAM_STR, 50);
    $stmt->execute();

  2. 0

    The $params parameters and $value need to be references so that you’ll modify the caller’s variables rather than copies.

    public function queryProc($rawQuery, &$params = array())
{
    $stmt = $this->conn->prepare($rawQuery);
    foreach ($params as $key => &$value) {
        $stmt->bindParam($key, $value[0], $value[1], $value[2]);
    }
    $stmt->execute();
    return $stmt;
}

public function callProc($rawQuery, &$params = array())
{
    try {
        $stmt = $this->queryProc($rawQuery, $params);                   
    } catch (PDOException $e) {
        echo ($e);
    } finally {
        $this->conn = null;
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search