This discussion is archived
4 Replies Latest reply: Nov 13, 2012 7:09 PM by rp0428 RSS

Java class in Java Stored Proc to create native Excel file

pl/sql novice Newbie
Currently Being Moderated
Hi all,
I developed a small Java program that uses Apache POI SXSSF libraries to create Excel file. It runs on my PC and gets the data via JDBC from Oracle DB server running on IBM AIX. The good thing about this library is I can control the number of rows it keeps in memory, so even though the output file is 27 MB, and I'm running many applications on my PC, the program managed to create the Excel smoothly, although it took longer than I like (45 min), and created a 300 MB temp file, which was removed automatically when program finished.

Just wondering if it is possible to create a Java stored proc to embed this program inside the DB server, so it can avoid all the network traffic time. The problem is it uses a lot of libraries that need to be imported into the DB server as well, just for this little report program.

The program can be called from PL/SQL program, which in turn can be scheduled by concurrent manager.
The result can be stored inside some BLOB column and FTP-ed to some remote server for user access.

Do you think this is a viable project? Or simply too much trouble to create an Excel report?
Many thanks.
  • 1. Re: Java class in Java Stored Proc to create native Excel file
    gimbal2 Guru
    Currently Being Moderated
    pl/sql novice wrote:
    Just wondering if it is possible to create a Java stored proc to embed this program inside the DB server, so it can avoid all the network traffic time.
    But is the network traffic a problem?

    Because you want to move business logic in a database just to avoid some traffic which you already throttle by doing batching.
  • 2. Re: Java class in Java Stored Proc to create native Excel file
    rp0428 Guru
    Currently Being Moderated
    >
    Just wondering if it is possible to create a Java stored proc to embed this program inside the DB server, so it can avoid all the network traffic time
    >
    Hard to say since you didn't provide your 4 digit Oracle version. The 11g versions use Java 1.5 so libraries that use 1.6 or later features are not going to compile or work in the database.

    As Gimbal2 already impilcitly suggested if you think network traffic time is a problme the obvious test to perform is to run your existing application solely on the DB server itself.

    That eliminates the network altogether and the results will tell you how much of a roll the network plays.

    See the JDBC Dev guide for details about the server-side thin driver
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/jdbcthin.htm
  • 3. Re: Java class in Java Stored Proc to create native Excel file
    pl/sql novice Newbie
    Currently Being Moderated
    Thanks a lot for the pointers!
    I check the DB version and it is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.
    I complied my Java program using JDK 1.6.0_17, and the other libraries need Java 1.6 also, so it probably won't compile in Oracle 11g.

    Yes, the fastest way to generate this report is probably using Oracle Report to create delimited text output file, but users has been receiving native Excel files for a long time (created by using some 3rd party tool which we're going to obsolete) so they're unlikely to be happy to see a delimited text file.

    Well, unless I can figure out a way (in PL/SQL) to turn a delimited text output file into a native Excel file by inserting all the extra Excel tags inside the file. That would be tough to do.

    Thanks again.
  • 4. Re: Java class in Java Stored Proc to create native Excel file
    rp0428 Guru
    Currently Being Moderated
    >
    I complied my Java program using JDK 1.6.0_17, and the other libraries need Java 1.6 also, so it probably won't compile in Oracle 11g.
    >
    It won't compile 'inside' the DB since the DB Java version is 1.5 but you can still do the test I suggested.

    Did you miss this part of my reply?
    >
    the obvious test to perform is to run your existing application solely on the DB server itself.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points