ORA-01722: invalid number ERROR

Arpanakhade2009 · Post by **Arpanakhade2009** » Fri Feb 05, 2010 5:01 am

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!

priyadarshikunal · Post by **priyadarshikunal** » Fri Feb 05, 2010 5:29 am

details please, we need to know which stage you are using and the exact version and release of datastage.

Are you using ODBC to fetch data?

Arpanakhade2009 · Post by **Arpanakhade2009** » Fri Feb 05, 2010 5:31 am

No oracle enterprise stage

Arpanakhade2009 · Post by **Arpanakhade2009** » Fri Feb 05, 2010 5:44 am

Oracle enterprise stage is used for extract.
Any other dtls reqd.

I know we can promote an information to warning but can we promote a warning to error. How?

ray.wurlod · Post by **ray.wurlod** » Fri Feb 05, 2010 5:49 am

Can you please post the query and the data types of the columns mentioned in the query?

Arpanakhade2009 · Post by **Arpanakhade2009** » Fri Feb 05, 2010 6:05 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!

invincibleDeepak · Post by **invincibleDeepak** » Fri Feb 05, 2010 6:11 am

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.

Arpanakhade2009 · Post by **Arpanakhade2009** » Fri Feb 05, 2010 6:16 am

The view 'vMain' is created not by any DS job but by an SQL query in Toad.

Are these option available in Datastage as in Toad i am getting the error while i execute the sql query

chulett · Post by **chulett** » Fri Feb 05, 2010 8:48 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.

priyadarshikunal · Post by **priyadarshikunal** » Fri Feb 05, 2010 9:22 am

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.

chulett · Post by **chulett** » Fri Feb 05, 2010 9:42 am

Sure, that's another option. If you go the 'after job' route and scan the log, make sure you check the INTERIM status to know if there has been a problem or not.

Arpanakhade2009 · Post by **Arpanakhade2009** » Mon Feb 08, 2010 3:44 am

Thank you all for your prompt replies, will informyou once i am able to resolve the above mentioned problm