Oracle Java Functions & Procedures
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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
What's New In 12cR2
What's New In 18cR3

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-2019 Daniel A. Morgan All Rights Reserved