查询Oracle的ORA代码错误详细信息 [英] Query Oracle for ORA-code error details

问题描述

我的问题：如何查询11g Oracle数据库以获取错误代码的描述？

My Question: How do you query an 11g Oracle database to get a description of an error code?

背景： 有人告诉我，他们已经看到代码一次会查询oracle的具体的 ORA代码错误的详细信息。我一直在谷歌搜索一段时间，似乎找不到这样的东西。有人知道这是否可行？是否有av $视图或其他东西？

Background: Someone told me they had seen code once which would query oracle for details on a specific ORA-code error. I've been searching on Google for a little while now and can't seem to find anything like that. Does anyone know if this is possible? Is there a v$view for it or something?

为什么：我想编写一个过程，将返回错误代码I的描述给它所以写的时候我可以这样称呼：

Why: I want to write a procedure which will return the description of an error code I give to it. So when it's written I could call it like this:

select ora_code_desc('ORA-00000')
from dual;

它会输出：

Normal, successful completion.
Cause: An operation has completed normally, having met no exceptions.
Action: No action required.

或类似的东西:)感谢您的帮助！

Or something like that :) Thanks for the help!

推荐答案

无法从SQL访问，但在PL / SQL中，可以使用 SQLERRM 函数。

It's not accessible from SQL but within PL/SQL, you can use the SQLERRM function.

例如

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    dbms_output.put_line( sqlerrm(0) );
  3    dbms_output.put_line( sqlerrm(-1041) );
  4* end;
SQL> /
ORA-0000: normal, successful completion
ORA-01041: internal error. hostdef extension doesn't exist

PL/SQL procedure successfully completed.

当然可以构建一个 ora_code_desc 函数带入一个字符串，删除前三个字符，将结果数传递给 SQLERRM ，并返回结果

You could, of course, build an ora_code_desc function that took in a string, removed the first three characters, passed the resulting number to SQLERRM, and returned the result

SQL> ed
Wrote file afiedt.buf

  1  create or replace function ora_code_desc( p_code in varchar2 )
  2    return varchar2
  3  is
  4    l_str varchar2(1000);
  5  begin
  6    l_str := sqlerrm( substr(p_code, 4 ) );
  7    return l_str;
  8* end;
SQL> /

Function created.

SQL> select ora_code_desc( 'ORA-00000' ) from dual;

ORA_CODE_DESC('ORA-00000')
--------------------------------------------------------------------------------
ORA-0000: normal, successful completion

Oracle还在Unix平台上运行一个实用程序<提供更多细节的href =http://perumal.org/oerr-the-command-line-oracle-error-code-lookup-utility/ =noreferrer> oerr ，特别是你正在寻找的原因和行动。如果你真的也想要这个数据，你可以编写一个Java存储过程，它调用一个操作系统shell，执行一个 oerr 命令，并返回结果。这将给你更多的数据，但显然会复杂得多。

Oracle also ships a utility on Unix platforms oerr that provides more detail-- particularly the cause and action you're looking for. If you really want that data too, you could write a Java stored procedure that called out to an operating system shell, executed an oerr command, and returned the result. That would give you more data but would, obviously, be much more complex.

这篇关于查询Oracle的ORA代码错误详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！