Top 5 Recent Articles

ARTICLES CATEGORIES

Writing xSV (eg csv) files from within the Oracle database using Java and PL/SQL

My EXPORTER package + Java code has a function called WriteDelimitedTextfile that can be used to export any SQL SELECT statement to disk using Oracle database’s DBMS_JOB or DBMS_SCHEDULER.

Yes, you can export to disk via pure PL/SQL but I have added this function to my EXPORTER to create an integrated solution for exports that include SDO_GEOMETRY objects.

You will note that when exporting a SQL SELECT statement with an SDO_GEOMETRY object that you can specify the export text format for all the SDO_GEOMETRY objects in the result set. This format can be Well Known Text (WKT) or Geography Markup Language (GML) version 2 (version 3 can only be made available for Oracle databases supporting JVM 1.5 or above).

Here is an example of an export.

 BEGIN
   codesys.exporter.WriteDelimitedTextfile(
      p_sql             => 'SELECT ID,SPECIES,STATION_NUMBER,LENGTH,WEIGHT,SEX,MATURITY,WATER_CHECK,SYSDATE as export_date,GEOM FROM GUTDATA',
      p_outputDirectory => 'C:\temp\',
     p_fileName        => 'gutdata.csv',
     p_FieldSeparator  => ',',
     p_TextDelimiter   => '"',
     p_DateFormat      => 'yyyy/MM/dd hh:mm:ss a',
     p_geomFormat     => EXPORTER.c_WKT_FORMAT,
     p_precision      => 3 );
 End;
 /

With the export file looking like….

ID,SPECIES,STATION_NUMBER,LENGTH,WEIGHT,SEX,MATURITY,WATER_CHECK,EXPORT_DATE,GEOM
204,2,6,218,121.1,“F”,“M”,“O”,“2011/10/12 06:01:33 PM”,“POINT (133.5381 -5.9327)”
205,2,2,383,199.5,“M”,“M”,“O”,“2011/10/12 06:01:33 PM”,“POINT (155.0342 -39.1499)”
206,7,21,338,116.2,“M”,“M”,“O”,“2011/10/12 06:01:33 PM”,“POINT (107.1845 -30.9735)”
207,2,28,379,258.5,“M”,“M”,“O”,“2011/10/12 06:01:33 PM”,“POINT (107.0394 -14.7986)”
208,1,11,452,276.5,“F”,“I”,“O”,“2011/10/12 06:01:33 PM”,“POINT (153.999 -45.8678)”
210,6,38,300,138.4,“F”,“M”,“O”,“2011/10/12 06:01:33 PM”,“POINT (158.1937 -42.6954)”
212,8,59,252,243.6,“M”,“I”,“O”,“2011/10/12 06:01:33 PM”,“POINT (142.0212 -5.7516)”
…..

Here is another example:

 CREATE TABLE source_data
     ( x, y, z
     , CONSTRAINT source_data_pk
         PRIMARY KEY (x,y,z)
     )
     ORGANIZATION INDEX
     AS
        SELECT ROWNUM           AS x
        ,      RPAD('x',50,'x') AS y
        ,      RPAD('y',50,'y') AS z
       FROM   dual
       CONNECT BY ROWNUM <= 100000;
 -- Results
 TABLE SOURCE_DATA created.
 -- Display structure
 DESCRIBE source_data
 -- Results
 Name NULL     TYPE
 ---- -------- ------------
 X    NOT NULL NUMBER
 Y    NOT NULL VARCHAR2(50)
 Z    NOT NULL VARCHAR2(50)
 -- Show some data
 SELECT x,y,z FROM codesys.source_data;
 -- Results...
 X Y                                                  Z
 - -------------------------------------------------- --------------------------------------------------
 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
 2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
 3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
 -- Now do export
 SET timing ON autotrace ON
 -- Results
 Autotrace Enabled
 Shows the execution plan AS well AS statistics OF the statement.
 -- Run Export
 BEGIN
   codesys.exporter.WriteDelimitedTextfile(
    p_sql             => 'select x,y,z from codesys.source_data',
    p_outputDirectory => 'c:\temp',
    p_fileName        => 'source_data.csv',
    p_FieldSeparator  => ',',
    p_TextDelimiter   => '"',
    p_DateFormat      => codesys.EXPORTER.c_DATEFORMAT,
    p_geomFormat      => codesys.EXPORTER.c_WKT_Format,
    p_precision       => 3);
 END;
 /
 -- Results
 anonymous block completed
 Elapsed: 00:00:03.813
    Statistics
 -----------------------------------------------------------
                3  USER calls
          9019392  physical READ total bytes
                0  physical WRITE total bytes
                0  spare statistic 3
                0  commit cleanout failures: cannot pin
                0  TBS Extension: bytes extended
                0  total NUMBER OF times SMON posted
                0  SMON posted FOR undo segment recovery
                0  SMON posted FOR dropping temp segment
                0  segment prealloc tasks
 -- Clean up
 SET timing off autotrace off
 -- Results
 Autotrace Disabled
 -- And ....
 DROP TABLE source_data purge;
 TABLE SOURCE_DATA dropped.

The first four rows in c:\temp\source_data.csv:

X,Y,Z
1.0,“xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”,“yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”
2.0,“xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”,“yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”
3.0,“xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”,“yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”
4.0,“xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”,“yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”

I hope this helps someone.