(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),45,10)) Returns Spaces after Executing FND_GLOBAL.APPS_INITIALIZE (Doc ID 1981582.1)

Last updated on JULY 10, 2017

Applies to:

Oracle Assets - Version 12.2.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.3 version, MRC/EFC

You run the following SELECT:

select *
from FA_BOOKS_MRC_ V;

It does not retrieve data. You then set the environment but the SELECT script now fails:


BEGIN
FND_GLOBAL.APPS_INITIALIZE(<user_id>,<resp_id>,140);
END;

Error starting at line 422 in command:
SELECT *
FROM FA_BOOKS_MRC_V
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:


The issue is with the filter the view uses:

CREATE OR REPLACE FORCE VIEW "APPS"."FA_BOOKS_MRC_V" ("SET_OF_BOOKS_ID",

AS
 SELECT FMCB.SET_OF_BOOKS_ID,

 FROM FA_MC_BOOKS FMCB
 WHERE FMCB.SET_OF_BOOKS_ID = NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),45,10)),-1);


The view errors out because part of its condition fails:

SELECT
NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),45,10)),-1)
FROM DUAL
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:

The problem is that SUBSTRB(USERENV('CLIENT_INFO'),45,10) retrieves 10 spaces and TO_NUMBER does not accept them.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms