Querying thru stored procedure in OCI applications...

We want to use stored procedures to open cursors and return rows to the calling functions in an OCI application. However, we face the problem that these packaged cursors persist throughout the whole login session.

So, in a client-server application where the process server have one login to the ORACLE database, if there are multiple clients asking the server to execute the same stored procedure which returns the next number of rows of a packaged cursor to the client, problem will come up as the first client gets the first n rows, the next client gets the next n rows and so on. This is not what we want. We want each client will have its own copy of cursor instead of sharing the same one!

This problem could be solved if ORACLE can return all the rows to the calling program without requiring it to pre-allocate enough memory to store the results. You never know how many rows the query will return ahead of time. The Sybase C interface library allocates the memory to store the query results for you and so the calling program will only have to pass a pointer to the stored procedures. Unfortunately, ORACLE does not provide you with this basic feature, and so we have to predefine a max number of rows to return each time the stored procedure is called.

The cdemo4.c example in the OCI programmer's guide shows how to create a stored procedure that opens the cursor if it is not already open, fetches a batch of rows, and returns the rows to the calling program. When all the rows in the cursor are fetched after the stored procedure is called a number of times, the procedure closes the cursor.

This sample program will not work if it is called by multiple clients. Each client will receive incorrect results.

Does anyone has solution for this kind of problem? I talked to ORACLE technical support but they could not give us an satisfactory answer. I believe someone who has used the OCI libraries and stored procedures may give us more helpful suggestion.

Please respond to carisa_at_aspectdv.portal.com if you have any suggestions. Your suggestions are greatly appreciated!

Carisa Received on Tue May 11 1993 - 02:15:47 CEST