How do I load OS Variables into PL/SQL?

Question

This should be pretty straightforward but the search words are so common that I am not finding an answer:

Q. How do I load the values from an operating system variable (RHEL) into PL/SQL on an Oracle 11g database?

Code:

begin
dba_utilities.utilities_with_ext_proc.send_email(
        p_recipient   => '$MAIL_LIST',
        p_subject     => 'Subject'
        p_body        => 'Body
);
End ;            

The procedure works fine if I put in an email address but there is a system variable called $MAIL_LIST that contains a group of people. I can also confirm that this is working on other servers but I am setting up a replacement.

Thanks in advance and sorry if the question is overly simple!

Answer 1

You can access the environment variables via the getenv method in Java's System class. To invoke this from PL/SQL, you can create a Java stored procedure as follows:

CREATE FUNCTION getenv(name VARCHAR2) RETURN VARCHAR2
  AS LANGUAGE JAVA 
  NAME 'java.lang.System.getenv(java.lang.String) return java.lang.String';

Now, you can write your PL/SQL code as follows:

begin
dba_utilities.utilities_with_ext_proc.send_email(
        p_recipient   => getenv('MAIL_LIST'),
        p_subject     => 'Subject'
        p_body        => 'Body
);
End ; 

The stored procedure can also exist inside a PL/SQL package (if you don't want to create a schema-level function just to acquire environment variables).