PI7 JDBC RECEIVER - RECEIVE A TABLE FROM ORACLE STORED PROCEDURE

[Former Member]

Hi everybody.

I am trying a FILE(sender)-JDBC(receiver)-FILE(receiver) scenario without BPM (using AF_Modules/RequestResponseBean and AF_Modules/ResponseOnewayBean modules on File Sender Comm Channel)

My requirement is to receive from an Oracle Stored Procedure, the result of a complex SQL (that, between a few things, opens a cursor, updates some tables, and returns the results in an OUTPUT parameter). The idea is that the stored procedure, returns a table, and not a single parameter (i am not sure if it s posible!!).

The definition of the Oracle stored procedure is as follows:

CREATE OR REPLACE PROCEDURE sp_name ( parameter_out   out  sys_refcursor ) 
is 
   BEGIN 
...... 
...... 
...... 
        
         COMMIT; 
     
         EXCEPTION 
         WHEN OTHERS 
             THEN 
             ROLLBACK; 
   END

The message i am sending to the JDBC receiver is defined like that:

<?xml version="1.0"; encoding="UTF-8"?> 
<ns0:MT_XXXXX xmlns:ns0="urn:XXX"> 
                <Statement> 
                               <sp_name action="EXECUTE"> 
                                               <parameter_out   IsOutput="true" type="SQLDatatype"> </parameter_out> 
                               </sp_name> 
                </Statement> 
</ns0:MT_XXXXX>

The JDBC comm channel is configured like that.

oracle.jdbc.driver.OracleDriver

jdbc:oracle:thin:@server:1521:DBNAME

When we execute the scenario, the JDBC Comm channel is raising the following error:

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'sp_name' (structure 'Statement'): java.sql.SQLException: Unsupported parameter type 'SQLDatatype' for parameter 'parameter_out' found.

I 'd really appreciate your help.

Thanks in advance.

Best regards.

Cristian.

[Former Member]

Hi Rajesh,

I expect from the Stored Procedure, multiple fields (each one with its predefined SQL data type) but also multiple lines. Similar to the result of a SELECT statement.

The people who created the Oracle Stored Procedure, told me that they are sending the response in a GENERIC CURSOR

Could you please facilitate, if you know, some example of this scenario ?

Thanks again.

Cristian.

Edited by: Cem_78 on Aug 27, 2009 2:55 PM

[Former Member]

As you mentioned SP is going to return more than one value(i.e some values)

but while calling SP you defined the output type as only one variable that is causing the problem

get the signature of the SP and then define the out values exactly what sp is going to return with appropriate data type

HTH

Rajesh