Oracle Java Functions & Procedures
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Note: This page is dedicated to Java functions I have found on the web with attribution for their authors. Have you written or found an interesting one? If so please email it to me and I will be glad to post it with attribution.
Data Dictionary Objects
ALL_ASSOCIATIONS CDB_ASSOCIATIONS DBA_USTATS
ALL_USTATS CDB_USTATS USER_USTATS
ASSOCIATION$ DBA_ASSOCIATIONS USER_ASSOCIATIONS
Object Privileges GRANT execute ON <object_name> TO <user_name>;
System Privileges
ALTER ANY PROCEDURE CREATE PROCEDURE DROP PROCEDURE
CREATE ANY PROCEDURE DEBUG ANY PROCEDURE EXECUTE ANY PROCEDURE
 
Demo 1
Query to retrieve $ORACLE_HOME
From Chris Poole, Sydney, Australia, on OTN 07-Jan-2008
CREATE OR REPLACE FUNCTION get_java_system_property(prop IN VARCHAR2) RETURN VARCHAR2 AUTHID DEFINER IS LANGUAGE JAVA
name 'java.lang.System.getProperty(java.lang.String) return java.lang.String';
/

SELECT object_type
FROM user_objects
WHERE object_name = 'GET_JAVA_SYSTEM_PROPERTY';

CREATE OR REPLACE VIEW v$oracle_home AS
SELECT get_java_system_property('user.dir') AS oracle_home
FROM dual;

SELECT * FROM v$oracle_home;
 
Demo 2
Java Sleep Procedure From user12389703 in the OTN forums CREATE OR REPLACE PROCEDURE sleep_fn(sec1 NUMBER) AS LANGUAGE JAVA
NAME 'java.lang.Thread.sleep(long)';
/

desc sleep_fn

CREATE TABLE t (
what VARCHAR2(12),
when DATE);

BEGIN
  INSERT INTO t (what, when) VALUES ('Testing1', SYSDATE);
  sleep_fn(10000);  -- 10 seconds
  INSERT INTO t (what, when) VALUES ('Testing2', SYSDATE);
END;
/

SELECT * FROM t;
 
Demo 3
Multivalue view developed by Tom Starr of  based on Chris Poole's code above. 20-Mar-2008 CREATE OR REPLACE VIEW v$system_properties AS
SELECT cast('User''s current working directory (ORACLE_HOME)' AS VARCHAR2(46)) AS PROPERTY_NAME, get_java_system_property('user.dir') AS PROPERTY_VALUE FROM DUAL
UNION ALL SELECT 'Java Runtime Environment version',
get_java_system_property('java.version') FROM DUAL
UNION ALL SELECT 'Java Runtime Environment vendor',
get_java_system_property('java.vendor') FROM DUAL
UNION ALL SELECT 'Java vendor URL',
get_java_system_property('java.vendor.url') FROM DUAL
UNION ALL SELECT 'Java installation directory',
get_java_system_property('java.home') FROM DUAL
UNION ALL SELECT 'Java Virtual Machine specification version',
get_java_system_property('java.vm.specification.version') FROM DUAL
UNION ALL SELECT 'Java Virtual Machine specification vendor',
get_java_system_property('java.vm.specification.vendor') FROM DUAL
UNION ALL SELECT 'Java Virtual Machine specification name',
get_java_system_property('java.vm.specification.name') FROM DUAL
UNION ALL SELECT 'Java Virtual Machine implementation version',
get_java_system_property('java.vm.version') FROM DUAL
UNION ALL SELECT 'Java Virtual Machine implementation vendor',
get_java_system_property('java.vm.vendor') FROM DUAL
UNION ALL SELECT 'Java Virtual Machine implementation name',
get_java_system_property('java.vm.name') FROM DUAL
UNION ALL SELECT 'Java Runtime Environment specification version',
get_java_system_property('java.specification.version') FROM DUAL
UNION ALL SELECT 'Java Runtime Environment specification vendor',
get_java_system_property('java.specification.vendor') FROM DUAL
UNION ALL SELECT 'Java Runtime Environment specification name',
get_java_system_property('java.specification.name') FROM DUAL
UNION ALL SELECT 'Java class format version number',
get_java_system_property('java.class.version') FROM DUAL
UNION ALL SELECT 'Java class path',
get_java_system_property('java.class.path') FROM DUAL
UNION ALL SELECT 'List of paths to search when loading libraries',
get_java_system_property('java.library.path') FROM DUAL
UNION ALL SELECT 'Default temp file path',
get_java_system_property('java.io.tmpdir') FROM DUAL
UNION ALL SELECT 'Name of JIT compiler to use',
get_java_system_property('java.compiler') FROM DUAL
UNION ALL SELECT 'Path of extension directory or directories',
get_java_system_property('java.ext.dirs') FROM DUAL
UNION ALL SELECT 'Operating system name',
get_java_system_property('os.name') FROM DUAL
UNION ALL SELECT 'Operating system architecture',
get_java_system_property('os.arch') FROM DUAL
UNION ALL SELECT 'Operating system version',
get_java_system_property('os.version') FROM DUAL
UNION ALL SELECT 'File separator ("/" on UNIX)',
get_java_system_property('file.separator') FROM DUAL
UNION ALL SELECT 'Path separator (":" on UNIX)',
get_java_system_property('path.separator') FROM DUAL
UNION ALL SELECT 'Line separator ("\n" on UNIX)',
get_java_system_property('line.separator') FROM DUAL
UNION ALL SELECT 'User''s account name',
get_java_system_property('user.name') FROM DUAL
UNION ALL SELECT 'User''s home directory',
get_java_system_property('user.home') FROM DUAL
/

col property_value format a60

SELECT * FROM v$system_properties
ORDER BY 1;

Related Topics
Built-in Functions
Functions
Hadoop
Procedures

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved