5 Replies Latest reply: Dec 14, 2012 10:17 AM by 977551 RSS

    PL/SQL and JAVA

    977551
      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
          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
            Wonderful thanks a lot!!! I'm going to study it!
            • 3. Re: PL/SQL and JAVA
              AlbertoFaenza
              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
                >
                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
                  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...