Tuesday, April 06, 2010

Environment Variables from database table - Oracle E-Business Suite

Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?

Try out out this query that shows you $FND_TOP:

select value
from   fnd_env_context
where  variable_name = 'FND_TOP'
and    concurrent_process_id = 
      ( select max(concurrent_process_id) from fnd_env_context );

VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0

Or did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?

col variable_name format a15
col value format a64
select variable_name, value
from   fnd_env_context
where  variable_name like '%\_TOP' escape '\'
and    concurrent_process_id = 
     ( select max(concurrent_process_id) from fnd_env_context )
order by 1;

VARIABLE_NAME   VALUE
--------------- ----------------------------------------------------------------
AD_TOP          /d01/oracle/VIS/apps/apps_st/appl/ad/12.0.0
AF_JRE_TOP      /d01/oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre
AHL_TOP         /d01/oracle/VIS/apps/apps_st/appl/ahl/12.0.0
AK_TOP          /d01/oracle/VIS/apps/apps_st/appl/ak/12.0.0
ALR_TOP         /d01/oracle/VIS/apps/apps_st/appl/alr/12.0.0
AME_TOP         /d01/oracle/VIS/apps/apps_st/appl/ame/12.0.0
AMS_TOP         /d01/oracle/VIS/apps/apps_st/appl/ams/12.0.0
AMV_TOP         /d01/oracle/VIS/apps/apps_st/appl/amv/12.0.0
AMW_TOP         /d01/oracle/VIS/apps/apps_st/appl/amw/12.0.0
APPL_TOP        /d01/oracle/VIS/apps/apps_st/appl
AP_TOP          /d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0
AR_TOP          /d01/oracle/VIS/apps/apps_st/appl/ar/12.0.0
...

Or perhaps the full directory path to $APPLTMP?

select value
from   fnd_env_context
where  variable_name = 'APPLTMP'
and    concurrent_process_id = 
      ( select max(concurrent_process_id) from fnd_env_context );

VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/inst/apps/VIS_demo/appltmp

NB: These queries assume your concurrent managers are running!

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

References

Update - Added example output and $APPLTMP output.
at

9 comments:

  1. BinurajWed Apr 07, 03:05:00 PM GMT+12

    Useful information. Thanks

    Ragards
    Binuraj

    ReplyDelete
  2. DavidTue Apr 13, 03:15:00 AM GMT+12

    Another option is to use standard function FND_PROFILE.VALUE(name IN varchar2) return varchar2;

    Best regards,
    David.

    ReplyDelete
  3. GarethTue Apr 13, 08:06:00 AM GMT+12

    Hi David,

    FND_PROFILE.VALUE returns profile option values rather than operating system level environment variables!

    Regards,
    Gareth

    ReplyDelete
  4. AmeliaThu Jun 23, 09:02:00 PM GMT+12

    I am working on EBS but yet not faced any situation to extract environment variables. I will bookmark this post for further use and will try to learn the way to extract variables. Thanks for this helpful information.
    oracle ebs

    ReplyDelete
  5. Kron77Sat Dec 31, 12:27:00 AM GMT+13

    Thanks Gareth, helpful as always. Don't suppose you would know where the autoconfig context values are held? I'm trying to pull the web entry point off the database. Cheers!

    ReplyDelete
  6. GarethTue Jan 03, 01:46:00 PM GMT+13

    Hi Kron77,

    Use profile option value for APPS_FRAMEWORK_AGENT Apps Framework Agent, from fnd_profile_option_values.

    Regards,
    Gareth

    ReplyDelete
  7. Kirti GuptaWed Jun 20, 11:04:00 PM GMT+12

    Hi Gareth,

    Could you please let me know when the fnd_env_context table gets populated with env variable values ?

    actually when i checked with your query in sql it gave me different value from unix box.

    Regards
    Kirti

    ReplyDelete
  8. GarethWed Jun 20, 11:09:00 PM GMT+12

    Hi Kirti,
    I haven't check that for a while, but I would assume either on load of Context File (running adautocfg.sh) or on startup of Concurrent Managers.

    Are you running multiple application servers or other complex setup?

    Please post comment back if you find the answer :-)

    Thanks,
    Gareth

    ReplyDelete
  9. JamesFri Aug 08, 04:31:00 AM GMT+12

    FND_ENV_CONTEXT is populated for each concurrent request at the time that request is run:

    SQL> desc fnd_env_context
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CONCURRENT_PROCESS_ID NOT NULL NUMBER(15)
    VARIABLE_NAME NOT NULL VARCHAR2(128)
    VALUE VARCHAR2(2000)

    ReplyDelete