oracle ORA-01722: 无效号码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2336048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01722: invalid number
提问by Lluis Martinez
I'm getting the infamous invalid number Oracle error. Hibernate is issuing an INSERT with a lot of columns, I want to know just the name of the column giving the problem. Is it possible?
我收到了臭名昭著的无效数字 Oracle 错误。Hibernate 发出一个包含很多列的 INSERT，我只想知道给出问题的列的名称。是否可以？
FYI the insert is this:
仅供参考，插入是这样的：
insert into GEM_INVOICE_HEADER
(ENDORSEE_ACCOUNT_ID, INVOICE_CODE, APPROVAL_ORGAN, APROVAL_DATE, APROVAL_REFERENCE, BALANCE_BASE_AMOUNT, BALANCE_DEDUCT_AMOUNT, BALANCE_TOTAL_AMOUNT, BALANCE_VAT_AMOUNT, BALANCE_VAT_DED_AMOUNT, BALANCE_VAT_NOT_DED_AMOUNT, DESCRIPTION, SUPPLIER_INVOICE_NUMBER, INVOICE_DATE, RECEIPT_DATE, MEMO, VAT_INTRACOM, INVOICE_BASE_AMOUNT, INVOICE_VAT_AMOUNT, INVOICE_VAT_DED_AMOUNT, INVOICE_VAT_NOT_DED_AMOUNT, INVOICE_DEDUCT_AMOUNT, INVOICE_TOTAL_AMOUNT, VAT_EXEMPT, RECTIFICATION_SIGN, REASON, LOT, FILE_ID, RETAINED, INSTITUTION_ID, PERIOD_CODE, IS_RECTIFIED, DEFAULT_OFFBUDGET_ACCOUNT, OFFBUDGET_DOC_ID, PHASE_OF_ACCOUNTING, ACCOUNTED_OFF_BUDGET, CANCEL_DOC_ID, BUDGET_TYPE, INVOICE_TYPE, SOURCE_ID, STATE_ID, MANAGER_UNIT_ID, DOCUMENT_TYPE_CODE, ACCOUNTED_DOC_ID, ACCOUNTING_LIST, ENDORSEE_ID, PAYMASTER_ID, SUPPLIER_ID, SUPPLIER_ACCOUNT_ID, PAY_JUSTIFY_ID, PETTY_CASH_ID, DBOID)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
回答by Gary Myers
Try DBMS_SQL.LAST_ERROR_POSITION
试试 DBMS_SQL.LAST_ERROR_POSITION
It will tell you the character position in the SQL string of the error. Don't know if it will work from Hibernate, but it does from PL/SQL.
它会告诉您错误在 SQL 字符串中的字符位置。不知道它是否适用于 Hibernate，但它适用于 PL/SQL。
DECLARE
v_ret NUMBER;
v_text varchar2(10) := 'a';
BEGIN
insert into a_test (val1, val2) values (1,v_text);
exception
when others then
v_ret := DBMS_SQL.LAST_ERROR_POSITION;
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line('Error at offset position '||v_ret);
END;
.
/
Note the '43' is the offset from the 'insert' ignoring any preceding whitespace.
请注意，“43”是“插入”的偏移量，忽略任何前面的空格。
回答by JavaRocky
You need Oracle DML Error Logging. Your friend is err$_dest.
您需要 Oracle DML 错误日志记录。你的朋友是 err$_dest。
This can tell you which column failed.
这可以告诉您哪个列失败。
http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php#insert
http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php#insert
回答by a'r
Unfortunately, you can't get Oracle to tell you which column is causing the problem. Can you dump out your insert data as insert statements that you can run manually through sqlplus?
不幸的是，您无法让 Oracle 告诉您哪个列导致了问题。您可以将插入数据转储为可以通过 sqlplus 手动运行的插入语句吗？
