Apex Code Development You need to sign in to do that
Don't have an account?
IBS 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
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
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
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
Id=sfId
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>
My guess is that it is not possible to call a PL / SQL stored procedure via a batch process (Dataloader)