Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Oci8 Adapter param length and 'out' variable #4088

Open
pkaso opened this Issue · 3 comments

4 participants

@pkaso

There is a problem in calling to Oracle function and getting back returned values.

In case you want to call Oracle function:

function test_in_out(somevar out varchar) return varchar is
  returnvar varchar(1000);
    begin
      begin
        somevar := 'out';
        returnvar := 'output';
      end;
        return(returnvar);
    end;

With this code:

$returnvar = 'in';
$outvar = 'in';
$databaseAdapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
$stmt = $databaseAdapter->createStatement('begin :returnvar := test_in_out(:outvar); end;');
$stmt->execute(array(
  ':returnvar' => $returnvar,
  ':outvar' => $outvar
));
var_dump($returnvar); // expecting 'output' value
var_dump($outvar); // expecting 'out' value

There are two problems:
1) because all params are binded with length -1 (Zend\Db\Adapter\Driver\Oci8\Statement.php)

oci_bind_by_name($this->resource, $name, $value, -1, $type);

Oracle will return error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

2) even if I changed the bind length, it's not posible to access return values from Oracle, because $returnvar and $outvar still contains initial value 'in', the same in ParameterContainer ($stmt->getParameterContainer()).

Maybe the Statement should have some kind of Bind function, to define length and corect value reference for these special cases.

Thanks,
Martin

@evertonce

The best solution for in and out parameters:

procedure zf2_call(p_id in out number) as
begin
null;
end;

$adapter = $this->getAdapter();

$statement = $adapter->createStatement();
$statement->prepare("call zf2_call(:id)");

$parameterContainer = new \Zend\Db\Adapter\ParameterContainer;
$parameterContainer->offsetSet('id', 9, $parameterContainer::TYPE_INTEGER); // In parameter
$parameterContainer->offsetSetReference('id', 'id'); // Out parameter
$statement->setParameterContainer($parameterContainer);
$statement->execute();

print_r($parameterContainer->getNamedArray()); // Get all parameters

@anilyeni

This works wierd for me.
It only returns the first char of the output, has anyone experinced that ?

I used classic php method to solve it.

//$parameterContainer->offsetSetReference('ps_out_error_text', 'ps_out_error_text');
$statement->setParameterContainer($parameterContainer);
$prepare = $statement->prepare();
oci_bind_by_name($prepare->getResource(),"ps_out_error_text", $errorText, 100);
$execute = $statement->execute();
var_dump(errorText);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.