Oracle Apps Knowledge Sharing

Query to get system property (OS name, OS Architecture, OS Version, Oracle Home) and values of environment variables of database server in PLSQL - java.lang.System.getProperty & java.lang.System.getenv

  In this post, we have given a PLSQL functions which inturn uses the standard Java function to gather the database tier OS level parameters and environment variable details. 

Custom Functions:

CREATE OR REPLACE FUNCTION get_system_property(prop IN VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
LANGUAGE JAVA
name 'java.lang.System.getProperty(java.lang.String) return java.lang.String';

CREATE OR REPLACE FUNCTION get_env_var_path(prop IN VARCHAR2) RETURN VARCHAR2
AUTHID DEFINER IS
LANGUAGE JAVA
name 'java.lang.System.getenv(java.lang.String) return java.lang.String';

Query: 

SELECT get_system_property('user.dir') AS oracle_home,
       get_system_property('os.name') AS os_name,
       get_system_property('os.arch') AS os_architecture,
       get_system_property('os.version') AS os_version,
       get_system_property('user.name') AS user_name,
       get_system_property('user.home') AS user_home_directory,
       get_system_property('user.dir') AS user_curr_dir,
       get_system_property('java.vm.version') AS jvm_version,
       get_system_property('java.home') AS JAVA_HOME
FROM dual;

SELECT get_env_var_path('ORACLE_HOME') AS "$ORACLE_HOME",
       get_env_var_path('CONTEXT_FILE') AS "$CONTEXT_FILE",
       get_env_var_path('HOSTNAME') AS "$HOSTNAME"
FROM dual;
System Properties (Quick Reference): 

java.version (Java Runtime Environment version)
java.vendor	(Java Runtime Environment vendor)
java.vendor.url	(Java vendor URL)
java.home	(Java installation directory)
java.vm.specification.version	(Java Virtual Machine specification version)
java.vm.specification.vendor	(Java Virtual Machine specification vendor)
java.vm.specification.name	(Java Virtual Machine specification name)
java.vm.version	(Java Virtual Machine implementation version)
java.vm.vendor	(Java Virtual Machine implementation vendor)
java.vm.name	(Java Virtual Machine implementation name)
java.specification.version	(Java Runtime Environment specification version)
java.specification.vendor	(Java Runtime Environment specification vendor)
java.specification.name	(Java Runtime Environment specification name)
java.class.version	(Java class format version number)
java.class.path	(Java class path)
java.library.path	(List of paths to search when loading libraries)
java.io.tmpdir	(Default temp file path)
java.compiler	(Name of JIT compiler to use)
os.name	(Operating system name)
os.arch	(Operating system architecture)
os.version	(Operating system version)
file.separator	(File separator ("/" on UNIX))
path.separator	(Path separator (":" on UNIX))
line.separator	(Line separator ("\n" on UNIX))
user.name	(User's account name)
user.home	(User's home directory)
user.dir	(User's current working directory)