Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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;
CREATE SYNONYM v$oracle_home FOR sys.v_$oracle_home;
SELECT * FROM v$oracle_home;
Demo 2
Java Sleep Procedure From user12389703 in the OTN forums
CREATE OR REPLACE PROCEDURE
sleep_fn(sec1 NUMBER) AUTHID DEFINER 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);
COMMIT;
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
/