[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORA-01722: invalid number: Identify data causing invalid number error.

Posted on 2011-10-21
10
Medium Priority
?
2,014 Views
Last Modified: 2013-12-07
The error is comming from the following statement.


        insert into table2 (abc,
                                            def,
                                            ghi
            )
            select abc,
               def,
               ghi
            from   view1 a
                   inner table2 x
                      on x.col1 = a.col1
            and          x.col2 = a.col2
                   inner join table3 mil
                      on mil.col1 = a.col1
            and          mil.col2 = a.col2
            and          mil.col3 = a.col3
            where  x.col1 = 'ABC'
            and    mil.col1 = 180;

Table from a view is being inserted into a table.
The select statement retrieves 4,500 rows.

However, i do not have the privileges to run this statement to pinpoint which row from the view is causing the error.
This is because I am checking into UAT on which i have only read access.
In such a case how do i identify the data causing the error?
0
Comment
Question by:gram77
  • 5
  • 4
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37006132
where  x.col1 = 'ABC'
            and    mil.col1 = 180;


there's your problem because...

x.col1 = a.col1 = mil.col1


it can't be both a string and a number
0
 
LVL 48

Expert Comment

by:schwertner
ID: 37006141
Run a SQLPlus procedure and catch the rowid of the eroneous row


....
FOR i IN (select abc,  ...  ) LOOP
-- put here number specific operations to detect the wrong number....
v_n1 := TO_NUMBER(i.abc);
......

END LOOP;


EXCEPTION
WHEN OTHERS
   --- select rowid or primary key here

END;
0
 

Author Comment

by:gram77
ID: 37006158
Assuming that the select statement is ok.

insert into table1 (abc,
                                            def,
                                            ghi
            )
            select abc,
               def,
               ghi
            from   view1 a
                   inner table2 x
                      on x.col1 = a.col1
            and          x.col2 = a.col2
                   inner join table3 mil
                      on mil.col1 = a.col1
            and          mil.col2 = a.col2
            and          mil.col3 = a.col3
            where  x.col1 = 'ABC'
            and    mil.col5 = 180;



where  x.col1 = 'ABC'
            and    mil.col1 = 180;
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:gram77
ID: 37006163
Assuming that the select statement is ok.

insert into table1 (abc,
                                            def,
                                            ghi
            )
            select abc,
               def,
               ghi
            from   view1 a
                   inner table2 x
                      on x.col1 = a.col1
            and          x.col2 = a.col2
                   inner join table3 mil
                      on mil.col1 = a.col1
            and          mil.col2 = a.col2
            and          mil.col3 = a.col3
            where  x.col1 = 'ABC'
            and    mil.col5 = 180;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37006203
well, the same problem is likely.

x.col1 = 'ABC'   and is being joined to a.col1 which is joined to mil.col1

is x.col1  a number? if so, this will fail
is a.col1  a number?  if so, this will fail
is mil.col1 a number? if so, this will fail


if you want to find non-numeric values in each table...

select * from view1 where regexp_like(col1,'[^0-9]')

select * from table2 where regexp_like(col1,'[^0-9]')

select * from table3 where regexp_like(col1,'[^0-9]')



0
 

Author Comment

by:gram77
ID: 37007257
as i said, assuming select statement is ok, and produces right data.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37007289
>>> as i said, assuming select statement is ok, and produces right data.

that doesn't make sense.

how can we make that assumption?

if you're getting an error because of bad data conversions,  how can we assume the query works?
if the query works, then you're not going to get an error.

And,  the very construction of your original query virtually guarantees there will be a problem

your revised version doesn't have the same guarantee, so that means you'll have to pick each piece apart as shown above

search each table.  For those that you find with non-numeric data,  compare those rows to the rows you'll be joining
to in the other table.


0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37007317
ok,  I thought of something.  let's assume the query "does" work.

If I assume all of your query data is strings, then the query won't get any conversion errors.

If you then attempt to "use" those strings in non-string ways (e.g. numeric) then you could get a conversion error


select * from (   <<<< your query >>>> )
where regexp_like(some_numeric_column_in_your_query ,'[^0-9]')

0
 

Author Comment

by:gram77
ID: 37007513
how do i match data like 44540932

where regexp_like('mycol' ,'44[^0-9][^0-9][^0-9][^0-9][^0-9][^0-9]');          

does not work

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37007534
right,  that searches for..

44(any 6 characters as long as they are NOT numeric)  within the literal string  "mycol"

if you want to search a column called mycol,  then remove the quotes around it

if you want to search mycol for the value 44540932
don't complicate it with the regular expression

where mycol=44540932  

if you're only interested in the first 2 characters


where mycol like '44%'

again, don't use regular expression unless you must




0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month18 days, 1 hour left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question