How to call stored procedure from solr

Problem: If you want to take advantage of solr data import handler to index the data, but unable to find a way to call oracle stored procedure or a function, here you go

Solution:

1. First thing to understand is to fill the index of solr, I use the dataimporthandler of solr

2. DataImportHandler does not directly support calling regular stored procedures

3. Instead, create oracle pipelined functions as shown in my earlier post at

https://javatoj2ee.wordpress.com/2012/08/30/oracle-pipelined-table-functions/

4. Now prepare the data-config.xml file as below

Configuring DataSources

Add the tag ‘dataSource’ directly under the ‘dataConfig’ tag, for example.

<dataSource name="jdbcds" type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:port/dbname" user="db_username" password="db_password" responseBuffering="adaptive" batchSize="0" autoCommit="true"/>

• The datasource configuration can also be done in solr config xml 
  
• The attribute ‘type’ specifies the implementation class. It is optional. The default value is 'JdbcDataSource'
• The attribute ‘name’ can be used if there are multiple datasources used by multiple entities
• All other attributes in the <dataSource> tag are specific to the particular dataSource implementation being configured.

  You might need to download and install the Oracle JDBC Driver in the /lib directory of your Solr installation.

  • The data-config.xml used for this example is:

  <dataConfig>
  <dataSource name="jdbcds" driver="org.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@hostname:port/dbname" user="sa" password="" responseBuffering="adaptive" batchSize="0" autoCommit="true"/>
  
  <document name="products">
   <entity name="item" datasource="jdbcds" pk="id"  query="SELECT * FROM TABLE(PACKAGE_NAME.PIPELINED_FUNC_NAME)" transformer="RegexTransformer,DateFomatTransformer,TemplateTransformer" 
  </entity>
  </document> 
  </dataConfig>