Announcement Announcement Module
Collapse
No announcement yet.
Stored procedure output parameters Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored procedure output parameters

    Hello,

    I am trying to call an Oracle stored procedure from a subclass of JdbcDaoSupport but I don't seem to be able to get the output parameter.
    How am I supposed to get the value of the ouput parameter? I'm using
    spring 1.2.4 and the version 9.2.0.5 of the Oracle JDBC driver.

    Thanks in advance,
    Al

    The stored procedure looks like this:

    CREATE OR REPLACE PACKAGE BODY Application
    IS
    PROCEDURE Create_Application (
    id_facility_aco_intrnl IN number,
    cd_gender_applcnt IN char,
    name_applcnt_last IN varchar2,
    dt_birth_applcnt IN date,
    nbr_applcnt OUT number
    )
    IS

    nothing varchar2(1);

    BEGIN

    nbr_applcnt := 5678;

    END;

    END;

    The class that is calling is the following:

    public class CRCApplicationJdbcImpl extends JdbcDaoSupport implements CRCApplicationDao
    {

    public CRCApplication createApplication(CRCApplication application) throws ReportableException
    {
    JdbcTemplate tmpl = getJdbcTemplate();
    ArrayList args = new ArrayList();
    args.add(new SqlOutParameter("nbr_applcnt", Types.BIGINT));
    Map resultMap =
    tmpl.call(new CRCApplicationCreateStatementCreator(), args);
    return null;
    }

    class CRCApplicationCreateStatementCreator implements CallableStatementCreator {

    public CallableStatement createCallableStatement(Connection c) throws SQLException {
    CallableStatement stm = c.prepareCall("{call Application.Create_Application(?,?,?,?,?)}");
    stm.setLong(1, 1);
    stm.setString(2, "C");
    stm.setString(3, "Just a sample string");
    stm.setDate(4, new java.sql.Date(Calendar.getInstance().getTimeInMill is()));
    stm.registerOutParameter(5, Types.BIGINT);
    return stm;
    }

    public static void main(String[] args) {
    try{
    // I get a db connection here...
    DriverManagerDataSource src = dbConnection.getDataSource();
    CRCApplicationJdbcImpl dao = new CRCApplicationJdbcImpl();
    dao.setDataSource(src);

    dao.createApplication(new CRCApplication());

    }catch(Throwable e){
    e.printStackTrace();
    }
    }

    }

    When I run this I get:

    org.springframework.jdbc.InvalidResultSetAccessExc eption: CallableStatementCallback; invalid ResultSet access for SQL []; nested exception is java.sql.SQLException: Invalid column index
    java.sql.SQLException: Invalid column index
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:269)
    at oracle.jdbc.driver.OracleStatement.prepare_for_new _get(OracleStatement.java:3379)
    at oracle.jdbc.driver.OracleStatement.getObjectValue( OracleStatement.java:5848)
    at oracle.jdbc.driver.OracleStatement.getObjectValue( OracleStatement.java:5827)
    at oracle.jdbc.driver.OracleCallableStatement.getObje ct(OracleCallableStatement.java:698)
    at org.springframework.jdbc.core.JdbcTemplate.extract OutputParameters(JdbcTemplate.java:917)
    at org.springframework.jdbc.core.JdbcTemplate$5.doInC allableStatement(JdbcTemplate.java:853)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:808)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:841)
    at gov.ga.dhr.ors.acorn.dao.jdbc.CRCApplicationJdbcIm pl.createApplication(CRCApplicationJdbcImpl.java:6 1)
    at gov.ga.dhr.ors.acorn.dao.jdbc.CRCApplicationJdbcIm pl.main(CRCApplicationJdbcImpl.java:148)

  • #2
    You are not using much of Spring's JDBC support here. If you want to code the JDBC call yourself then take a look at this link http://www.enterprisedt.com/publicat...esult_set.html for how to access the resultset. If you want to use Spring's stored procedure support look at this thread http://forum.springframework.org/showthread.php?t=14420
    Last edited by robyn; May 14th, 2006, 07:37 PM.

    Comment


    • #3
      IN OUT parameter in stored procedure

      Hi all,

      Does spring's StoredProcedure abstraction support in out parameter of oracle stored procedure? I did not found a class named SqlInOutParameter for example...

      regards,
      Sandro

      Comment

      Working...
      X