PHP: oci_bind_by_name and timestamp field results in “ORA-01461: can bind a LONG value only for insert into a LONG column”

Question

I have an Oracle database and need to insert a string with a date in YYYY-MM-DD HH:MM:SS format into an Oracle timestamp field. For this I have written this code:

$date = '2013-01-01 10:10:10';
$sql = oci_parse($c,"INSERT INTO MY_TABLE (ID, SEND_DATE) VALUES (MY_SEQ.nextval, TO_TIMESTAMP(:send_date, 'YYYY-MM-DD HH24:MI:SS'))");
oci_bind_by_name($sql, ':send_date', $date, null, SQLT_CHR);
oci_execute($sql);

The table looks like this:

CREATE TABLE "MY_TABLE" 
(   "ID" NUMBER NOT NULL ENABLE, 
    "SEND_DATE" TIMESTAMP (0) NOT NULL ENABLE );

If I execute the query above, I get this error:

ORA-01461: can bind a LONG value only for insert into a LONG column

There are already tons of questions regarding ORA-01461 on Stack Overflow, yet I could not find a solution for this particular problem. I really cannot understand where in this constellation LONG comes in.

Vladimir Vladimir · Accepted Answer · 2013-11-20T15:58:25

From (http://www.php.net/manual/en/function.oci-bind-by-name.php#92334) :

Sometimes you get the error "ORA-01461: can bind a LONG value only for insert into a LONG column". This error is highly misleading especially when you have no LONG columns or LONG values.

From my testing it seems this error can be caused when the value of a bound variable exceeds the length allocated.

To avoid this error make sure you specify lengths when binding varchars e.g.

<?php
    oci_bind_by_name($stmt,':string',$string, 256);
?>

And for numerics use the default length (-1) but tell oracle its an integer e.g.

<?php
    oci_bind_by_name($stmt,':num',$num, -1, SQLT_INT);
?>

PHP: oci_bind_by_name and timestamp field results in “ORA-01461: can bind a LONG value only for insert into a LONG column”

1 Answers