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