OCI_CONNECT是否可以导致ORA-01438:值大于此列允许的指定精度? [英] Can OCI_CONNECT cause a ORA-01438: value larger than specified precision allowed for this column?

问题描述

我想知道oci_connect()是否会导致1438错误，因为我一直都这样:

I'm wondering if oci_connect() can cause a 1438 error, because i get this all the time:

警告:oci_connect()[function.oci-connect]:ORA-00604:错误 发生在递归SQL级别1 ORA-01438:值大于 该列允许的指定精度ORA-06512:在第8行中 /xxxxxx/some.php在第220行

Warning: oci_connect() [function.oci-connect]: ORA-00604: error occurred at recursive SQL level 1 ORA-01438: value larger than specified precision allowed for this column ORA-06512: at line 8 in /xxxxxx/some.php on line 220

这不取决于要查询哪个表.似乎oci_connect()在某些sys表中插入了trackingstaff，或者触发器与登录有关.但是我无权在sys中解决此问题.

It's not depending on which table is being queried. It seems like oci_connect() is inserting some trackingstaff in some sys table, or maybe a trigger is related with the logon. But i don't have the permission to figure out this problem in sys.

任何想法都可能是导致此错误的原因吗?

Any Idea what could be the cause for this error?

更新

oracle是否在未进行特定配置的情况下自动进行一些日志记录?我可以以某种方式让oracle或PHP向我显示受影响的表或列吗?

Does oracle do some logging somewhere automatically out of box without configured to specifically? Can i somehow let oracle or PHP show me which table or column is affected?

更新 我发现，当我直接在Bash中调用PHP脚本时，它确实可以正常工作.但是来自网络的呼叫将导致标题问题.有想法吗?

Update I found out that, when i call the PHP Script in Bash directly, it does work fine. But a call from web will cause titled problem. Any Idea?

推荐答案

消息error occurred at recursive SQL level 1向我提示错误是在触发器内引起的.我的猜测是有一个AFTER LOGON ON SCHEMA或DATABASE触发器，由于某种原因，当您的Web服务器进程尝试连接时，它会导致错误.

The message error occurred at recursive SQL level 1 suggests to me that the error is arising within a trigger. My guess is that there is an AFTER LOGON ON SCHEMA or DATABASE trigger, and for some reason it causes an error when your web server process attempts to connect.

这是如何产生错误的示例.我有一个名为TINY的表，其中只有一列，最多只能接受99个值:

Here's an example of how to generate the error you're getting. I have a table called TINY, with a single column that can only take values up to 99:

SQL> desc tiny;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                                  NUMBER(2)

现在，我们创建一个用户帐户并验证他们是否可以连接:

Now let's create a user account and verify that they can connect:

SQL> create user fred identified by fred account unlock;

User created.

SQL> grant connect to fred;

Grant succeeded.

SQL> connect fred/fred
Connected.

好-让我们以我的身份重新登录并创建一个触发器，如果​​FRED尝试连接，该触发器将导致错误:

Good - let's log back in as me and create a trigger that will cause an error if FRED attempts to connect:

SQL> connect luke/password
Connected.
SQL> create or replace trigger after_logon_error_if_fred
  2    after logon on database
  3  begin
  4    if user = 'FRED' then
  5      insert into tiny (n) values (100);
  6    end if;
  7  end;
  8  /

Trigger created.

回想一下，我们的TINY表最多只能存储99个值.那么FRED尝试连接时会发生什么?

Recall that our TINY table can only store values up to 99. So, what happens when FRED attempts to connect?

SQL> connect fred/fred
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 3

除了行号和PHP位之外，这就是您收到的消息.

Other than the line number, and the bit PHP added, that's exactly the message you got.

如果要查看数据库中是否有任何AFTER LOGON触发器，请尝试运行查询

If you want to see whether there are any AFTER LOGON triggers in your database, try running the query

SELECT trigger_name, owner FROM all_triggers
 WHERE TRIM(triggering_event) = 'LOGON';

在我的数据库(Oracle 11g XE beta)上，得到以下输出:

On my database (Oracle 11g XE beta), I get the following output:

TRIGGER_NAME                   OWNER
------------------------------ ------------------------------
AFTER_LOGON_ERROR_IF_FRED      LUKE

我不认为Oracle可以立即进行任何登录，如果PHP的oci_connect可以执行任何操作，我会感到惊讶.

I don't believe Oracle does any logging out-of-the-box, and I'd be surprised if PHP's oci_connect does either.

我只能推测为什么错误仅发生在您的Web服务器上，而不是当您从bash脚本运行PHP时才出现.也许触发条件是查询V$SESSION并试图找出哪个用户帐户正在尝试连接到数据库?

I can only speculate as to why the error arises only for your web server and not when you run PHP from a bash script. Perhaps the trigger is querying V$SESSION and trying to figure out what user account is trying to connect to the database?

这篇关于OCI_CONNECT是否可以导致ORA-01438:值大于此列允许的指定精度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！