Dealing with stored procedure return value and one/two cursors with n-number of returned columns by 'int-jdbc:stored-proc-outbound-gateway'

Question

So far I was able to pass n-number of parameters to stored procedure (Oracle) from front end and receive one cursor (with n-number of columns) from back end (Thanks to Gary for his great suggestion link ).

However, the following scenario still i couldn't resolve where I am trying to receive not only just one cursor (with n-number of columns) but a return value along with n-number of cursors (with n-number of columns) from back-end. I need help :)

So far what i have done is as follows:

In my pkg_personalinfo_spring.proc_personalinfo_spring having the following Spec:

procedure proc_personalinfo_spring(
        firstname       in  VARCHAR2,
        lastname        in  VARCHAR2,
        p_RetVal       out  VARCHAR2,           //Previously I was trying without such kind of p_RetVal 'out' variable and it was working. As soon as I added this 
                                                //started   getting exception 
        p_ResultSet    out  sys_refcursor);

In the body of my stored proc having this:

...
p_RetVal := '1';

EXCEPTION WHEN OTHERS THEN p_RetVal := p_RetVal || 'proc_settlement_report: SQLCode: ' || SQLCODE || ' SQL Error Msg: ' || SUBSTR(SQLERRM, 1, 1950); dbms_output.put_line('Error');

  RETURN;
...

Now to use p_RetVal (which is my return value) along with my one cursor so far, I did these are the changes:

a) In PersonalinfoDao I added new getter and setter for p_RetVal:

String p_RetVal;

public String getP_RetVal() {
    return p_RetVal;
}

public void setP_RetVal(String p_RetVal) {
    this.p_RetVal = p_RetVal;
}

b) And in Mapper class added the following line:

...
PersonalInfo personalInfo = new PersonalInfo();

        try{
            personalInfo.setP_RetVal(resultSet.getString(DBConstants.P_RETVAL));
            feedInfo.setFirstname(resultSet.getString(DBConstants.FIRSTNAME));
            feedInfo.setLastname(resultSet.getString(DBConstants.LASTNAME));
... 

c) Finally in Gateway definition file I have the following changes:

<!-- Stored Procedure Outbound-Gateway = To call a database stored procedure -->        
    <int-jdbc:stored-proc-outbound-gateway  id="outbound-gateway-storedproc-personalinfo"
                                            request-channel="procedureRequestChannel"
                                            data-source="dataSource"
                                            stored-procedure-name="pkg_personalinfo_spring.proc_personalinfo_spring"
                                            expect-single-result="true"
                                            ignore-column-meta-data="true"
                                            skip-undeclared-results="true">
        <!-- Parameter Definitions -->                                      
        <int-jdbc:sql-parameter-definition  name="firstname" direction="IN"/>
        <int-jdbc:sql-parameter-definition  name="lastname" direction="IN"/>
        <int-jdbc:sql-parameter-definition name="p_RetVal" type="VARCHAR" direction="OUT"/>
        <int-jdbc:sql-parameter-definition name="get_ResultSet" type="#{T(oracle.jdbc.OracleTypes).CURSOR}" direction="OUT"/>

        <!-- Parameter Mappings Before Passing & Receiving -->                              
        <int-jdbc:parameter name="firstname" expression="payload[0]"/>
        <int-jdbc:parameter name="lastname" expression="payload[1]"/>
        <int-jdbc:returning-resultset name="p_RetVal" row-mapper="com.ge.far.support.FeedinfoMapper"/>
        <int-jdbc:returning-resultset name="get_ResultSet" row-mapper="com.support.PersonalinfoMapper"/>

</int-jdbc:stored-proc-outbound-gateway>

Now, When I am running the app, getting following exceptions:

...
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:596)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:527)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call PKG_PERSONALINFO_SPRING.PROC_PERSONALINFO_SPRING(?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 3:
PLS-00306: wrong number or types of arguments in call to 'PROC_PERSONALINFO_SPRING'
ORA-06550: line 1, column 3:
PL/SQL: Statement ignored

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1135)
...
    at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:170)
    at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:78)
    ... 59 more
