Skip to Content
0
Former Member
Aug 27, 2009 at 05:03 PM

PI7 JDBC RECEIVER - RECEIVE A TABLE FROM ORACLE STORED PROCEDURE

17 Views

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.