I wanted to blog on Java stored procedures, the problem being that all I could come up with was something really contrived such as the infamous ATM machine example that always gets trotted out in training course material. Fortunately I came up with the requirement for a general purpose DBA utilities package, the requirements of which included:-
Below is my PL/SQL package that uses the FileUtils class, if you read the comment banner at the top of package, instruction is provided for compiling and loading the Java class. You can create the source in SQLPLUS via the CREATE JAVA SOURCE command, however my preference is to always write the source in Eclipse, compile it and then load it into the database via loadjava. My apologies for anyone reading this using Google Chrome, for some reason the formatting of the PL/SQL goes awry, Firefox, however, has no such problem.
Use cases for Java stored procudures include:-
- A "Poor mans" data guard using data pump
- The ability to rotate alert logs etc
- The ability to produce data pump exports, compress them and maintain an archive, i.e. if you wanted seven days worth of dmp files, when you reached seventh file, as soon as the newest one is produced, the oldest one would be purged.
My main motivation for writing this was to develop a utility package that was portable across Windows and Unix, hence saving the effort in having to maintain multiple scripts.
The second two requirements is where Java stored procedures came in. Although a full desemination of Java stored procedures is beyond the scope of a simple blog posting, I'll bullet point some of more salient points:-
- The ability to run Java within the database was introduced with Oracle 8i.
- 10g supports code compiled with the J2SE 1.4 JDK and 11g supports code compiled with the J2SE 1.5 JDK.
- All Java methods to be wrapped by PL/SQL need to be declared as public (obviously !) and static.
- The embedded JVM run time uses a different security model compared to the conventional JVM outside the database. Permissions are granted via certain database roles such as JAVASYSPRIV, more fine grained permissions can be granted using the DBMS_JAVA PL/SQL package.
- Java in the database scales using sessions rather than threads.
- SQL is run via an embedded JDBC driver.
- The Oracle 11g JVM furnishes a JIT compiler, prior to this, Java had to be compiled ahead of time using NCOMP.
- System classes are shared between sessions so as to reduce memory and general resource overheads.
Here is my Java class, much of the code comes courtesy of the Java almanac. I've omitted the use of featues such as annotations, the string builder class and the Java foreach loop, so as to make the code portable between 10 and 11g. Also, note that I've had to remove the original dump file after it has been compressed as the Oracle JVM does not remove the compressed file after the zip stream is closed.
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;
public class FileUtils {
/**
*
* This is utility code to be used by the dbutilspkg PL/SQL package, it will purge
* and files from the specified by directory that have:-
*
* 1) Are suffixed by bak.zip
* 2) If there are more than NumFilesToKeep files in the directory with the bak.zip
* extension, it will purge the oldest files, such that only NumFilesToKeep #
* of files remain.
*
* @param Directory directory containing the compressed dump
* files to scan.
* @param NumFilesToKeep number of compressed dump files to keep
*
*/
public static void PurgeDmpFiles(String Directory, long NumFilesToKeep) throws Exception {
int DumpFiles = 0;
int StrLen;
File files[] = new File(Directory).listFiles();
Arrays.sort( files, new Comparator()
{
public int compare(final Object o1, final Object o2) {
return new Long(((File)o1).lastModified()).compareTo
(new Long(((File) o2).lastModified()));
}
} );
for (int i = 0; i < files.length; i++) {
StrLen = files[i].getName().length();
if (StrLen > 7) {
if ( files[i].getName().substring(StrLen - 7, StrLen).equals("bak.zip") ) {
DumpFiles++;
System.out.println("Found a gzipped dump file");
System.out.println("<<" + files[i].getName() + ">>");
if ( DumpFiles > NumFilesToKeep ) {
System.out.println("Deleting a dump file");
files[i].delete();
}
}
}
}
}
/**
*
* Java code to gzip a file based on code from the Java Almanac, note that because
* the JVM embedded in the Oracle RDBMS does not remove the original file after the
* zip stream has been closed, this has had to be coded for.
*
* @param fileName absolue path of file to zip
*
*/
public static void GzipFile(String fileName) {
try {
// Create the GZIP output stream
String outFilename = fileName + ".Z";
GZIPOutputStream out = new GZIPOutputStream(new FileOutputStream(outFilename));
// Open the input file
FileInputStream in = new FileInputStream(fileName);
// Transfer bytes from the input file to the GZIP output stream
byte[] buf = new byte[1024];
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
in.close();
// Complete the GZIP file
out.finish();
out.close();
File finalFile = new File(fileName + "bak.zip");
File tempFile = new File(fileName + ".Z");
tempFile.renameTo(finalFile);
boolean success = (new File(fileName).delete());
if (!success) {
System.err.println("Unable to delete uncompressed dmp file: " + fileName);
}
} catch (IOException e) {
System.err.println(e.getMessage());
}
}
/**
*
* Java code to un-gzip a file.
*
* @param fileName absolue path of file to zip
*
*/
public static void UnGzipFile(String fileName) {
try {
// Open the compressed file
GZIPInputStream in = new GZIPInputStream(new FileInputStream(fileName));
// Open the output file
String outFilename = "outfile";
OutputStream out = new FileOutputStream(outFilename);
// Transfer bytes from the compressed file to the output file
byte[] buf = new byte[1024];
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
// Close the file and stream
in.close();
out.close();
} catch (IOException e) {
System.err.println(e.getMessage());
}
}
/**
*
* Java code to rotate a file if its length exceeded the specified
* rotation size (bytes).
*
* @param fileName absolue path of file to zip
* @param RotateSize size threshhold for file rotation
*
*/
public static void RotateFile(String FileName, long RotateSize) {
File file = new File(FileName);
if ( file.exists() ) {
if ( file.length() >= RotateSize ) {
SimpleDateFormat dateformatMMDDYYYYHHmm = new SimpleDateFormat("MMddyyyyHHmm");
StringBuffer nowMMDDYYYYHHmm = new
StringBuffer( dateformatMMDDYYYYHHmm.format( new Date() ) );
File rotatedFile = new File(FileName + "." + nowMMDDYYYYHHmm);
// Rename file (or directory)
file.renameTo(rotatedFile);
System.out.println("File rotated");
}
else {
System.out.println("File below rotation size");
}
}
else
{
System.out.println("File not rotated");
}
}
}
Below is my PL/SQL package that uses the FileUtils class, if you read the comment banner at the top of package, instruction is provided for compiling and loading the Java class. You can create the source in SQLPLUS via the CREATE JAVA SOURCE command, however my preference is to always write the source in Eclipse, compile it and then load it into the database via loadjava. My apologies for anyone reading this using Google Chrome, for some reason the formatting of the PL/SQL goes awry, Firefox, however, has no such problem.
CREATE OR REPLACE PACKAGE BODY DbUtilsPkg AS
/*==========================================================================*/
/* */
/* Name: DbUtilsPkg (BODY) */
/* */
/* Version: 1.0 */
/* */
/* Description: This package includes useful DBA utilities, namely:- */
/* */
/* 1) A procedure, pump_backup_db, to data pump export */
/* full databases, gzip the dmp files on successful */
/* completion and then remove any obsolete dmp files */
/* , depending on the number of dump file to retain */
/* parameter value specified. */
/* */
/* 2) Rotate log files once they have exceeded the */
/* specified file rotation size. */
/* */
/* Special Notes */
/* ~~~~~~~~~~~~~ */
/* The "DpCopySchema" procedure is intended to function as a poor mans */
/* data guard, as such the destination schemas objects are dropped. I */
/* have elected against dropping the schema, as this would require */
/* the password of the destination schema to be reset afterwards, for */
/* security purposes this package does not handle or manage any */
/* passwords. */
/* */
/* The source code for this, with the exception of the procedures to */
/* drop schema objects comes coutesy of Dani Rey from Trivadis AG, */
/* refer to: http://danirey.wordpress.com . */
/* */
/* Dependencies */
/* ~~~~~~~~~~~~ */
/* This package is dependant upon the FileUtils java class, the source */
/* code for this should be compiled using the version 5 JDK as follows */
/* :- */
/* */
/* javac FileUtils.java */
/* */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* !!! NOTE !!! Use the J2SE 1.4 JDK javac for Oracle 10g */
/* Use the J2SE 1.5 JDK javac for Oracle 11g *
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* */
/* This will result in to java classes being produced: FileUtils$.class */
/* and FileUtils$1.class, these then need to be loaded into the */
/* database as follows:- */
/* */
/* loadjava r -f username/password FileUtils.class FileUtils$1.class */
/* */
/* Should you wish to debug the java, messages displayed via */
/* System.out.println will appear on the SQPLUS command line, if the */
/* Java is wrapped with PL/SQL after DBMS_JAVA.SET_OUTPUT(<bufferSize>) */
/* and set serveroutput on are issued/ */
/* */
/* Note that the security model for executing Java within the database */
/* differs slightly from that used by PL/SQL, therefore, in order to be */
/* able to write to files, DBMS_JAVA must be used to grant the pre- */
/* requisite permissions to the relevant users:- */
/* */
/* EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', */
/* 'java.io.FilePermission', */
/* '<<ALL FILES>>', */
/* 'read ,write, execute, delete'); */
/* */
/* Grant JAVASYSPRIVS to the relevant user will grant blanket file CRUD */
/* permissions. */
/* */
/* Additionally, the following object permissions are required, unless */
/* this package is run as SYS or SYSTEM or a user with he DBA role */
/* privilege:- */
/* */
/* 1) SELECT on V$DATABASE */
/* 2) EXECUTE on DBMS_DATAPUMP */
/* 3) SELECT privilege on DBA_USERS */
/* 4) execute privilege on DBMS_FLASHBACK */
/* 5) EXP_FULL_DATABASE sys privilege */
/* Not to be confused with EXPORT FULL DATABASE. */
/* 6) CREATE TABLE privilege */
/* 7) SELECT privilege on DBA_DIRECTORIES. */
/* 8) SELECT privilege on V$PARAMETER. */
/* 9) For the schema copy utility:- */
/* i) A database link connecting the destination to the source */
/* schema must exist. */
/* ii) The destination schema must have IMP_FULL_DATABASE and */
/* EXP_FULL_DATABASE privileges. */
/* iii) The source schema must have IMP_FULL_DATABASE and */
/* EXP_FULL_DATABASE privileges. */
/* */
/* History */
/* ~~~~~~~ */
/* */
/* Author Date Change Description */
/* =========== ========== ========================================= */
/* C. J. Adkin 21/09/2009 Initial version created */
/* */
/*==========================================================================*/
PROCEDURE PumpBackupDb (PumpDirectory IN VARCHAR2,
DmpFilesToRetain IN NUMBER DEFAULT 7) IS
DirectoryExists NUMBER;
DpHandle NUMBER;
DoP NUMBER;
DbName VARCHAR2(32);
JobState VARCHAR2(32);
DmpFileAbsolutePath VARCHAR2(100);
DmpFileName VARCHAR2(100);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('Start time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
BEGIN
SELECT 1
INTO DirectoryExists
FROM dba_directories
WHERE directory_name = PumpDirectory;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Data pump directory '||PumpDirectory||' does not exist');
RAISE;
END;
--
SELECT name
INTO DbName
FROM v$database;
--
DmpFileName := DbName||'_FULL_'||TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI');
--
DBMS_OUTPUT.PUT_LINE('Openning data pump job');
--
DpHandle := DBMS_DATAPUMP.OPEN (operation => 'EXPORT' ,
job_mode => 'FULL' );
--
DBMS_OUTPUT.PUT_LINE('Adding data pump dmp file');
--
DBMS_DATAPUMP.ADD_FILE (handle => DpHandle ,
filename => DmpFileName||'.dmp',
directory => PumpDirectory);
DBMS_OUTPUT.PUT_LINE('Adding data pump log file');
--
DBMS_DATAPUMP.ADD_FILE (handle => DpHandle ,
filename => DmpFileName||'.log',
directory => PumpDirectory,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
--
SELECT value
INTO DoP
FROM v$parameter
WHERE name = 'cpu_count';
--
IF DoP > 1 THEN
DBMS_DATAPUMP.SET_PARALLEL(handle => DpHandle,
degree => DoP);
END IF;
--
DBMS_DATAPUMP.START_JOB(DpHandle);
--
DBMS_DATAPUMP.WAIT_FOR_JOB(DpHandle, JobState);
--
DBMS_DATAPUMP.DETACH(DpHandle);
--
SELECT directory_path
INTO DmpFileAbsolutePath
FROM dba_directories
WHERE directory_name = PumpDirectory;
--
DBMS_OUTPUT.PUT_LINE(DmpFileAbsolutePath);
--
GzipFile(DmpFileAbsolutePath||'\'||DmpFileName||'.dmp');
--
PurgeDumpFiles(DmpFileAbsolutePath, DmpFilesToRetain);
--
DBMS_OUTPUT.PUT_LINE('End time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
--dbms_datapump.stop_job(DpHandle);
RAISE;
END;
-------------------------------------------------------------------------------
PROCEDURE PumpCopySchema(sourceSchema IN VARCHAR2,
destinationSchema IN VARCHAR2,
databaseLink IN VARCHAR2,
debug IN BOOLEAN := FALSE) IS
handle NUMBER;
message VARCHAR2(2000);
flashbackSCN NUMBER;
logfileName VARCHAR2(200);
destinationSchemaExists NUMBER;
jobState user_datapump_jobs.state%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('verify whether destination schema '||destinationSchema||' already exists');
SELECT COUNT(username)
INTO destinationSchemaExists
FROM dba_users
WHERE username = destinationSchema
AND username NOT IN ('SYS','SYSTEM','DBSNMP','DIP','EXFSYS','OUTLN','TSMSYS');
--
IF ( destinationSchemaExists = 0 ) THEN
DBMS_OUTPUT.PUT_LINE ('---> !!! Source schema does not exist !!!');
ELSE
DBMS_OUTPUT.PUT_LINE ('open datapump job over database link '||databaseLink);
--
handle := dbms_datapump.open (operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => databaseLink);
--
IF debug = TRUE THEN
DBMS_OUTPUT.PUT_LINE('set logfile parameter');
logfileName := 'datapump_'||to_char(SYSDATE,'YYYYMMDD')||'.log';
--
DBMS_DATAPUMP.ADD_FILE (handle => handle,
filename => logfileName,
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);
END IF;
--
DBMS_OUTPUT.PUT_LINE('set FLASHBACK_SCN parameter');
--
DBMS_DATAPUMP.SET_PARAMETER (handle => handle,
name => 'FLASHBACK_SCN',
value => dbms_flashback.get_system_change_number);
--
DBMS_DATAPUMP.SET_PARAMETER (handle => handle,
name => 'TABLE_EXISTS_ACTION',
value => 'TRUNCATE' );
--
DBMS_OUTPUT.PUT_LINE ('define SCHEMA_LIST');
--
DBMS_DATAPUMP.METADATA_FILTER (handle => handle,
name => 'SCHEMA_LIST',
value => ''''||sourceSchema||'''');
--
DBMS_OUTPUT.PUT_LINE ('define schema remapping');
--
DBMS_DATAPUMP.METADATA_REMAP (handle => handle,
name => 'REMAP_SCHEMA',
old_value => sourceSchema,
value => destinationSchema);
--
DBMS_OUTPUT.PUT_LINE('start datapump job');
--
DBMS_DATAPUMP.START_JOB (handle);
--
DBMS_OUTPUT.PUT_LINE ('wait for job to finish');
DBMS_DATAPUMP.WAIT_FOR_JOB (handle, jobState);
END IF;
--
DBMS_OUTPUT.PUT_LINE('End time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = 31631 THEN
DBMS_OUTPUT.PUT_LINE ('Caught ORA-31631, ensure that both source and destination ' ||
'schemas have IMP_FULL_DATABASE and EXP_FULL_DATABASE ' ||
'privileges');
ELSE
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END IF;
END;
----------------------------------------------------------------------------
PROCEDURE PurgeDmpFiles (Directory VARCHAR2,
NumFilesToKeep NUMBER)
IS LANGUAGE JAVA
NAME 'FileUtils.PurgeDmpFiles(java.language.String, long)';
----------------------------------------------------------------------------
PROCEDURE GzipFile (FileName VARCHAR2)
IS LANGUAGE JAVA
NAME 'FileUtils.GzipFile(java.language.String)';
----------------------------------------------------------------------------
PROCEDURE RotateFile (FileName VARCHAR2,
RotateSize NUMBER)
IS LANGUAGE JAVA
NAME 'FileUtils.RotateFile(java.language.String, long)';
END DbUtilsPkg;
Use cases for Java stored procudures include:-
- The need to integrate the database with other systems and middle ware components which have APIs accessible from Java.
- "A better PL/SQL", I mainly use PL/SQL as a wrapper for SQL in order to leverage the flow control constructs that PL/SQL gives you. PL/SQL does have object extensions, however, I find these 'clunky' compared to those in Java. Java also supports some basic language features that PL/SQL does not, such as enumeration.
- Moving some of the business logic out of the middle tier and closer to the data in order to improve performance and scalability by reducing network round trips between the middle tier and the database.
- A way to leverage skills of Java programmers within the database.
- Accessing operating system commands from within the database.
- OTN: Samples Corner: Java Stored Procedures
- OTN: Oracle JVM and Java Stored Procedures
- OTN: Oracle JVM Forum
- 360 Degree Programming Blog
