public void open(VariableSpace space, Process sqlldrProcess) throws KettleException { String loadMethod = meta.getLoadMethod(); try { OutputStream os = null; if (OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(loadMethod)) { os = sqlldrProcess.getOutputStream(); } else { // Else open the data file filled in. String dataFile = meta.getDataFile(); dataFile = space.environmentSubstitute(dataFile); os = new FileOutputStream(dataFile, false); } String encoding = meta.getEncoding(); if (Const.isEmpty(encoding)) { // Use the default encoding. output = new BufferedWriter(new OutputStreamWriter(os)); } else { // Use the specified encoding output = new BufferedWriter(new OutputStreamWriter(os, encoding)); } } catch (IOException e) { throw new KettleException("IO exception occured: " + e.getMessage(), e); } }
/** * Create the command line for an sqlldr process depending on the meta information supplied. * * @param meta The meta data to create the command line from * @param password Use the real password or not * @return The string to execute. * @throws KettleException Upon any exception */ public String createCommandLine(OraBulkLoaderMeta meta, boolean password) throws KettleException { StringBuilder sb = new StringBuilder(300); if (meta.getSqlldr() != null) { try { FileObject fileObject = KettleVFS.getFileObject(environmentSubstitute(meta.getSqlldr()), getTransMeta()); String sqlldr = KettleVFS.getFilename(fileObject); sb.append(sqlldr); } catch (KettleFileException ex) { throw new KettleException("Error retrieving sqlldr string", ex); } } else { throw new KettleException("No sqlldr application specified"); } if (meta.getControlFile() != null) { try { FileObject fileObject = KettleVFS.getFileObject(environmentSubstitute(meta.getControlFile()), getTransMeta()); sb.append(" control=\'"); sb.append(KettleVFS.getFilename(fileObject)); sb.append("\'"); } catch (KettleFileException ex) { throw new KettleException("Error retrieving controlfile string", ex); } } else { throw new KettleException("No control file specified"); } if (OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(meta.getLoadMethod())) { sb.append(" data=\'-\'"); } if (meta.getLogFile() != null) { try { FileObject fileObject = KettleVFS.getFileObject(environmentSubstitute(meta.getLogFile()), getTransMeta()); sb.append(" log=\'"); sb.append(KettleVFS.getFilename(fileObject)); sb.append("\'"); } catch (KettleFileException ex) { throw new KettleException("Error retrieving logfile string", ex); } } if (meta.getBadFile() != null) { try { FileObject fileObject = KettleVFS.getFileObject(environmentSubstitute(meta.getBadFile()), getTransMeta()); sb.append(" bad=\'"); sb.append(KettleVFS.getFilename(fileObject)); sb.append("\'"); } catch (KettleFileException ex) { throw new KettleException("Error retrieving badfile string", ex); } } if (meta.getDiscardFile() != null) { try { FileObject fileObject = KettleVFS.getFileObject(environmentSubstitute(meta.getDiscardFile()), getTransMeta()); sb.append(" discard=\'"); sb.append(KettleVFS.getFilename(fileObject)); sb.append("\'"); } catch (KettleFileException ex) { throw new KettleException("Error retrieving discardfile string", ex); } } DatabaseMeta dm = meta.getDatabaseMeta(); if (dm != null) { String user = Const.NVL(dm.getUsername(), ""); String pass = Const.NVL( Encr.decryptPasswordOptionallyEncrypted(environmentSubstitute(dm.getPassword())), ""); if (!password) { pass = "******"; } String dns = Const.NVL(dm.getDatabaseName(), ""); sb.append(" userid=") .append(environmentSubstitute(user)) .append("/") .append(environmentSubstitute(pass)) .append("@"); String overrideName = meta.getDbNameOverride(); if (Utils.isEmpty(Const.rtrim(overrideName))) { sb.append(environmentSubstitute(dns)); } else { // if the database name override is filled in, do that one. sb.append(environmentSubstitute(overrideName)); } } else { throw new KettleException("No connection specified"); } if (meta.isDirectPath()) { sb.append(" DIRECT=TRUE"); if (getStepMeta().getCopies() > 1 || meta.isParallel()) { sb.append(" PARALLEL=TRUE"); } } return sb.toString(); }
public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException { meta = (OraBulkLoaderMeta) smi; data = (OraBulkLoaderData) sdi; try { Object[] r = getRow(); // Get row from input rowset & set row busy! if (r == null) { // no more input to be expected... setOutputDone(); if (!preview) { if (output != null) { // Close the output try { output.close(); } catch (IOException e) { throw new KettleException("Error while closing output", e); } output = null; } String loadMethod = meta.getLoadMethod(); if (OraBulkLoaderMeta.METHOD_AUTO_END.equals(loadMethod)) { // if this is the first line, we do not need to execute loader // control file may not exists if (!first) { execute(meta, true); sqlldrProcess = null; } } else if (OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(meta.getLoadMethod())) { try { if (sqlldrProcess != null) { int exitVal = sqlldrProcess.waitFor(); sqlldrProcess = null; logBasic( BaseMessages.getString(PKG, "OraBulkLoader.Log.ExitValueSqlldr", "" + exitVal)); checkExitVal(exitVal); } else if (!first) { throw new KettleException("Internal error: no sqlldr process running"); } } catch (Exception ex) { throw new KettleException("Error while executing sqlldr", ex); } } } return false; } if (!preview) { if (first) { first = false; String recTerm = Const.CR; if (!Utils.isEmpty(meta.getAltRecordTerm())) { recTerm = substituteRecordTerminator(meta.getAltRecordTerm()); } createControlFile(environmentSubstitute(meta.getControlFile()), r, meta); output = new OraBulkDataOutput(meta, recTerm); if (OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(meta.getLoadMethod())) { execute(meta, false); } output.open(this, sqlldrProcess); } output.writeLine(getInputRowMeta(), r); } putRow(getInputRowMeta(), r); incrementLinesOutput(); } catch (KettleException e) { logError(BaseMessages.getString(PKG, "OraBulkLoader.Log.ErrorInStep") + e.getMessage()); setErrors(1); stopAll(); setOutputDone(); // signal end to receiver(s) return false; } return true; }
/** * Get the contents of the control file as specified in the meta object * * @param meta the meta object to model the control file after * @return a string containing the control file contents */ public String getControlFileContents(OraBulkLoaderMeta meta, RowMetaInterface rm, Object[] r) throws KettleException { DatabaseMeta dm = meta.getDatabaseMeta(); String inputName = "'" + environmentSubstitute(meta.getDataFile()) + "'"; String loadAction = meta.getLoadAction(); StringBuilder contents = new StringBuilder(500); contents.append("OPTIONS(").append(Const.CR); contents.append(" ERRORS=\'").append(meta.getMaxErrors()).append("\'").append(Const.CR); if (meta.getCommitSizeAsInt(this) != 0 && !(meta.isDirectPath() && getStepMeta().getCopies() > 1)) { // For the second part of the above expressions: ROWS is not supported // in parallel mode (by sqlldr). contents.append(" , ROWS=\'").append(meta.getCommitSize()).append("\'").append(Const.CR); } if (meta.getBindSizeAsInt(this) != 0) { contents.append(" , BINDSIZE=\'").append(meta.getBindSize()).append("\'").append(Const.CR); } if (meta.getReadSizeAsInt(this) != 0) { contents.append(" , READSIZE=\'").append(meta.getReadSize()).append("\'").append(Const.CR); } contents.append(")").append(Const.CR); contents.append("LOAD DATA").append(Const.CR); if (!Utils.isEmpty(meta.getCharacterSetName())) { contents.append("CHARACTERSET ").append(meta.getCharacterSetName()).append(Const.CR); } if (!OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(meta.getLoadMethod()) || !Utils.isEmpty(meta.getAltRecordTerm())) { String infile = inputName; if (OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(meta.getLoadMethod())) { infile = "''"; } // For concurrent input, data command line argument must be specified contents.append("INFILE ").append(infile); if (!Utils.isEmpty(meta.getAltRecordTerm())) { contents .append(" \"STR x'") .append(encodeRecordTerminator(meta.getAltRecordTerm(), meta.getEncoding())) .append("'\""); } contents.append(Const.CR); } contents .append("INTO TABLE ") .append( dm.getQuotedSchemaTableCombination( environmentSubstitute(meta.getSchemaName()), environmentSubstitute(meta.getTableName()))) .append(Const.CR) .append(loadAction) .append(Const.CR) .append("FIELDS TERMINATED BY ',' ENCLOSED BY '\"'") .append(Const.CR) .append("("); String[] streamFields = meta.getFieldStream(); String[] tableFields = meta.getFieldTable(); String[] dateMask = meta.getDateMask(); if (streamFields == null || streamFields.length == 0) { throw new KettleException("No fields defined to load to database"); } for (int i = 0; i < streamFields.length; i++) { if (i != 0) { contents.append(", ").append(Const.CR); } contents.append(dm.quoteField(tableFields[i])); int pos = rm.indexOfValue(streamFields[i]); if (pos < 0) { throw new KettleException("Could not find field " + streamFields[i] + " in stream"); } ValueMetaInterface v = rm.getValueMeta(pos); switch (v.getType()) { case ValueMetaInterface.TYPE_STRING: if (v.getLength() > 255) { contents.append(" CHAR(").append(v.getLength()).append(")"); } else { contents.append(" CHAR"); } break; case ValueMetaInterface.TYPE_INTEGER: case ValueMetaInterface.TYPE_NUMBER: case ValueMetaInterface.TYPE_BIGNUMBER: break; case ValueMetaInterface.TYPE_DATE: if (OraBulkLoaderMeta.DATE_MASK_DATE.equals(dateMask[i])) { contents.append(" DATE 'yyyy-mm-dd'"); } else if (OraBulkLoaderMeta.DATE_MASK_DATETIME.equals(dateMask[i])) { contents.append(" TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ff'"); } else { // If not specified the default is date. contents.append(" DATE 'yyyy-mm-dd'"); } break; case ValueMetaInterface.TYPE_BINARY: contents.append(" ENCLOSED BY '<startlob>' AND '<endlob>'"); break; case ValueMetaInterface.TYPE_TIMESTAMP: contents.append(" TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ff'"); break; default: break; } } contents.append(")"); return contents.toString(); }