Interview Questions

Spring - Execute Stored Procedure

In this article let’s discuss about how to execute stored procedures using Spring framework.

Please refer article on Spring JDBC, to know about performing JDBC operations (SQL) using Spring.

CREATE OR REPLACE PROCEDURE get_col_values(emp_names out varchar2)
   v_empnames   VARCHAR2 (4000);

   CURSOR c_emp_names
      SELECT DISTINCT emp_name FROM employees;
   FOR i IN c_emp_names
      v_empnames := v_empnames || TRIM (i.emp_name) || ',';

   v_empnames := RTRIM (v_empnames, ', ');
   emp_names := v_empnames;

Consider the above stored procedure. It returns the employee names as comma separated values. Let’s see how to call this procedure using Spring JDBC.

First create a class which extends from StoredProcedure Spring api.

public class ProcedureAndFunctions extends StoredProcedure
        public ProcedureAndFunctions()

        public Map execute()
                Map inputs = new HashMap();
                return super.execute(inputs);

Create a DAO class to access the stored procedure.


import java.sql.Types;
import java.util.Iterator;
import java.util.Map;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import in.techdive.spring.dao.ProcedureAndFunctions;

public class EmpDaoJdbc extends SimpleJdbcDaoSupport
        public String getAttrNamesProc()
                String st = "";
                ProcedureAndFunctions stproc = new ProcedureAndFunctions();

                // uncomment the below line if you going to call sql function.
                // stproc.setFunction(true);

                stproc.declareParameter(new SqlOutParameter(st, Types.VARCHAR));
                Map results = stproc.execute();

                for (Iterator it = results.keySet().iterator(); it.hasNext();)
                return st;

Here is the Spring application context file for your reference.


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
   <property name="url" value="<jdbc-url>"/>
   <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
   <property name="username" value="<userName>"/>
   <property name="password" value="<passWord>"/>


<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource"/>

<bean id="EmpDaoJdbc" class="in.techdive.spring.dao.impl.EmpDaoJdbc">
        <property name="jdbcTemplate" ref="jdbcTemplate" />


Finally we can test our code using the following class

import org.springframework.context.ApplicationContext;
import in.techdive.spring.dao.impl.EmpDaoJdbc;

public class SpringJdbcTest
    public static void main(String[] args)
        ApplicationContext ctx = new FileSystemXmlApplicationContext(
                new String [] {"classpath*:spring-stProcedure.xml"});
        EmpDaoJdbc empDaoJdbc = (EmpDaoJdbc)ctx.getBean("EmpDaoJdbc");

In this way you can call stored procedure/ function using Spring JDBC.