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

Author: Daan Bakboord

I am an Oracle Business Analytics Consultant with great interest in anything closely related to the Oracle Business Analytics (OBIEE, BICS, Big Data, Data Integration, Data Visualization) .

6 thoughts on “Solving ORA-01722 – Invalid Number while navigating from Oracle BI 11g to Oracle eBS R12”

  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

    1. 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