Search code examples
sqloracle-databaseora-01722

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

Solution

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