Caused by: java.sql.SQLException: ORA-06550: line 1, column 3:
PLS-00306: wrong number or types of arguments in call to 'PROC_PERSONALINFO_SPRING'
ORA-06550: line 1, column 3:
PL/SQL: Statement ignored

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) 

Plz help me to find out where I am doing mistake(s). I also need to deal with a situation where there will be one return variable (such like p_RetVal) as we as n-number of cursors (sys_refcursor) in my result set.

Thanks again everyone :)

Answer 1

Ok, so after little research i think got a little clue which can be a solution to the above scenario(s).

First: in my Gateway definition file i changed 'expect-single-result' attribute's value to 'false' in 'int-jdbc:stored-proc-outbound-gateway' element. This is logically correct because my stored proc will be returning more than one result-set objects.

Second: I added the following line in my Gateway definition file:

<int-jdbc:sql-parameter-definition name="p_RetVal" direction="OUT"/>

So currently it looks like as follows:

<!-- Stored Procedure Outbound-Gateway = To call a database stored procedure -->        
<int-jdbc:stored-proc-outbound-gateway  id="outbound-gateway-storedproc-personalinfo"
                                            request-channel="procedureRequestChannel"
                                            data-source="dataSource"
                                            stored-procedure-name="pkg_personalinfo_spring.proc_personalinfo_spring"
                                            expect-single-result="false"
                                            ignore-column-meta-data="true">
        <!-- Parameter Definitions -->                                      
        <int-jdbc:sql-parameter-definition  name="firstname" direction="IN"/>
        <int-jdbc:sql-parameter-definition  name="lastname" direction="IN"/>
        <int-jdbc:sql-parameter-definition name="p_RetVal" direction="OUT"/>
        <int-jdbc:sql-parameter-definition name="get_ResultSet" type="#{T(oracle.jdbc.OracleTypes).CURSOR}" direction="OUT"/>

        <!-- Parameter Mappings Before Passing & Receiving -->                              
        <int-jdbc:parameter name="firstname" expression="payload[0]"/>
        <int-jdbc:parameter name="lastname" expression="payload[1]"/>
        <int-jdbc:returning-resultset name="get_ResultSet" row-mapper="com.support.PersonalinfoMapper"/>

</int-jdbc:stored-proc-outbound-gateway>

Third: as i am not defining 'p_RetVal' under 'Parameter Mappings Before Passing & Receiving' section that means i will not use 'p_RetVal' for mapping in my Java code. Therefore, i deleted 'String p_RetVal;' from 'PersonalinfoDao' and its getter and setter.

Forth: Due to third step, I also modified my Mapper class as follows:

...
PersonalInfo personalInfo = new PersonalInfo();

        try{
            feedInfo.setFirstname(resultSet.getString(DBConstants.FIRSTNAME));
            feedInfo.setLastname(resultSet.getString(DBConstants.LASTNAME));
... 

That's all! really???

Yes!!!...but there is a catch...well, i was not sure till this far that my code will run without exceptions. But after running the App with all those little changes i saw no exceptions and so started little debugging what is going on through 'FIDDLER' (My best friend lah ;) ), where i saw the following JSON object return from back-end. Yahoooooooooo...means Spring Integration is on its action :)

JSON
  - {}
     -  p_ResultSet
     -   -{}
     -     -    firstname=Faisal
     -     -    lastname=Quazi
     -  p_RetVal=1

Now, the catch is as u already noticed that in Gateway definition file I defined 'p_RetVal' under 'Parameter Definitions', I didn't define that under 'Parameter Mappings Before Passing & Receiving'. This part i am still not sure WHY or HOW it is working in background.

So at this very moment, u can consider this answer of mine not an ultimate solution but may accept it as by-passing the deadlock situation. Spring Integration Experts might have some good say on this :)

Answer 2

Since your p_RetVal is VARCHAR OUT param you can't use the returning-resultset for it.

Just remove the <int-jdbc:returning-resultset name="p_RetVal"> definition and the result payload of the <int-jdbc:stored-proc-outbound-gateway> will contains a Map with two keys: p_RetVal as String and get_ResultSet as List<PersonalInfo>.

Of course you should get rid of DBConstants.P_RETVAL in the PersonalinfoMapper and the FeedinfoMapper is redundant.

<int-jdbc:returning-resultset> is only for the CURSOR procedure OUT params.