SQL Error "Ora-01722: Invalid Number"

ORA-01722: invalid number 01722. 00000 - invalid number *Cause: The specified number was invalid. *Action: Specify a valid number

Use cast with default null on conversion error to avoid exception and investigate the cause of the failed conversion.

Example

with dt as 
(select '001' remark from dual union all
 select '  2' from dual union all
 select 'OMG' from dual)
select substr(remark,1,3) txt,
cast (substr(remark,1,3) as INT default null on conversion error) num
from dt;

TXT        NUM
--- ----------
001          1
  2          2
OMG           

ORA-01722- Invalid Number

Obviously v_acct_num column contains non-numeric values. to_number() function has restriction that the value in the argument shouldn't contain any non-numeric character, otherwise the system hurls with ORA-01722 : invalid number. If you're sure about the data matches when non-numeric characters are extracted then use regexp_replace(t2.v_acct_num,'[^[:digit:]]') within the collation of the join's on clause :

select t2.column  
  from table1 t1
  join table2 t2 on nvl(t1.acct_nmbr,0) = nvl(regexp_replace(t2.v_acct_num,'[^[:digit:]]'),0)
 where t2.created_date = date'2017-05-17'

Use ANSI-92 standard for Join, and ISO-8601 standard for date syntaxes.

how to fix oracle ORA-01722 invalid number error

If you have a numeric column, all you have to do is use a to_char with the right parameters; this should do the work:

select to_char(column1, '00000000000000000D000000', 'NLS_NUMERIC_CHARACTERS = ''.,''') from ...

SQL Select Error: ORA-01722: invalid number only when number is queried

There is nothing confusing about the version where you provide a number value in the WHERE clause. Because your value is a number Oracle attempts to convert the varchar2 column values into a number data type for comparison. Some of the columns do not contain valid numeric data so an error occurs. If you enclose the value you want to search for in single quotes now you are doing a character to charter compare. Problem if the table data column stores any non-digits in the value which then match your value not match. So you may have to filter for leading and trailing spaces, remove dollar signs or commas etc... This is an example of 1) why the appropriate database data type should always be used to store data and 2) why data should always be normalized (edited): case, leading and/or trailing spaces, zero filled, etc....

ora-01722 invalid number using count(1) on minus operator of two views

Somewhere in your query there is an implicit type conversion. Just use explain plan of your query and in the predicates section or in the projection you will see something like to_number(..) or internal_function() .

SSIS - ORA-01722 - Invalid Number

First problem is to convert timestamp to date >> I think this will not be possible...

Oracle:

For your column with date datatype:

create table t1(a date)

insert into t1 values(Jun 30 2005) 
--this will not work
insert into t1 values(to_date('Jun 30 2005', 'Mon DD YYYY'))
--this will work

For your column with varchar datatype:

create table t2(b varchar(100))
insert into t2 values(Test)
 --this will not work
insert into t2 values('Test')
--this will work

When I correct this two things insert is ok...
Here is a demo

INSERT ALL INTO throws ORA-01722: invalid number

You do not specify the order of the columns in the INSERT ALL statement and if the columns of the tables are in different orders then you will find that using SELECT * and not specifying the columns will attempt to put the column from one table into the wrong column in the other table.

For example:

CREATE TABLE DB_LOCVIEW (DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE) AS
SELECT SYSDATE, 'ABC', 'DEF', 1, 23, 'GHI' FROM DUAL;

-- Create the table with the columns in a different order
CREATE TABLE DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE) AS
SELECT DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE FROM DB_LOCVIEW WHERE 1 = 0;

Then running your INSERT ALL query fails with the error:

SQLCODE: -1722  -SQLERRM: ORA-01722: invalid number

This is because the statement is effectively expanded to:

BEGIN
  INSERT ALL
    INTO DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE)
    SELECT DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE FROM DB_LOCVIEW; 
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line  ('SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
END;
/

And you can see that the columns do not match. So, even though the columns have identical types, the order of the columns matters.

---

Instead, you can specify the columns and their order:

BEGIN
  INSERT ALL
    INTO DB_LOCATIONS (DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE)
    SELECT DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE FROM DB_LOCVIEW; 
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line  ('SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
END;
/

Or use INSERT INTO ... SELECT and specify the columns:

BEGIN
   INSERT INTO DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE)
   SELECT DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE FROM DB_LOCVIEW;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line  ('SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
END;
/

Note: You do not need to catch the exception; if there is an error then it will stop the PL/SQL block, implicitly rollback the transaction and print the error trace. Additionally, catching OTHERS is considered bad practice. If you do want to catch the exception then you should catch specific expected exceptions then you know when there is an uncaught exception that something unexpected has occurred and you can debug it; if you catch all exceptions then you lose the ability to identify unexpected behaviours.

Or, without PL/SQL:

INSERT INTO DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE)
SELECT DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE FROM DB_LOCVIEW;

COMMIT;

db<>fiddle here

---

Related Topics