Display OS variables with dbms_system.get_env

Question:  I want to display OS variables from within my PL/SQL for Oracle applications environment variables.  Is it possible to shell out of PL/SQL and see the current value of OS environment variables?

Answer:  Yes!  Oracle can indeed go out to the OS and gather environmental settings!

We have several procedures and packages within Oracle that allow us to get out of the OS:

• dbms_system.get_env - Gets OS variables
• sys_context - Gets the IP address from within PL/SQL
• utl_inaddr.get_host_address - Gets the hostname from  inside PL/SQL

For complete details, see the great book "Advanced Oracle Utilities", with lots of working examples of using the Oracle utilities inside PL/SQL.

Below, we see dbms_system,get_env used to gather the value of the APPL_TOP environment variable:

var APPL_TOP varchar2(255)
exec dbms_system.get_env('APPL_TOP',:APPL_TOP);

Here is another example of using dbms_system.get_env to display the ORACLE_HOME OS environment variable (for this session):

create or replace procedure getvar(envar in varchar2) as

buffer varchar2(300);

begin

dbms_system.get_env(ENVAR, BUFFER);

dbms_output.put_line(BUFFER);

end;

/

 

Procedure created.

 

SQL> SET SERVEROUTPUT ON

 

SQL> exec getvar('ORACLE_HOME');

/u01/app/oracle/oracle/product/10.2.0/db_4

PL/SQL procedure successfully completed.

As you can see, Oracle has several utilities for displaying external information.

For complete details, see the book "Advanced Oracle Utilities".

---