cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Receiver Oracle Stored Procedure with Cursor

Former Member
0 Kudos

Hi All,

I was able to successfully implement the Oracle stored procedure in my receiver jdbc adapter and it works fine. Now my requirement arises that in my search response I may get multiple records say Select * from UsersTable where userStatus="active". This will return say 1000 records.

I spoke with my DB person and he said I can change the stored procedure to have two parameters, one passing userStatus, and one passing out ref cursor. Does SAP PI 7.31 support cursors and how will my request and response structure in message type change  to adapt the cursor response.

Regards

Ravijeet

Accepted Solutions (1)

Accepted Solutions (1)

Harish
Active Contributor
0 Kudos

Hi Ravijeet,

Please check step 5 and 6 in the below blog.

reference link -

regards,

Harish

Former Member
0 Kudos

Hi All,

Any suggestions for me.

Below is my Oracle stored Procedure definition.

    PROCEDURE get_user (P_PERSONNEL_NO           IN OUT NUMBER,

                          P_RESPONSE_CODE          OUT NUMBER,

                          P_RESPONSE_MSG           OUT VARCHAR2,

                          R_RECORD                 OUT SYS_REFCURSOR);

Thanks in advance

Ravijeet

former_member184720
Active Contributor
0 Kudos

Hi Ravijeet -

You can define your request structure similar to what is mentioned by venky in the below thread.

and to answer his question on what to map for cursor field, try mapping it with the below constant.

OracleTypes.CURSOR

There should not be any change in your response structure.

Former Member
0 Kudos

Hi Hareesh,

So you say I need to map the constant OracleTypes.CURSOR to the type attribute. I get below error:

Catching exception calling messaging system: XIAdapterFramework:GENERAL:com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'IAM_USERS.IAM_USER_ACCTS.get_user' (structure 'Statement'): java.sql.SQLException: Unsupported parameter type 'OracleTypes.CURSOR' for parameter 'R_RECORD' found

Below is the request I sent to DB

<?xml version="1.0" encoding="UTF-8"?>

<ns1:MT_SelectUserDBRequest>

  <Statement>

  <stmt action="EXECUTE">

  <table>IAM_USERS.IAM_USER_ACCTS.get_user</table>

  <P_PERSONNEL_NO isOutput="true" isInput="true" type="STRING">1</P_PERSONNEL_NO>

  <P_RESPONSE_CODE isOutput="true" type="STRING"/>

  <P_RESPONSE_MSG isOutput="true" type="STRING"/>

  <R_RECORD isOutput="true" type="OracleTypes.CURSOR"/>

  </stmt>

  </Statement>

</ns1:MT_SelectUserDBRequest>

I am using in the adapter config the below JDBC Driver : oracle.jdbc.driver.OracleDriver

Connection : jdbc:oracle:thin:@hpusir49.com:1521:CMP

Do I need to have anything specific in the configuration ?

Regards

RJ

gagandeep_batra
Active Contributor
0 Kudos

Hi ravi,

try with isOutput=“1“   isInput="1"  instead of "true"

check below that may help you

SAP Library - Partner Connectivity Kit


Regards

GB

former_member184720
Active Contributor
0 Kudos

Hi Ravijeet - I think you should also upgrade your oracle drivers. Earlier versions seems to be unsupportive for passing cursors to SP's.

Have a look into the below discussion -

StoredProcedureItemReader and Oracle SYS_REFCURSOR as an IN parameter? - Spring Forum

Former Member
0 Kudos

Hi Hareesh,

I checked the jdbc version and it states below version:

conn.getMetaData().getDatabaseProductName() = Oracle

conn.getMetaData().getDatabaseProductVersion() = Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

conn.getMetaData().getDriverName() = Oracle JDBC driver

conn.getMetaData().getDriverVersion() = 10.2.0.5.0

conn.getMetaData().getJDBCMajorVersion() = 10

conn.getMetaData().getJDBCMinorVersion() = 2

So I think that is a recent version, in the above blog it was mentioning about a version 9.

Any other suggestion which could be causing this error ?

Regards

RJ

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Hareesh,

My scenario is working good with cursors except the case when my search criteria returns empty result set, I get an exception in my receiver jdbc adapter as below:

XIAdapterFramework:GENERAL:com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'IAM_USER.f_get_user' (structure 'Statement'): java.sql.SQLException: Cursor is closed.

Any idea how to handle cursor response in sap pi when there is no data in response

Regards

Ravijeet

former_member184720
Active Contributor
0 Kudos

Hi Das - Can you have a look at the below thread if it helps?

http://scn.sap.com/thread/3282263