on 01-24-2014 6:49 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi ravi,
try with isOutput=“1“ isInput="1" instead of "true"
check below that may help you
SAP Library - Partner Connectivity Kit
Regards
GB
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Das - Can you have a look at the below thread if it helps?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.