Solving ORA-01722 – Invalid Number while navigating from Oracle BI 11g to Oracle eBS R12
29/10/2012 Leave a comment
In a previous blog post, I covered how to navigate from Oracle BI 11g to Oracle eBS R12. While setting this up, I had some challenges to overcome. One of them was the following. When I clicked the column with the Action Link, the following error showed up;
The Oracle eBS Server was available, so that couldn’t be the problem. Checking the logfile(s) showed that there was a ORA-01722 error involved.
The question at this point is; Which query is throwing a ‘Invalid Number’-error. Thanks to Robin I got the idea to Sql Trace the Connection Pool which is being used for the Action Link.
Via the generated Trace Files I was able to identify the problem – query. It turned out to be the query which constructs the Url to navigate to Oracle eBS.
select fnd_run_function.get_run_function_url ( cast ( fnd_function.get_function_id ( 'AP_APXINWKB_SUMMARY_VIEW' ) as number ) , cast ( '200.00' as number ) , cast ( '*****' as number ) , cast ( '0.00' as number ) , 'INVOICE_ID=*****' , null ) as action_link from DUAL
A quick check in SQL*Plus gave the same error. The problem is in the Cast functions and the NLS_NUMERIC_CHARACTERS-settings.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;
….. solved the problem in SQL*Plus.
In Oracle BI, I added the ‘Alter Session’-Statement in the Connection Pool which is being used for the Action Link.