How to pass a Oracle Reference Cursor as an input to a stored procedure

, ,
1

Hi All

We have a situation where in we need a pass a oracle reference cursor as an input to the stored procedure.

I tried directly mapping the document list to the reference cursor but the driver doesnt seem to recognize it.

Can you please advise me how to type cast the document list to an oracle reference cursor.

Thansk in advance.

Mani

2

From Oracle documentation at [URL]http://download.oracle.com/docs/cd/B25329_01/doc/appdev.102/b25312/procedures_plsql.htm[/URL]

A ref cursor is a PL/SQL datatype whose value is the address of the memory location of a query work area on the database server. A query work area can be thought of as the result set, or a row set, on the server; it is the location where the results of a query are stored in server memory. In essence, a ref cursor is a handle to a result set on the server. A ref cursor is represented through the OracleRefCursor ODP.NET class.
Ref cursors have the following characteristics:

  1. A ref cursor refers to server memory. The memory address represented by a ref cursor “lives” on the database server, not on the client machine. Therefore, the client’s connection to the database must be in place during the lifetime of the ref cursor. If the underlying connection to the database is closed, the ref cursor will become inaccessible to the client.

What you’re asking to do may not be doable. And certainly a document list is not going to be able to simply be type cast to a ref cursor.