 Identify invalid number in exception handler [message #606934] |
Thu, 30 January 2014 17:28  |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
I am getting the following error message when I run an insert select.
INSERT
INTO TABLE1
SELECT COL1,COL3,COL3,COL4,COL5,....,COL25
FROM TABLE2;
ORA-20202: (-1722) ORA-01722: invalid number
When I run the select statement I return 1.4+ million records.
This tells me that there is a mismatch between a record.column with a column from TABLE2 .
I want to create an exception that will display the column and the value.
i.e
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE(COLx,value); --??? t
Can someone show me how to do this better , if doable at all ?
Thanks in advance to responders.
|
|
|
|
|
|
| Re: Identify invalid number in exception handler [message #606936 is a reply to message #606935] |
Thu, 30 January 2014 19:20  |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
BlackSwan,
I appreciate your insistence on details. Don't let it obscure the conceptual question.
I am very well aware of the difference between SQL and PL/SQL.
I am merely interested in the syntax that will display the information I need to identify
which of the 1.4+ million records is causing the error.
I also have a suspicion that the column in question will be of type NUMBER.
The thing is that someone must have entered an entity wrongly from the source data
and it is my intention to identify the data, hopefully in the DBMS_OUTPUT.PUT_LINE output.
BEGIN
EXECUTE IMMEDIATE ('truncate table TABLE1');
INSERT INTO TABLE1(COL1,COL2,COL3,COL4,COL5,....,COL25)
SELECT COL1,COL3,COL3,COL4,COL5,....,COL25
FROM TABLE2;
COMMIT;
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE(COLx,value); --??? this is where I need help BlackSwan.
END;
Output: ORA-20202: (-1722) ORA-01722: invalid number
Is there a better way to do it ? Can it be done ?
Please advise.
|
|
|
|
| Re: Identify invalid number in exception handler [message #606937 is a reply to message #606936] |
Thu, 30 January 2014 19:42 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>Is there a better way to do it ?
do not SELECT non-numeric value prior to INSERT a NUMERIC column
>I also have a suspicion that the column in question will be of type NUMBER.
why suspect?
why not do actual datatype comparison as below
SELECT COLUMN_ID, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE1'
MINUS
SELECT COLUMN_ID, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE2'
UNION
SELECT COLUMN_ID, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE2'
MINUS
SELECT COLUMN_ID, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE1';
[Updated on: Thu, 30 January 2014 20:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
