Announcement Announcement Module
Collapse
No announcement yet.
SimplJdbcCall Not Working on Procedures with One Input Parameter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SimplJdbcCall Not Working on Procedures with One Input Parameter

    I am testing on an oracle 11gR2 (11.2.0.3) database with the ojdbc6-11.2.0.3 jdbc driver, and the application server is apache-tomcat.
    I have oracle procedures that have one cursor output parameter and several input parameters, and they all work fine with the following SimpleJdbcCall:

    Code:
    dbSizeChartModelList = new SimpleJdbcCall(
    					datasourceDao.getJdbcTemplate("TESTDB"))
    					.withSchemaName("webapp")
    					.withProcedureName("pr_usedtssizesma15")
    					.returningResultSet("cur_out", new TsSizeRowMapper())
    					.executeObject(List.class, selectedTs);
    But an oracle procedure with one input parameter is not running with the following SimpleJdbcCall:

    Code:
    new SimpleJdbcCall(datasourceDao.getJdbcTemplate("TESTDB")).withCatalogName("webapp")
    					.withProcedureName("pr_dynamicquerygrant")
    					.execute("testuser");
    When I look at the debug output of spring-jdbc library, it seems that the metadata processor is not able to process the procedure metadata information returned by the datasource. So, the SimpleJdbcCall object compiles the procedure (ie, pr_dynamicquerygrant) with zero (0) input parameters.

    When I look at the metadata information provided with the datasource, I can see that the procedure has one input parameter. The code and the result are as follows:

    Code:
    ResultSet rs = datasourceDao
    					.getJdbcTemplate("TESTDB")
    					.getDataSource()
    					.getConnection()
    					.getMetaData()
    					.getProcedureColumns(null, "WEBAPP",
    							"PR_DYNAMICQUERYGRANT", "P_DB_USER");
    
    System.out Result: 
    ------------------
      PROCEDURE_NAME = PR_DYNAMICQUERYGRANT 
      COLUMN_NAME = P_DB_USER
      COLUMN_TYPE = 1
      TYPE_NAME = VARCHAR2
    Can someone explain me what is wrong with the previous SimpleJdbcCall usage?
    Thanks in advance.

    P.S. : SimpleJdbcCall to the previous procedure runs correctly, if I use the following call pattern:

    Code:
    new SimpleJdbcCall(datasourceDao.getJdbcTemplate(datasource)
    					.getDataSource())
    					.withCatalogName("webapp")
    					.withProcedureName("pr_dynamicquerygrant")
    					.withoutProcedureColumnMetaDataAccess()
    					.declareParameters(
    							new SqlParameter("p_db_user", Types.VARCHAR))
    					.execute("testuser");
Working...
X