OCI_CONNECT 是否会导致 ORA-01438: 值大于此列允许的指定精度？

发布于 2024-12-04 11:56:31 字数 547 浏览 0 评论 0原文

我想知道 oci_connect() 是否会导致 1438 错误，因为我一直收到此错误：

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

这并不取决于正在查询哪个表。看起来 oci_connect() 正在某些 sys 表中插入一些跟踪人员，或者触发器可能与登录相关。但我没有权限在 sys.path 中解决这个问题。

知道导致此错误的原因是什么吗？

更新

oracle 是否会自动在某处进行一些日志记录，无需专门配置？我可以以某种方式让 oracle 或 PHP 显示哪个表或列受到影响吗？

更新我发现，当我直接在 Bash 中调用 PHP 脚本时，它确实工作得很好。但是从网络调用会导致标题问题。有什么想法吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苏别ゝ 2024-12-11 11:56:31

消息错误发生在递归 SQL 级别 1 向我表明错误是在触发器内出现的。我的猜测是，存在一个 AFTER LOGON ON SCHEMA 或 DATABASE 触发器，并且由于某种原因，当您的 Web 服务器进程尝试连接时，它会导致错误。

以下是如何生成您遇到的错误的示例。我有一个名为 TINY 的表，其中有一列最多只能接受 99 的值：

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

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

SQL> create user fred identified by fred account unlock;

User created.

SQL> grant connect to fred;

Grant succeeded.

SQL> connect fred/fred
Connected.

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

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< 时会发生什么/code> 尝试连接？

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 添加的位之外，这正是您收到的消息。

如果您想查看数据库中是否有任何 AFTER LOGON 触发器，请尝试在我的数据库（Oracle 11g XE beta）上运行查询

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

，我得到以下输出：

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

I don't believe Oracle does任何开箱即用的日志记录，如果 PHP 的 oci_connect 这样做，我会感到惊讶。

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

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.

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.

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.

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

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

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

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

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

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

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?

回复收藏 0 原文