001: /*
002: * OracleControlFileWriter.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.exporter;
013:
014: import java.io.BufferedWriter;
015: import java.io.File;
016: import java.io.FileWriter;
017: import java.io.IOException;
018: import java.io.PrintWriter;
019: import java.util.Iterator;
020: import java.util.LinkedList;
021: import java.util.List;
022: import workbench.log.LogMgr;
023: import workbench.resource.Settings;
024: import workbench.storage.ResultInfo;
025: import workbench.util.CharacterRange;
026: import workbench.util.FileUtil;
027: import workbench.util.SqlUtil;
028: import workbench.util.StringUtil;
029: import workbench.util.WbFile;
030:
031: /**
032: * @author support@sql-workbench.net
033: */
034: public class OracleControlFileWriter implements FormatFileWriter {
035: public OracleControlFileWriter() {
036: }
037:
038: public void writeFormatFile(DataExporter exporter,
039: RowDataConverter converter) {
040: ResultInfo resultInfo = converter.getResultInfo();
041: WbFile baseFile = new WbFile(exporter.getFullOutputFilename());
042: String dir = baseFile.getParent();
043: String baseName = baseFile.getFileName();
044: File ctl = new File(dir, baseName + ".ctl");
045: PrintWriter out = null;
046: try {
047: out = new PrintWriter(new BufferedWriter(
048: new FileWriter(ctl)));
049: if (exporter.getExportHeaders()) {
050: out.println("-- Skip the header row of the input file");
051: out.println("OPTIONS (skip=1)");
052: }
053:
054: if (!exporter.getEncoding().startsWith("UTF")) {
055: out
056: .println("-- The specified character set is an ISO name and will most probably not work");
057: out
058: .println("-- as Oracle uses its own names for character sets (e.g. WE8ISO8859P1 for ISO-8859-1)");
059: }
060: out.print("LOAD DATA CHARACTERSET '");
061: if (exporter.getEncoding().equalsIgnoreCase("UTF-8")) {
062: // Oracle only understand UTF8 not UTF-8
063: out.println("UTF8'");
064: } else {
065: out.println(exporter.getEncoding().toUpperCase() + "'");
066: }
067: File f = new File(exporter.getFullOutputFilename());
068: out.println("INFILE '" + f.getName() + "'");
069: out
070: .println("-- to replace the data in the table use TRUNCATE instead of APPEND");
071: out.println("APPEND");
072: out.print("INTO TABLE ");
073: out.println(resultInfo.getUpdateTable().getTableName());
074: out.print("FIELDS TERMINATED BY '");
075: out.print(StringUtil.escapeUnicode(exporter
076: .getTextDelimiter(), CharacterRange.RANGE_CONTROL));
077: out.println("' TRAILING NULLCOLS");
078: out.println("(");
079: int count = resultInfo.getColumnCount();
080: int max = 0;
081: // calculate max. column name length for proper formatting
082: for (int i = 0; i < count; i++) {
083: int l = resultInfo.getColumnName(i).length();
084: if (l > max) {
085: max = l;
086: }
087: }
088: max++;
089:
090: String format = exporter.getTimestampFormat();
091: if (format == null) {
092: format = Settings.getInstance()
093: .getDefaultTimestampFormat();
094: //if (format == null) format = exporter.getDateFormat();
095: }
096: String oraFormat = convertJavaDateFormatToOracle(format);
097: List<String> blobColumns = new LinkedList<String>();
098: boolean clobAsFile = exporter.getWriteClobAsFile();
099: for (int i = 0; i < count; i++) {
100: String col = resultInfo.getColumnName(i);
101: int type = resultInfo.getColumnType(i);
102: out.print(" ");
103: if (SqlUtil.isBlobType(type)
104: || (clobAsFile && SqlUtil.isClobType(type,
105: exporter.getConnection()
106: .getDbSettings()))) {
107: blobColumns.add(col);
108: out.print("lob_file_" + col.toLowerCase()
109: + " FILLER");
110: } else {
111: out.print(col);
112: }
113:
114: if (SqlUtil.isDateType(type)) {
115: for (int k = col.length(); k < max; k++) {
116: out.print(" ");
117: }
118: out.print("DATE");
119: if (format != null) {
120: out.print(" \"");
121: out.print(oraFormat);
122: out.print("\"");
123: }
124: }
125: if (i < count - 1 || blobColumns.size() > 0) {
126: out.print(",");
127: }
128: out.println();
129: }
130:
131: if (blobColumns.size() > 0) {
132: Iterator<String> itr = blobColumns.iterator();
133: while (itr.hasNext()) {
134: String col = itr.next();
135: out.print(" ");
136: out.print(col);
137: out.print(" LOBFILE(lob_file_" + col.toLowerCase()
138: + ") TERMINATED BY EOF");
139: if (itr.hasNext()) {
140: out.print(",");
141: }
142: out.println();
143: }
144: }
145: out.print(")");
146: } catch (IOException io) {
147: LogMgr.logError(
148: "OracleControlFileWriter.writeFormatFile()",
149: "Error opening outputfile", io);
150: } finally {
151: FileUtil.closeQuitely(out);
152: }
153: }
154:
155: private String convertJavaDateFormatToOracle(String format) {
156: String result = format.replaceAll("HH", "HH24");
157: result = result.replaceAll("hh", "HH12");
158: result = result.replaceAll("mm", "MI");
159: result = result.replaceAll("yy", "YY");
160: result = result.replaceAll("dd", "DD");
161: result = result.replaceAll("ss", "SS");
162: return result;
163: }
164: }
|