Solving ORA-01722 – Invalid Number while navigating from Oracle BI 11g to Oracle eBS R12


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.

An easy;

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.

Problem Solved.

– Daan

6 comments

  1. HI Daan,

    Below is the query:

    EXECUTE PHYSICAL CONNECTION POOL “Oracle EBS OLTP InitBlocks Connection Pool”
    SELECT fnd_run_function.get_run_function_url
    (CAST(fnd_function.get_function_id(‘AR_ARXTWMAI_SUMMARY’) AS NUMBER),
    CAST(‘0’ AS NUMBER),
    CAST(‘0’ AS NUMBER),
    CAST(‘0’ AS NUMBER),”,NULL) AS ACTION_LINK FROM DUAL

    Please let me know if I missed anything.

    Thanks,
    Rajesh S.

    Like

    • Hi Rajesh,

      You might want to check the quotes before the NULL. What if you replace the quotes with NULL also? You can just enter the sql statement in SQL+ or SQL Developer.

      Did you also set the ‘Alter session’-statement?

      Cheers.

      Daan

      Like

  2. Hi Daan,

    We are getting the below is the query:
    EXECUTE PHYSICAL CONNECTION POOL “Oracle EBS OLTP InitBlocks Connection Pool”
    SELECT fnd_run_function.get_run_function_url
    (CAST(fnd_function.get_function_id(‘AR_ARXTWMAI_SUMMARY’) AS NUMBER),
    CAST(‘0’ AS NUMBER),
    CAST(‘0’ AS NUMBER),
    CAST(‘0’ AS NUMBER),”,NULL) AS ACTION_LINK FROM DUAL

    Please let me know if I missed anything here.

    Regards,
    Rajesh S.

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s