My current project has me building a generic based extraction process in Oracle using OWB (Oracle Warehouse Builder). One of the issues is when an extract is so large it really needs to be compressed before any FTP activity occurs. The good news is you can. YEAH!!!!
The approach I took was utilizing Java, which can be used in Oracle. Being more of a C# developer, using Java is not that much of a stretch, but the debugging can be a real pain. I used SQL Developer and JDeveloper to create and Debug the code, which really gives poor error messages, if any at all. I am sure there must be a better way, and I will take some time in the future to find out how. Below is the code I used to take a path and filename and create a zip file in the same folder. There are a lot of write ups on this subject, but for me the best was located at http://www.javafaq.nu/java-example-code-224.html
You simple run this in SQLDeveloper to create a Java Class in the user schema. I place comments in the code to tell you what is happening. One of the things to really point out is the issue of parameters with an IN OUT. There is a great write up that helped me at http://www.cs.umbc.edu/help/oracle8/java.815/a64686/03_pub3.htm
create or replace and compile java source named "CreateZipFile"
public class CreateZipFile
public static void DoCreateZipFile( String pPathAndFileName ,String pErrorMessage ,int pReturn)
int ReturnResults = 0;
String oZipFileName ;
byte buf = new byte;
FileInputStream FIS = null;
ZipOutputStream ZOS = null;
//Create the path and file name for the zip file
oZipFileName = pPathAndFileName.substring(0,pPathAndFileName.lastIndexOf(".")) + ".zip";
// build the file objects
File oInFile = new File( pPathAndFileName);
File oOutFile = new File( oZipFileName);
//Create the output zip streams
FileOutputStream FOS = new FileOutputStream(oOutFile);
ZOS = new ZipOutputStream(FOS);
//Set the highest level of compression
//Create an entry for the single file we are zipping up
ZOS.putNextEntry(new ZipEntry( oInFile.getName()));
//create the infile input stream
FIS = new FileInputStream( oInFile);
//Read in chucks of the input file and insert into the output file
while((len = FIS.read(buf)) > 0)
ZOS.write(buf, 0, len);
//close up the single entry
//Close in and out files
catch (Exception e)
ReturnResults = 1;
pErrorMessage = e.getMessage();
Now that you have created and installed the Java code, congrads, you can now zip a file,… almost. First you must create a spec for this method via an Oracle Procedure or Function.
create or replace
PROCEDURE jproc_Zipfile(pPathAndFileName in varchar2, pErrorMessage in out varchar2, pReturn in out NUMBER)
java name 'CreateZipFile.DoCreateZipFile(java.lang.String,java.lang.String,int) ';
Now that you have a spec for this method, congrads, you can now zip a file,… not quite yet. Next you have to give your user permission to interact with the file structure.
DBMS_JAVA.grant_permission('GEAP_PROJECT', 'java.io.FilePermission', 'J:\GEAP_EXTRACTS\', 'read ,write, execute, delete');
Now that you have give your user permission, congrads, you can now zip a file,… finally (make sure about OS file permissions as well).
PPATHANDFILENAME := 'C:\TEMP\danny2.csv';
PERRORMESSAGE := NULL;
PRETURN := NULL;
PPATHANDFILENAME => PPATHANDFILENAME,
PERRORMESSAGE => PERRORMESSAGE,
PRETURN => PRETURN
DBMS_OUTPUT.PUT_LINE('PERRORMESSAGE = ' PERRORMESSAGE);
DBMS_OUTPUT.PUT_LINE('PRETURN = ' PRETURN);
There are a couple of caveats to point out at this point. To the best that I can tell, you cannot add a password protection using just the Java library, it just isn’t there. From what I am readying, this has been on the request list now for eight years.
Secondly, when I was creating this, I was in a windows environment. If you happen to test by hard coding the path such as 'C:\TEMP\danny2.csv', remember Java required double slashes, such as 'C:\\TEMP\\danny2.csv'
Hopefully I was able to hit on all the issues I ran across and that this article will help you. It’s Friday, and almost Beer time.