CODEWITHSUNDEEP
sqloracle-databaseora-01722
Lluis Martinez
Lluis Martinez

Reputation: 1973

ORA-01722: invalid number

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?

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

Upvotes: 4

Views: 7126

Answers (3)

JavaRocky
JavaRocky

Reputation: 19893

You need Oracle DML Error Logging. Your friend is err$_dest.

This can tell you which column failed.

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

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

Try 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.

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.

Upvotes: 6

a'r
a'r

Reputation: 36999

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?

Upvotes: 0

Related Questions