+ Start a Discussion
IBS AdminIBS Admin 

Colling PL / SQL Procedure via data loader batch processes

Hello together,

for a system ingeration (oracle DB 19.2) with salesforce (LEX)  i have a customer requirement to call a PL/ SQL Procdure via dataloader through a batch process.

I have been searching in google for hours but could not find anything about the right syntax in the batch. 
I also had a look in several dataloader documentations and also on help.salesforce.com and found nothing. 

I appreciate any help.

Thanks in advance
Ilia
OFröhlichOFröhlich
Hi,
it's a long time ago, that I've connected an oracle database with salesforce dataloader. Not sure, if it will help you:

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:@<server>:1521:<instance>"/>
        <property name="username" value="xxx"/>
        <property name="password" value="xxx"/>
    </bean>

    <bean id="queryPersonAccount"
          class="com.salesforce.dataloader.dao.database.DatabaseConfig"
          singleton="true">
        <property name="sqlConfig" ref="querySql"/>
        <property name="dataSource" ref="dbDataSource"/>
    </bean>
    <bean id="querySql"
          class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
        <property name="sqlString">
            <value><![CDATA[
                SELECT w1, w2 FROM personaccount where w1=5]]>
            </value>
        </property>
        <property name="columnNames">
            <list>
                <value>w1</value>
                <value>w2</value>
            </list>
        </property>
    </bean>
</beans>

process-conf.xml
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>

  <bean id="upsertPersonAccount"
        class="com.salesforce.dataloader.process.ProcessRunner"
        singleton="false">
      <description>upsertPersonAccount</description>
      <property name="name" value="upsertPersonAccount"/>
      <property name="configOverrideMap">
          <map>
              <entry key="sfdc.loadBatchSize" value="2500"/>
              <entry key="sfdc.debugMessages" value="false"/>
                            <entry key="sfdc.useBulkApi" value="true"/>
                            <entry key="sfdc.bulkApiSerialMode" value="false"/>
                            <entry key="sfdc.connectionTimeoutSecs" value="60"/>
              <entry key="sfdc.entity" value="Account"/>
              <entry key="sfdc.externalIdField" value="exId"/>
              <entry key="process.operation" value="upsert"/>
              <entry key="process.mappingFile" value="C:\personaccount_map.sdl"/>
              <entry key="process.outputError" value="C:\results\errorUpsert.csv"/>
              <entry key="dataAccess.name" value="queryPersonAccount"/> 
              <entry key="dataAccess.type" value="databaseRead"/>
          </map>
      </property>
  </bean>
</beans> 

If this helps, please mark as Best Answer to help others too.

Oliver
IBS AdminIBS Admin

Hi Oliver,

Thanks but unfortunately my case relates to another direction: from Salesforce to Oracle database. In my case I have the requirement to call a PL/SQL Stored Procedure of the form: "exec storedProcedureName (Var1, Var2)". I have made many attempts, but unfortunately I get an error message: "Error creating PreparedStatement for the database configuration Exec_for_Create_Order_salesforce. Error replacing parameter: sfId with value: 8012p0000091nEhAAI of type: class java.lang.String. Sql error: Invalid column index."

databace-conf.xml
<bean id="ExecConfig_for_Create_Order_salesforce"
      class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            exec proddta.create_order_salesforce_Test(sfId:@sfId@)
        </value>
    </property>
    <property name="sqlParams">
        <map>
            <entry key="sfId" value="java.lang.String"/>
        </map>
    </property>    
        <property name="columnNames">
        <list>
            <value>sfId</value>            
        </list>
    </property>
</bean>

process-config.xml
    <bean id="databaseOrderExtractProcess"
          class="com.salesforce.dataloader.process.ProcessRunner"
          singleton="false">
        <description>databaseOrderExtract job gets Orders info from salesforce and updates or inserts info into database."</description>
        <property name="name" value="databaseOrderExtractProcess"/>
        <property name="configOverrideMap">
            <map>
                <entry key="sfdc.debugMessages" value="true"/>
                <entry key="sfdc.debugMessagesFile" value="C:\Users\berater\dataloader\v46.0.0\sf-db-import\SF_to_ERP_log.txt"/>
                <entry key="sfdc.endpoint" value="https://login.salesforce.com"/>
                <entry key="sfdc.username" value="USERName"/>
                <!-- password specified below is invalid, please generate one using the encrypt.bat utility -->
                <entry key="sfdc.password" value="XXXXXX"/>
                <entry key="process.encryptionKeyFile" value="C:\Users\berater\.dataloader\dataLoader.key"/>
                <entry key="sfdc.timeoutSecs" value="600"/>
                <entry key="sfdc.loadBatchSize" value="100"/>
                <entry key="sfdc.entity" value="Order"/>
                <entry key="sfdc.extractionRequestSize" value="500"/>
                <entry key="sfdc.extractionSOQL" value="Select Id FROM Order LIMIT 1"/>
                <entry key="process.operation" value="extract"/>
                <entry key="process.mappingFile" value="C:\Users\berater\dataloader\v46.0.0\sf-db-import\mapping\OrderExtractToERPMap.sdl"/>
                <entry key="dataAccess.type" value="databaseWrite"/>
                <entry key="dataAccess.name" value="Exec_for_Create_Order_salesforce"/>
                <entry key="process.initialLastRunDate" value="2005-12-01T00:00:00.000-0800"/>
            </map>
        </property>
    </bean>

Any other ideas or hints why it doesn't work?

Thanks in advance
Ilia

OFröhlichOFröhlich
Looks not so bad. Your sdl looks like that:
Id=sfId
IBS AdminIBS Admin
My sdl looks exactly like that: Id=sfId
OFröhlichOFröhlich

and stops after sfID, without a blank at the end?

Have you tried, when delete this section
<property name="columnNames">
<list>
<value>sfId</value>            
</list>
</property>

IBS AdminIBS Admin
I checked SDL and tried delete this section. Unfortunately I get the same error message:  "Error replacing parameter: sfId with value: 8012p0000094eMaAAI of type: class java.lang.String.  Sql error: Ung³ltiger Spaltenindex."
My guess is that it is not possible to call a PL / SQL stored procedure via a batch process (Dataloader)