+ Start a Discussion
Danny IncompanyDanny 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
<!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 = Fecha
MAPPING "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__c
PROCESS-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!
James LoghryJames Loghry
First off, it looks like you have an invalid log-conf.xml file that is throwing the first error.  I would take a quick a look at that and see if there are any open tags or invalid XML that are causing that error to display.

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.
buyan thyagarajanbuyan thyagarajan
Hi Danny,
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