PHP:oci_bind_by_name 和timestamp 字段导致“ORA-01461:只能为插入到LONG 列而绑定LONG 值"； [英] PHP: oci_bind_by_name and timestamp field results in "ORA-01461: can bind a LONG value only for insert into a LONG column"

问题描述

我有一个 Oracle 数据库，需要将一个日期为 YYYY-MM-DD HH:MM:SS 格式的字符串插入到 Oracle 时间戳字段中.为此，我编写了以下代码:

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);

表格如下所示:

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:只能为插入到 LONG 列而绑定 LONG 值

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

在 Stack Overflow 上已经有大量关于 ORA-01461 的问题，但我找不到针对这个特定问题的解决方案.我真的无法理解这个星座的 LONG 是从哪里来的.

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.

推荐答案

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

有时您会收到错误ORA-01461:只能为插入到 LONG 列而绑定 LONG 值".此错误具有很强的误导性，尤其是当您没有 LONG 列或 LONG 值时.

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.

为避免此错误，请确保在绑定 varchars 时指定长度，例如

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

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

对于数字，使用默认长度 (-1) 但告诉 oracle 它是一个整数，例如

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 和timestamp 字段导致“ORA-01461:只能为插入到LONG 列而绑定LONG 值"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！