ORA-01722: invalid number

Question

I get a notorious incorrect Oracle error. Hibernate releases INSERT with a large number of columns, I want to know only the name of the column that sets the problem. Is it possible?

FYI insert is as follows:

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

Try DBMS_SQL.LAST_ERROR_POSITION

It will tell you the character position in the SQL error string. I don’t know if it will work from Hibernate, but it works from 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 that “43” is an offset from the “insert” ignoring any previous spaces.

Answer 2

You need Oracle DML error logging. Your friend is err $ _dest.

This may tell you which column failed.

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

Answer 3

Unfortunately, you cannot get Oracle to tell you which column is causing the problem. Can you upload the insert data as insert statements that you can manually start through sqlplus?