stored procedure by using EXECUTE at the sender JDBC

[Former Member]

We have Scenario from JDBC to RFC (ASYNCH).We are using Oracle DB,at the database side we have a stored Procedure and inside the Procedure we are accessing multiple table.

My query is how can we excecute update sql statement a stored procedure by using EXECUTE at the sender JDBC?

EXECUTE SP_STUDENT_UPDATE

ERROR: 'java.sql.SQLException: ORA-00900: invalid SQL statement '. For details, contact your database server vendor.

[Former Member]

Hi. Peter.

yes , is possible to update your rows in your sp.

• If you want the data determined from the Query SQL Statement to remain in the database unchanged after being sent successfully, enter <TEST>.This is recommended if the data has not only been read, but also changed by a stored procedure entered under Query SQL Statement.

http://help.sap.com/saphelp_nw73/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm?frameset=...

Could you put your sp? Maybe something is  wrong .

Regards

Lucho

[dgreengas]

The sender JDBC can use a stored procedure to pull the data. Within that stored procedure you can have many update statements, but only one select statement. After pulling the data, you can run a specific sql statement, but not execute a stored procedure.

In the usage scenarios at my company we do the following:

1. Execute a stored procedure to pull data (asynchronous integration) with the procedure doing the following:

    a) Update a set of rows with an intermediate status

    b) Select rows with this intermediate status (may contain joins to other tables)

2. For post processing, run an update statement to update records with the intermediate to the final status (e.g. "In Processing" to "Completed"

Does your stored procedure have multiple selects? Is the EXECUTE command correct? I have used EXEC with SQL Server. It appears that Oracle supports EXEC too.