Announcement Announcement Module
Collapse
No announcement yet.
JdbcTemplate with StoredProcedure returning cursor Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JdbcTemplate with StoredProcedure returning cursor

    Hello,

    I am facing an issue while trying to execute a stored procedure (in Oracle 10) using jdbctemplate. The procedure "GETLINKS" is returning a resultset back as a cursor.
    Code:
    create or replace PROCEDURE getlink (
          p_link_id   IN       VARCHAR2,
          p_links OUT SYS_REFCURSOR
       )
      AS
       BEGIN
          OPEN p_links FOR
             SELECT *
               FROM LINK l where l.link_guid=p_link_id;
      END;
    The java/dao layer code which I have written to execute this procedure is

    Code:
        @Autowired
        @Qualifier("unityJdbcTemplate")
        JdbcTemplate unityJdbcTemplate;
    	
    
    	public LinkGroup getLinksByGroup(String linkGroupId) {
    		LinksByGroup hinksByGroup = new LinksByGroup(unityJdbcTemplate.getDataSource());  
    		    Map<String, Object> inParams = new HashMap<String, Object>();  
    		    inParams.put("p_link_id", linkGroupId); 
    		     Map results = hinksByGroup.execute(inParams);
    		     return new LinkGroup((Collection<Link>)results.get("p_links")); 
    		
    		    
    	}
    
    
    	protected class LinksByGroup extends StoredProcedure  
    	{  
    	    public LinksByGroup(DataSource ds)  
    	    {  
    	        super(unityJdbcTemplate, "GETLINK");  
    	        declareParameter(new SqlOutParameter("p_links", OracleTypes.CURSOR,new LinkTypeMapper()));  
    	        declareParameter(new SqlParameter("p_link_id",Types.VARCHAR));  
    	        compile();  
    	    }  
    	}  
    	private class LinkTypeMapper implements RowMapper  
    	{  
    	    public Object mapRow(ResultSet rs, int rowNum) throws SQLException  
    	    {  
    	    	Link link = new Link();
    	    	link.setLinkId(rs.getString("link_id"));
    	    	link.setLinkURL(rs.getString("link_url"));
    	    	link.setIconURL(rs.getString("icon_url"));
    	    	link.setTitle(rs.getString("title"));
    	    	return link;  
    	    }  
    	}
    I am using spring 3.0.5 release. The dependencies in POM are

    Code:
    <dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-context</artifactId>
    			<version>3.0.5.RELEASE</version>
    			<scope>compile</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-web</artifactId>
    			<version>3.0.5.RELEASE</version>
    			<scope>compile</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-jdbc</artifactId>
    			<version>3.0.5.RELEASE</version>
    			<scope>compile</scope>
    		</dependency>
                    <dependency>
    			<groupId>oracle</groupId>
    			<artifactId>oracle-jdbc</artifactId>
    			<version>10g</version>
    		</dependency>
    When I run the application I get this error

    Code:
    Caused by: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GETLINK(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'GETLINK'
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'GETLINK'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
    	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
    	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)
    	at qcom.unity.unityservice.dao.UnityDaoImpl.getLinksByGroup(UnityDaoImpl.java:42)
    	at qcom.unity.unityservice.service.impl.LinkServiceImpl.getLinksByGroup(LinkServiceImpl.java:34)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	at java.lang.reflect.Method.invoke(Method.java:597)
    	at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:173)
    	at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:89)
    	... 30 more
    Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
    The procedure call works fine for a normal procedure which doesn't return a result set.

    Has anyone faced this before or can guide me in identifying the issue.Please help.

    Thanks.

  • #2
    For some reason StoredProcedure class has got some issues while accessing ref_cursors. Was able to get this resolved by using SimpleJDBCCall instead.
    Code:
    SimpleJdbcCall caller = new SimpleJdbcCall(
    				unityJdbcTemplate.getDataSource());
    		caller.withProcedureName("GETLINK")
    				.declareParameters(
    						new SqlOutParameter("p_links",
    								OracleTypes.CURSOR, new LinkTypeMapper()),
    						new SqlParameter("p_link_id", Types.VARCHAR));
    		MapSqlParameterSource params = new MapSqlParameterSource().addValue(
    				"p_link_id", linkGroupId);
    		Map<String,Object>  results = caller.execute(params);
    		return new LinkGroup((Collection<Link>) results.get("p_links"),linkGroupId);
    Hopefully someone would find this helpful.

    Thanks

    Comment

    Working...
    X