ORA-01722:无效数字

Question

我得到了臭名昭著的无效编号Oracle错误。Hibernate发出了一个包含很多列的插入，我想知道出现问题的列的名称。有可能吗？

仅供参考，插入内容如下：

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 
   (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Answer 1

试试DBMS_SQL.LAST_ERROR_POSITION

它将告诉您错误在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;
.
/

注意，'43‘是'insert’的偏移量，忽略前面的空格。

Answer 2

您需要Oracle DML错误日志记录。你的朋友是err$_dest。

这可以告诉您哪一列失败了。

http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php#insert

Answer 3

不幸的是，您无法让Oracle告诉您是哪一列导致了问题。您是否可以将insert数据转储为insert语句，以便通过sqlplus手动运行？