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.

9 comments:

Binuraj said...

Useful information. Thanks

Ragards
Binuraj

David said...

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

Best regards,
David.

Gareth said...

Hi David,

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

Regards,
Gareth

Amelia said...

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

Kron77 said...

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!

Gareth said...

Hi Kron77,

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

Regards,
Gareth

Kirti Gupta said...

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

Gareth said...

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

James said...

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)