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

Daan Bakboord Oracle BI 11g 29/Oct/201229/Oct/2012 1 Minute

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

Published by Daan Bakboord

I am a Self Employed Oracle Data &; Analytics Consultant with a great interest in anything closely related to Oracle Big Data Analytics (OBIEE, BICS, OAC, Big Data, Data Integration, Data Visualization, Data Management, Data Architecture). View all posts by Daan Bakboord

Published 29/Oct/201229/Oct/2012

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

Rajesh Chowdary says:

31/May/2016 at 15:10

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.

LikeLike

Reply
1. Daan Bakboord says:
  
  31/May/2016 at 20:06
  
  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
  
  LikeLike
  
  Reply
Rajesh Chowdary says:

31/May/2016 at 15:08

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.

LikeLike

Reply
Rajesh Chowdary says:

30/May/2016 at 13:28

Hello Daan,

I am facing the same above error. I followed your blog to fix the issue, still getting the same error.

We are using OBIEE 12c.

Thanks in Adv.
Rajesh S.

LikeLike

Reply
1. Daan Bakboord says:
  
  30/May/2016 at 16:04
  
  Hi Rajesh,
  
  Are you able to perform the sql-trace? If so, what is the result of this sql-trace?
  
  Thanks.
  
  Daan
  
  LikeLike
  
  Reply
  1. Rajesh Chowdary says:
    
    06/Jun/2016 at 08:37
    
    Hi Daan,
    
    Results of the Traces SQL is :
    http://work01.kpipartners.com:8000/OA_HTML/RF.jsp?function_id=1690&resp_id=0&resp_appl_id=0&security_group_id=0&lang_code=US
    
    Please guide us on this issue.
    
    Thanks & Regards,
    Samad
    
    LikeLike