Announcement Announcement Module
Collapse
No announcement yet.
Error while call Stored Procedure using Spring Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error while call Stored Procedure using Spring

    Hi

    I am my trying to call Stored Proc from Java using spring class extending StoreProcedure class

    Getting exception like
    org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call EIP_STAGE.PKG_GATEWAY_DATA.GET_ACCOUNT_ALL(?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'GET_ACCOUNT_ALL'
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'GET_ACCOUNT_ALL'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    at org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.doTranslate(SQLStateSQLExceptionTran slator.java:98)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:969)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:1003)
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:144)
    at com.jpmorgan.im.equity.procedure.AccountStoreProce dure.getAccount(AccountStoreProcedure.java:69)
    at com.jpmorgan.im.equity.dao.AccountDAOImpl.getAccou ntsByAccountId(AccountDAOImpl.java:29)
    at com.jpmorgan.im.equity.dao.AccountDAOTests.testGet SpecificAccount(AccountDAOTests.java:32)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runRefle ctiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallabl e.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExpl osively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod .evaluate(InvokeMethod.java:20)
    at org.junit.internal.runners.statements.RunBefores.e valuate(RunBefores.java:28)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:76)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner. java:193)
    at org.junit.runners.ParentRunner$1.schedule(ParentRu nner.java:52)
    at org.junit.runners.ParentRunner.runChildren(ParentR unner.java:191)
    at org.junit.runners.ParentRunner.access$000(ParentRu nner.java:42)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRu nner.java:184)
    at org.junit.runners.ParentRunner.run(ParentRunner.ja va:236)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestR eference.run(JUnit4TestReference.java:49)
    at org.eclipse.jdt.internal.junit.runner.TestExecutio n.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:197)

    Here is my Stored Procedure definition

    PROCEDURE get_account_all
    (
    p_account_id IN NUMBER
    ,p_account_cursor OUT pkg_gateway_data.account_cursor
    ,err_msg OUT Varchar
    ,p_blockno OUT NUMBER
    ,err_code OUT NUMBER

    );

    Here is Java code to call this proc

    @SuppressWarnings("unchecked")
    public Account getAccount(Long accountId){
    log.debug("Calling EIP_STAGE.PKG_GATEWAY_DATA.GET_ACCOUNT_ALL to get single account data....");
    setSql("EIP_STAGE.PKG_GATEWAY_DATA.GET_ACCOUNT_ALL ");
    declareParameter(new SqlParameter("p_account_id", Types.NUMERIC));

    declareParameter(new SqlOutParameter("err_msg", Types.VARCHAR));
    declareParameter(new SqlOutParameter("p_blockno", Types.NUMERIC));
    declareParameter(new SqlOutParameter("err_code", Types.NUMERIC));
    declareParameter(new SqlOutParameter("p_account_cursor",OracleTypes.CUR SOR,new AccountRowMapper()));
    compile();

    Map<String, Object> inParameters = new HashMap<String, Object>();
    inParameters.put("p_account_id", accountId);
    Map<String, Object> result = execute(inParameters);
    for(String key : result.keySet()){
    System.out.println("Key: "+key+" value: "+ result.get(key));
    }
    List<Account> accounts = null;
    if(null != result && result.size()>0){
    accounts = (List<Account>) result.get("p_account_cursor");
    }
    return accounts.get(0);
    }


    Can you please suggest how to fix this?

    thanks in advance
    Mitra
Working...
X