php oci_bind_by_name float to numeric

Question

I need to bind floating point number to OCI statement.

What I'm doing:

$price = 0.1
oci_bind_by_name($resource, 'price', $price);

In my Oracle DB 'price' is an argument of the stored procedure and it's type is NUMERIC.

After executing my statement I'm getting the following error:

Message: oci_execute() [function.oci-execute]: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1

If $price is an integer everything works fine. In PHP docs http://lv.php.net/manual/en/function.oci-bind-by-name.php I haven't found a special type for floats for the fifth parameter (int $type = SQLT_CHR).

Answer found: I just changed decimal symbol in my OS from "," to "." and now everything works fine

Answer 1

Try: oci_bind_by_name($resource, 'price', $price, -1, SQLT_NUM); SQLT_NUM is just missing from the documentation.

Answer 2

If you can't change the decimal symbol of your OS (or you simply don't want to), the only solution for this issue is to avoid float parameters. You must enter the the value directly into the sql. You must also be aware to use en_US as locale for the correct decimal separator.

// Ensure that the period is used as decimal separator when converting float to string
setlocale(LC_ALL, 'en_US');

// Generate SQL
// ...
$variables = array();
if(is_int($myValue))
{
    $sql .= ':MYVALUE';
    $variables[':MYVALUE'] = $myValue;
}
else if(is_float($myValue))
{
    $sql .= (string) $myValue;
}
// ...

// Generate statement
// $resource = oci_parse(...);

// Bind parameters (if neccessary)
if(count($variables) > 0)
{
    foreach($variables as $name => &$variable)
        oci_bind_by_name($resource, $name, $variable);
}