ORA-01722: invalid number ERROR
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am
ORA-01722: invalid number ERROR
Hi,
I recieve follwoing warning when i execute a DS job
ORA-01722: invalid number
There is a select query on an oracle view (oracle stage) to populate into a sequential file.
An error is prompted when i execute the same select query through TOAD.
I want the DS job to abort with the error "ORA-01722: invalid number " and not warning.
Please do let me know if i need to make any admin level setting (parameter property to be modified) to abort the job for such kind of errors.
Thanks!
I recieve follwoing warning when i execute a DS job
ORA-01722: invalid number
There is a select query on an oracle view (oracle stage) to populate into a sequential file.
An error is prompted when i execute the same select query through TOAD.
I want the DS job to abort with the error "ORA-01722: invalid number " and not warning.
Please do let me know if i need to make any admin level setting (parameter property to be modified) to abort the job for such kind of errors.
Thanks!
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am
The query is very complex thus stating it in simplified format as below:
1.There is table named A1 which has columns of datatype number and varchar. From this table data is populated into a view vB1.
2. Similar way data from different tables (A1,A2....) is populated into different views(vB1,vB2...)
3. Union of all views (vB1,vB2...) is taken and populated into another view 'VMain'.
4. Select query to this main view 'VMain' is done in Oracle Stage which has datatype number and varchar.
Problem identified for the error is that there is Sum function used for col1 of table A1 to populate view vB1.
And col1 has space ' ' in the record. Due to which the warning named 'ORA-01722: invalid number' is prompted
In DS job i want to know for which record the warning has occured and also want to abort the job for such warnings
Thanks!
1.There is table named A1 which has columns of datatype number and varchar. From this table data is populated into a view vB1.
2. Similar way data from different tables (A1,A2....) is populated into different views(vB1,vB2...)
3. Union of all views (vB1,vB2...) is taken and populated into another view 'VMain'.
4. Select query to this main view 'VMain' is done in Oracle Stage which has datatype number and varchar.
Problem identified for the error is that there is Sum function used for col1 of table A1 to populate view vB1.
And col1 has space ' ' in the record. Due to which the warning named 'ORA-01722: invalid number' is prompted
In DS job i want to know for which record the warning has occured and also want to abort the job for such warnings
Thanks!
Arpana
-
- Participant
- Posts: 14
- Joined: Sun Jun 07, 2009 12:26 pm
- Location: Mumbai
ORA-01722 Invalid number error is cause the attempted conversion of a character string to a number failed because the character string was not a valid numeric conversion. Which stage you are using for this query? We have a option for tpump/multiload to check checkbox for throwing error.
regards
Deepak Kumar
Datastage Developer
Deepak Kumar
Datastage Developer
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am
You would need to run the job with a low warning threshold so the job aborts because of the warnings but I don't believe there is a way you could abort the job because of this specific warning.
You've got other options in a Server job, the OCI stage has an option to 'treat warnings as fatal' (so you could single things like this out) but I don't believe the OE stage has anything similar and can't check at the moment.
![Confused :?](./images/smilies/icon_confused.gif)
You've got other options in a Server job, the OCI stage has an option to 'treat warnings as fatal' (so you could single things like this out) but I don't believe the OE stage has anything similar and can't check at the moment.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
as a workaround you can create a after job sub-routine to check the status of that job and if the status is finished with warning return Errorcode <>0 or you can even scan the logs for specific warning before raising an error.
I would suggest you to go to official support provider.
I would suggest you to go to official support provider.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)
-
- Participant
- Posts: 30
- Joined: Tue Sep 08, 2009 4:24 am