You need to sign in to do that
Don't have an account?
Danny Incompany
Salesforce to Oracle Issue
Hi,
We are integrating salesforce to oracle 10g using SOAP dataloader. It gives an error. Not sure what may be wrong. Here are the files and information we have, any help will be gretaly apreciated:
DATABASE-CONF.XML
Any help I invite a beer!! thanks!
We are integrating salesforce to oracle 10g using SOAP dataloader. It gives an error. Not sure what may be wrong. Here are the files and information we have, any help will be gretaly apreciated:
DATABASE-CONF.XML
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="dbDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/> <property name="username" value="naf6"/> <property name="password" value="naf6"/> </bean> <bean id="queryAsiento" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true"> <property name="sqlConfig" ref="queryAsientoSql"/> <property name="dataSource" ref="dbDataSource"/> </bean> <bean id="insertAsiento" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true"> <property name="sqlConfig" ref="insertAccountSql"/> <property name="dataSource" ref="dbDataSource"/> </bean> <bean id="insertAccountSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true"> <property name="sqlString"> <value> INSERT INTO SF_ARCGAE ( ANO, MES, NO_CUOTA , NO_OPERACION , DESCRI1, COD_DIARIO,T_DEBITOS,T_CREDITOS, NO_CIA, NO_ASIENTO,FECHA, AUTORIZADO, ORIGEN,T_CAMB_C_V,NO_COMPROBANTE,NO_PRODUCTO,ID_CLIENTE,NOM_CLIENTE) VALUES (@ANO@, @MESES@, @NOCUOTA@, @NO_OPERACION@, @DESCRI1@, 'CONTA', @CREDITOS@, @DEBITOS@, '10', @DIARIO@,@Fecha@,'N', 'SF','V',0,'Test','9999','Cesar') </value> </property> <property name="sqlParams"> <map> <entry key="ANO" value="java.lang.String"/> <entry key="MESES" value="java.lang.Double"/> <entry key="NOCUOTA" value="java.lang.String"/> <entry key="NO_OPERACION" value="java.lang.String"/> <entry key="DESCRI1" value="java.lang.String"/> <entry key="DIARIO" value="java.lang.String"/> <entry key="CREDITOS" value="java.lang.Double"/> <entry key="DEBITOS" value="java.lang.Double"/> <entry key="Fecha" value="java.lang.String"/> </map> </property> </bean> <bean id="insertLinea" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true"> <property name="sqlConfig" ref="insertLineaSql"/> <property name="dataSource" ref="dbDataSource"/> </bean> <bean id="insertLineaSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true"> <property name="sqlString"> <value> INSERT INTO SF_ARCGAL ( NO_CIA , ANO, MES , NO_ASIENTO , NO_LINEA, CUENTA,DESCRI,COD_DIARIO,MONEDA,MONTO,TIPO) VALUES ('10', @ANO@, @MES@, @NO_ASIENTO@, @NO_LINEA@, @CUENTA@, @DESCRI@, 'CONTA', 'C',@MONTO@,@TIPO@) </value> </property> <property name="sqlParams"> <map> <entry key="ANO" value="java.lang.String"/> <entry key="MES" value="java.lang.integer"/> <entry key="NO_ASIENTO" value="java.lang.String"/> <entry key="NO_LINEA" value="java.lang.String"/> <entry key="CUENTA" value="java.lang.String"/> <entry key="DESCRI" value="java.lang.String"/> <entry key="MONTO" value="java.lang.Double"/> <entry key="TIPO" value="java.lang.String"/> </map> </property> </bean> </beans>MAPPING "ASIENTOS"
Credito__c = NO_OPERACION Year__c = ANO Pago__c = NOCUOTA Descripcion__c = DESCRI1 Month__c = MESES Name = DIARIO TotalCreditos__c = DEBITOS TotalDebitos__c = CREDITOS CreatedDate = FechaMAPPING "LINEAS"
ANO = Ano__c MES = Mes__c NO_ASIENTO = Asiento_FK__c NO_LINEA = Name CUENTA = CodigoCuentasContables__c DESCRI = CuentasContables__c MONTO = Monto__c TIPO = Tipo_Oracle__cPROCESS-CONF.XML
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="databaseAccountExtractProcess" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false"> <description>DatabaseAccountExtract job gets Asiento info from salesforce and updates or inserts info into database."</description> <property name="name" value="databaseAccountExtract"/> <property name="configOverrideMap"> <map> <entry key="sfdc.debugMessages" value="false"/> <entry key="sfdc.debugMessagesFile" value="C:\ProgramFiles (x86)\salesforce.com\Data Loader\Configuracion Funde\ProductoSoapTrace.log"/> <entry key="sfdc.endpoint" value="https://login.salesforce.com"/> <entry key="sfdc.username" value="salesforce@email.com"/> <!-- password specified below is invalid, please generate one using the encrypt.bat utility --> <entry key="sfdc.password" value="theencryptedpassword"/> <entry key="sfdc.timeoutSecs" value="600"/> <entry key="sfdc.loadBatchSize" value="200"/> <entry key="sfdc.entity" value="Asiento__c"/> <entry key="sfdc.extractionRequestSize" value="200"/> <entry key="sfdc.extractionSOQL" value="select Credito__c,Year__c,Pago__c,Descripcion__c,Month__c,Name,TotalCreditos__c,TotalDebitos__c, CreatedDate from Asiento__c limit 1"/> <entry key="process.operation" value="extract"/> <entry key="process.mappingFile" value="C:\Program Files (x86)\salesforce.com\Data Loader\Configuracion Funde\conf\AsientoMap.sdl"/> <entry key="dataAccess.type" value="databaseWrite"/> <entry key="dataAccess.name" value="insertAsiento"/> </map> </property> </bean> <bean id="LineasExtractProcess" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false"> <description>DatabaseAccountExtract job gets Asiento info from salesforce and updates or inserts info into database."</description> <property name="name" value="databaseAccountExtract"/> <property name="configOverrideMap"> <map> <entry key="sfdc.debugMessages" value="false"/> <entry key="sfdc.debugMessagesFile" value="C:\ProgramFiles (x86)\salesforce.com\Data Loader\Configuracion Funde\ProductoSoapTrace.log"/> <entry key="sfdc.endpoint" value="https://login.salesforce.com"/> <entry key="sfdc.username" value="salesforce@emial.com"/> <!-- password specified below is invalid, please generate one using the encrypt.bat utility --> <entry key="sfdc.password" value="THEENCRYPTEDPASSWORD"/> <entry key="sfdc.timeoutSecs" value="600"/> <entry key="sfdc.loadBatchSize" value="200"/> <entry key="sfdc.entity" value="LineaAsiento__c"/> <entry key="sfdc.extractionRequestSize" value="500"/> <entry key="sfdc.extractionSOQL" value="select Ano__c,Mes__c,Asiento_FK__c,Name,CodigoCuentasContables__c,CuentasContables__c,Monto__c,Tipo_Oracle__c from LineaAsiento__c"/> <entry key="process.operation" value="extract"/> <entry key="process.mappingFile" value="C:\Program Files (x86)\salesforce.com\Data Loader\Configuracion Funde\conf\LineasMap.sdl"/> <entry key="dataAccess.type" value="databaseWrite"/> <entry key="dataAccess.name" value="insertLinea"/> </map> </property> </bean> </beans>THIS IS THE DEBUG
C:\Program Files (x86)\salesforce.com\Data Loader\Configuracion Funde>call "C:\P rogram Files (x86)\salesforce.com\Data Loader\bin\process.bat" "C:\Program Files (x86)\salesforce.com\Data Loader\Configuracion Funde\conf" databaseAccountExtra ctProcess 2015-11-30 14:36:06,986 INFO [main] controller.Controller initLog (Controller.j ava:382) - Reading log-conf.xml in C:\Program Files (x86)\salesforce.com\Data Lo ader\Configuracion Funde\log-conf.xml log4j:WARN Fatal parsing error 1 and column 1 log4j:WARN Premature end of file. log4j:ERROR Could not parse file [C:\Program Files (x86)\salesforce.com\Data Loa der\Configuracion Funde\log-conf.xml]. org.xml.sax.SAXParseException: Premature end of file. at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(Unknown So urce) at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(Unk nown Source) at javax.xml.parsers.DocumentBuilder.parse(Unknown Source) at org.apache.log4j.xml.DOMConfigurator$1.parse(DOMConfigurator.java:749 ) at org.apache.log4j.xml.DOMConfigurator.doConfigure(DOMConfigurator.java :866) at org.apache.log4j.xml.DOMConfigurator.doConfigure(DOMConfigurator.java :755) at org.apache.log4j.xml.DOMConfigurator.configure(DOMConfigurator.java:8 91) at com.salesforce.dataloader.controller.Controller.initLog(Controller.ja va:384) at com.salesforce.dataloader.process.ProcessRunner.ensureLogging(Process Runner.java:173) at com.salesforce.dataloader.process.ProcessRunner.getInstance(ProcessRu nner.java:266) at com.salesforce.dataloader.process.ProcessRunner.main(ProcessRunner.ja va:246) 2015-11-30 14:36:07,095 INFO [main] controller.Controller initLog (Controller.j ava:391) - The log has been initialized 2015-11-30 14:36:07,126 INFO [main] process.ProcessConfig getBeanFactory (Proce ssConfig.java:104) - Loading process configuration from config file: C:\Program Files (x86)\salesforce.com\Data Loader\Configuracion Funde\conf\process-conf.xml 2015-11-30 14:36:07,236 INFO [main] support.AbstractApplicationContext prepareR efresh (AbstractApplicationContext.java:495) - Refreshing org.springframework.co ntext.support.FileSystemXmlApplicationContext@1ebd75b: startup date [Mon Nov 30 14:36:07 CST 2015]; root of context hierarchy 2015-11-30 14:36:07,329 INFO [main] xml.XmlBeanDefinitionReader loadBeanDefinit ions (XmlBeanDefinitionReader.java:315) - Loading XML bean definitions from file [C:\Program Files (x86)\salesforce.com\Data Loader\Configuracion Funde\conf\pro cess-conf.xml] 2015-11-30 14:36:07,407 INFO [main] support.DefaultListableBeanFactory preInsta ntiateSingletons (DefaultListableBeanFactory.java:557) - Pre-instantiating singl etons in org.springframework.beans.factory.support.DefaultListableBeanFactory@e6 4686: defining beans [databaseAccountExtractProcess,LineasExtractProcess]; root of factory hierarchy 2015-11-30 14:36:07,501 INFO [databaseAccountExtract] controller.Controller ini tConfig (Controller.java:327) - config dir created at C:\Program Files (x86)\sal esforce.com\Data Loader\Configuracion Funde\conf 2015-11-30 14:36:07,532 INFO [databaseAccountExtract] controller.Controller ini tConfig (Controller.java:355) - The controller config has been initialized 2015-11-30 14:36:07,548 INFO [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:116) - Initializing process engine 2015-11-30 14:36:07,579 INFO [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:119) - Loading parameters 2015-11-30 14:36:08,203 INFO [databaseAccountExtract] config.LastRun load (Last Run.java:96) - Last run info will be saved in file: C:\Program Files (x86)\sales force.com\Data Loader\Configuracion Funde\conf\databaseAccountExtract_lastRun.pr operties 2015-11-30 14:36:08,250 INFO [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:127) - Logging in to: https://login.salesforce.com 2015-11-30 14:36:08,265 INFO [databaseAccountExtract] client.PartnerClient logi n (PartnerClient.java:478) - Beginning Partner Salesforce login .... 2015-11-30 14:36:08,312 INFO [databaseAccountExtract] client.PartnerClient logi nInternal (PartnerClient.java:519) - Salesforce login to https://login.salesforc e.com/services/Soap/u/35.0 as user mfeoli@fundecooperacion.org 2015-11-30 14:36:09,685 INFO [databaseAccountExtract] dao.DataAccessObjectFacto ry getDaoInstance (DataAccessObjectFactory.java:51) - Instantiating data access object: insertAsiento of type: databaseWrite 2015-11-30 14:36:09,716 INFO [databaseAccountExtract] xml.XmlBeanDefinitionRead er loadBeanDefinitions (XmlBeanDefinitionReader.java:315) - Loading XML bean def initions from file [C:\Program Files (x86)\salesforce.com\Data Loader\Configurac ion Funde\conf\database-conf.xml] 2015-11-30 14:36:09,810 INFO [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:132) - Checking the data access object connection 2015-11-30 14:36:09,981 INFO [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:137) - Setting field types 2015-11-30 14:36:12,040 INFO [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:141) - Setting object reference types 2015-11-30 14:36:16,627 INFO [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:145) - Creating Map 2015-11-30 14:36:16,658 INFO [databaseAccountExtract] action.OperationInfo inst antiateAction (OperationInfo.java:95) - Instantiating action for operation: extr act 2015-11-30 14:36:16,689 INFO [databaseAccountExtract] controller.Controller exe cuteAction (Controller.java:120) - executing operation: extract 2015-11-30 14:36:16,705 INFO [databaseAccountExtract] action.AbstractAction exe cute (AbstractAction.java:120) - Loading: extract 2015-11-30 14:36:17,812 ERROR [databaseAccountExtract] database.DatabaseWriter w riteRowList (DatabaseWriter.java:211) - Database error encountered while writing row #1 through row #2 (execute batch update). Database configuration: insertAsi ento. Sql error: ORA-01861: literal does not match format string . java.sql.SQLException: ORA-01861: literal does not match format string at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:753) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement. java:218) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedSta tement.java:971) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme nt.java:1191) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep aredStatement.java:3414) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar edStatement.java:3498) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del egatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del egatingPreparedStatement.java:105) at com.salesforce.dataloader.dao.database.DatabaseWriter.writeRowList(Da tabaseWriter.java:177) at com.salesforce.dataloader.action.visitor.AbstractQueryVisitor.writeBa tch(AbstractQueryVisitor.java:130) at com.salesforce.dataloader.action.visitor.AbstractQueryVisitor.flushRe sults(AbstractQueryVisitor.java:123) at com.salesforce.dataloader.action.visitor.AbstractQueryVisitor.visit(A bstractQueryVisitor.java:84) at com.salesforce.dataloader.action.AbstractExtractAction.visit(Abstract ExtractAction.java:57) at com.salesforce.dataloader.action.AbstractAction.execute(AbstractActio n.java:129) at com.salesforce.dataloader.controller.Controller.executeAction(Control ler.java:121) at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.jav a:149) at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.jav a:100) at com.salesforce.dataloader.process.ProcessRunner.main(ProcessRunner.ja va:253) 2015-11-30 14:36:18,218 INFO [databaseAccountExtract] progress.NihilistProgress Adapter doneSuccess (NihilistProgressAdapter.java:63) - The operation has fully completed. There were 0 successful extractions and 1 errors. Press any key to continue . . .
Any help I invite a beer!! thanks!
For the second error, it appears like you're trying to insert a String, but the format is incorrect. If you have an idea of which String is throwing the error, that should help you narrow down where the error is happening. You could likely do this by updating your Oracle integration to remove the fields 1 by 1 and seeing if the error goes away.
I suspect the error is being caused by a Date or Datetime field, or a "String-like" field that isn't quite a string, and that you're trying to write into Oracle. You'll need to map these to a string value somehow before you insert them into Oracle. One easy way to do this is using a text formula field in Salesforce and returns the formatted date, datetime, or other field.
James is right. Is your batch job fixed? How is the data currently integrated with salesforce? Is it an automatic batch job which runs at daily intervals or a web application which posts data to salesforce?
Buyan