Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, March 26, 2009

How to get the value of environment variables in PL/SQL code

A well coded program avoids hard-coding of paths and uses variables. If you want to get the value of unix environment variables in your programs, you can use the DBMS_SYSTEM.GET_ENV procedure. For applying it in E-Business Suite, you would need to make sure that the variables are defined in RDBMS $ORACLE_HOME/custom$CONTEXT_NAME.env

For example, I defined environment variable CUSTOM_TOP in a new file $ORACLE_HOME/custom$CONTEXT_NAME.env. This file is called automatically by your database environment file $CONTEXT_NAME.env.

To test, whether the value appears simply do this in an sql session:

SQL> set autoprint on
SQL> var CUSTOM_TOP varchar2(255)
SQL> exec dbms_system.get_env('CUSTOM_TOP',:CUSTOM_TOP);

PL/SQL procedure successfully completed.


CUSTOM_TOP
--------------------------------------------------------------------------------
/custom/apps11i

Here's another code snippet:

1 DECLARE
2 v_file UTL_FILE.FILE_TYPE;
3 V_directory VARCHAR2(255);
4 BEGIN
5 dbms_system.get_env('CUSTOM_TOP', v_directory);
6 v_directory := v_directory||'/out';
7 dbms_output.put_line(v_directory);
8 v_file := UTL_FILE.FOPEN(v_directory,'TEST.DAT','W');
9 UTL_FILE.PUT_LINE(v_file,'This is a test file on CUSTOM_TOP');
10 UTL_FILE.FCLOSE(v_file);
11 EXCEPTION
12 WHEN OTHERS
13 THEN
14 DBMS_OUTPUT.PUT_LINE(SQLERRM);
15* END;
SQL> /
/custom/apps11i

PL/SQL procedure successfully completed.

SQL> host ls -ltr /customnp/apps11i/TEST.DAT
-rw-r--r-- 1 oracle dba 13 Mar 23 16:52 /custom/apps11i/TEST.DAT

SQL> host cat /custom/apps11i/TEST.DAT
This is a test file on CUSTOM_TOP

5 comments:

Anonymous said...

Vikram,

For eBusiness Suite, you can also look at fnd_env_context table in your pl/sql code to get the path to the custom top variables as well.

Thanks,
Shree.

Geek Dad DBA said...

Very nice find. I have never used that, it could be very handy.

Gareth said...

Hi Vikram and Shree,

Nice! Learn something new and helpful every day.

Regards,
Gareth

B Modesto said...

A very informative article. I always wanted to find out how to get those values from an SQL code.

Would these codes work on any SQL version?

Sagar ADF OAF said...

Vikram Sir,

DBMS_SYSTEM.get_env is returning null after x86 Linux RAC Upgrade.

Thanks,
Abhijit