|
Writing xSV (eg csv) files from within the Oracle database using Java and PL/SQL
Wednesday October 12 2011 at 17:51
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.
        
|
SpatialDB Advisor
Home
Comment