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.

[VijayKonam]

Unfortunately you can send complex data type to a stored procedure. They should be primitive SQL data types. One alternative is to send them them as one string (comma seaprated) parameter and process. As far I know, JDBC channel can return multiple records (you are calling it table).

VJ