This discussion is archived
5 Replies Latest reply: Dec 14, 2012 8:17 AM by 977551 RSS

PL/SQL and JAVA

977551 Newbie
Currently Being Moderated
Hi All,

i need to do a loop on a table and export the data in Excel format (so i need a procedure to do it).
My idea is to write a java class that build this Excel.. oracle procedure loop around the data and every step my Java class write on Excel file.

So, for this i should initialize my java class for example
 public class ExcelExporter{
    private String fileName;

   public ExcelExporter(String fileName){
      this.fileName = fileName;
   }
 }
So, for this issuse i should call the ExcelExporter costructor from my PL/SQL so i can create an object of it and next i use this to populate my Excel.

How its possible to create Java object from PLSQL ? I've seen on the net all procedure call only Java static methods...
  • 1. Re: PL/SQL and JAVA
    Hoek Guru
    Currently Being Moderated
    If you want to create an Excel file from PL/SQL you could also explore these links from the SQL and PL/SQL FAQ: {message:id=9360007}
  • 2. Re: PL/SQL and JAVA
    977551 Newbie
    Currently Being Moderated
    Wonderful thanks a lot!!! I'm going to study it!
  • 3. Re: PL/SQL and JAVA
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    You can also consider creating and HTML file which can be read easily with Excel.

    i.e.:
    SET SERVEROUTPUT ON SIZE UNLIMITED
    SET LIN 500
     
    SET DEFINE OFF
    SET UNDERLINE OFF
    SET TAB OFF
    SET FEEDBACK OFF
    SET VERIFY OFF
    SET TERMOUT OFF
    SET PAGES 9999
     
    SET MARKUP HTML ON TABLE "cellspacing=0 border=1" ENTMAP OFF
     
    spool myexcel.xls
     
    /* Replace the query below as you like */
    SELECT * FROM EMP;
     
    spool off
     
    EXIT
    The format is HTML but once you load in Excel you can easily save as the format you like.

    Additional info in this post:Re: Saving SQL+ output to a text file

    Regards.
    Al
  • 4. Re: PL/SQL and JAVA
    rp0428 Guru
    Currently Being Moderated
    >
    need to do a loop on a table and export the data in Excel format (so i need a procedure to do it).
    My idea is to write a java class that build this Excel.. oracle procedure loop around the data and every step my Java class write on Excel file.
    >
    Oracle already wrote code that can export data to Excel (i.e. CSV) format. It's free code and is called sql developer.
    SELECT /*csv*/ c.email_addr, c.job_title,. . . FROM myTable;
    You use a comment that is very similar to a hint but does not have the +, spaces and is case-sensitive.

    See this article by Jeff Smith, a frequent sql developer forum contributor, for other export options
    http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/
    >
    The Code

    SELECT /*csv*/ * FROM scott.emp;
    SELECT /*xml*/ * FROM scott.emp;
    SELECT /*html*/ * FROM scott.emp;
    SELECT /*delimited*/ * FROM scott.emp;
    SELECT /*insert*/ * FROM scott.emp;
    SELECT /*loader*/ * FROM scott.emp;
    SELECT /*fixed*/ * FROM scott.emp;
    SELECT /*text*/ * FROM scott.emp;

    You need to execute your statement(s) as a script using F5 or the 2nd execution button on the worksheet toolbar. You’ll notice the hint name matches the available output types on the Export wizard.
  • 5. Re: PL/SQL and JAVA
    977551 Newbie
    Currently Being Moderated
    I have to export Data in Excel with a procedure because i have to use it on another program, so i cant extract data with SQL Developer or others tools...

Legend

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