However the following does work for a function returning a refcursor.
procedure test1 (pRefCursor1 out sys_refcursor, prefCursor2 out sys_refcursor);
If you press the "Why Not" button in the PLSQL Web Service dialog you get this message.
function retrieve_emps return sys_refcursor;
"Ref cursor type arguments are unsupported due to a JDBC limitation. Ref cursor types are only supported for use as the return type".
Due to this limitation if you have the luxury of developing the PLSQL from scratch you can simple use PLSQL tables to overcome this. So lets assume we have a collection defined as follows.
create or replace TYPE employee_list AS TABLE OF employee_obj;
DROP TYPE employee_list;
DROP TYPE employee_obj;
CREATE OR REPLACE TYPE employee_obj AS OBJECT
( empno NUMBER(4)
, ename VARCHAR2(10)
, job VARCHAR2(9)
, sal NUMBER(7,2)
, deptno NUMBER(2)
);
/
So now we can use a method signature as follows and this will work fine.
procedure copy_emps (pInEmps in employee_list, pOutEmps out employee_list);
7 comments:
Hi,
would it really be useful if a REF CURSOR can be returned by a web service? I doubt not, because the application which access the database through a web service doesn't have direct access to your database so the REF CURSOR is some kind of useless.
Let's say it's used as IN parameter for a next call. Also in that case it's useless, because it's likely that your web service is using a connection pool so you wouldn't get the same database session again. And I'm not talking about cursors which are not closed, ...
I think using PL/SQL TABLE as result is from a technical perspective the only way to go.
Greetings
Patrick
Actually we get lots of customer's who wish to use refcursor types with PLSQL Web Services, and returning a refcursor is useful for them. When you return a refcursor you get all the rows from that CURSOR which is perfect for READ ONLY reports and other needs so I don't think it's useless at all.
AS for refcursor being used as an IN parameter well thats not possible hence the reason for this BLOG entry, as refcuros types can't be used as program arguments in PLSQL Web Services.
Yes I would prefer using PLSQL tables as well hence why I proposed that and has no such limitations exist there.
If you are returning PL/SQL tables/collections then you would need to be aware/careful of how much data you are passing back to minimise the amount of memory needed during transport.
ref cursors bypass this issue, by providing cursor control to the caller. However, this would require a stateful web service.
I think the point that Patrick might be making is that this then restricts the flexibility of the service. For example publishing your web service on the public internet.
All the best,
Hi Pas,
to my knowledge a REF CURSOR is just a pointer to a result set in server memory, it's not a fixed result set which is transfered to the client.
See also the first section of http://www.oracle.com/technology/oramag/oracle/06-jan/o16odpnet.html
That's why you have to make an extra call to the server to get the actual result set, but if you would do that with a web service there is no guaranteed that the web service will use the same database session...
It's not comparable to a JDBC connection/ODBC connection where you to the following call in the same database session. That's why in my opinion REF CURSORS can't work with web services by design.
Patrick
Your right about ref cursor BUT in the case of a web service as soon as you make a call to a function that returns a ref cursor all the data is returned to the client at that point.
I confirmed this with the "HTTP Analyzer" in JDeveloper and thats exactly what happened. You can try this yourself in JDeveloper 10.1.3.x.
I have no doubt outside of web services it works differently with ref cursors.
My function was as follows:
function retrieve_emps return sys_refcursor;
And my web service proxy calls that function with this line of code.
Element retData = myPort.retrieveEmpsXML();
At the end of that call you get all the data from the ref cursor returned to the client as I viewed it in the "HTTP Analyxer".
Of course that being the case it makes sense to LIMIT the amount of data being returned.
I do not really have experience with JDeveloper, but based on the function name "retrieveEmpsXML" in your comment I would assume that the web service proxy JPublisher generates for your PL/SQL function does internally the two calls. If first gets the REF CURSOR and it does a second call to retrieve the result set of the REF CURSOR and transform it into XML (?). So actually you have a web service which doesn't return a REF CURSOR you just get XML and the web service itself does the resolving of the REF CURSOR without having to call another web service to get the actual result.
If you look at the generated proxy code I'm sure you will see that. As Mathew has written, as long as you do not have a stateful web service it's technically not possible. But a stateful web service is dangerous because you can get a lot of opened cursors which are never closed...
Patrick
For clarity, by a stateful web service, I meant that the the ref cursor processing would require a few database calls, and you would need to maintain session state between these calls ie: maintin the same session.
Your comment about ref cursors is interesting. I think we all agree that ref cursors require additional processing by the caller, in order to execute/fetch all the available rows associated with the cursor on the DB side, and then close the cursor.
The additional information you provide, and the code snippet, suggest that this cursor processing is happening within the call you make. The suggestion seems to be that this is a third Party software call.
If you have a simple test case, I'd be interested to look at it.
Otherwise, maybe this is something to investigate for a later blog post?
Thanks for the postings, they made me re-evaluate what I understand about DB backed web services.
Regards,
Post a Comment