Passing floats to oracle DB from PHP (PDO) raises ORA-01722 only if using bindParam

Question

Here is a float, declared in PHP:

$floatValue = 6.66;

Saving it to an Oracle database like this works fine:

$statement = $connection->prepare("INSERT INTO fooTable (numValue) VALUES($floatValue)");
$statement->execute();
//All good!

However, if I use bindParam, error ORA-01722 is raised:

$statement = $connection->prepare("INSERT INTO fooTable (numValue) VALUES(?)");
$statement->bindParam(1, $floatValue);
$statement->execute();
//ORA-01722 raised

This only happens with floats, ints are fine.

I tried changing the decimal separator in my OS, no problem there.

So what's happening here?

Why only floats?

Is there an alternative to bindParam specificaly for Floats..?

what happens when you add `PDO::PARAM_INT` or `PDO::PARAM_STR` as an additional argument? also checking for errors http://php.net/manual/en/pdo.error-handling.php should there be anything else that may have not been caught. — Funk Forty Niner, Oct 25 '17 at 23:43
Have a look at this entire post https://stackoverflow.com/questions/12549029/sql-error-ora-01722-invalid-number to see if there's anything in there that may be relevant to be tried. Also https://www.techonthenet.com/oracle/errors/ora01722.php — Funk Forty Niner, Oct 25 '17 at 23:45
NUMBER(2,5) is probably wrong, it should be NUMBER(5,2). But NUMBER should work. ORA-01722 is raised when Oracle tries to convert a STRING to a NUMBER; I don't know how the passing of numbers from PHP to Oracle works, is it always through string representation of numbers? If so, see if there is a mismatch in the character used as the decimal separator, between PHP and Oracle. — , Oct 26 '17 at 03:44
Thanks for the input guys! I've narrowed the problem down and edited the question consequently; the problem seems to come from the use of bindParam. — Félix Paradis, Oct 28 '17 at 16:50

score 1 · Answer 1 · answered Jun 29 '23 at 10:18

1

What was working for me is setting the ORACLE decimal separator (for the session) before the insert statement:

$stmt = $connection->exec( "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '");

answered Jun 29 '23 at 10:18

Feri Beke

11
1

score 0 · Answer 2 · answered Oct 30 '17 at 05:37

Works for me:

$floatValue = 6.66;
$statement = $connection->prepare("INSERT INTO fooTable (numValue) VALUES(?)");
$statement->bindParam(1, $floatValue);
$statement->execute();

$s = $connection->prepare('select * from fooTable');
$s->execute();
while ($r = $s->fetch(PDO::FETCH_ASSOC)) {
    var_dump($r);
}

gives the expected output:

array(1) {
  ["NUMVALUE"]=>
  string(4) "6.66"
}

This is expected because the fetch converts to string. In SQL*Plus:

SQL> desc fooTable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUMVALUE                                           NUMBER

SQL> select * from fooTable;

  NUMVALUE
----------
      6.66

Passing floats to oracle DB from PHP (PDO) raises ORA-01722 only if using bindParam

2 Answers2