Can we use PL/SQL Cursor variable as an out parameter in a GET API?

Ankan Chanda

Hi team,

We are trying to fetch email of some users from a table using pl/sql stored procedure. What we are trying to achieve is that ,in the handler section we are passing a variable called list (containing list of mails in the stored procedure) to a sys_refcursor variable called l_cursor and in the restful services we are providing the begin insert command like this:

declare l_cursor out SYS_REFCURSOR; 

                        begin 

                        RETRIEVDATA(list => l_cursor); 

                        end;

Now my query is what to do with the l_cursor variable? Should we bind it with list? Which one will be the bind variable here, as I assume, l_cursor will be the bind variable. We did that and the other way round but it still gives HTTP 555 error.

Please suggest as to how can we fetch the emails through the GET API?

thatJeffSmith-Oracle

the easiest way is with a function, then just use SQL

https://www.thatjeffsmith.com/archive/2021/03/four-options-for-working-with-ords-plsql-refcursors/

Ankan Chanda

Hi Jeff,

Thank you so much for your response. I went through your article which is fantastic by the way, you have shown a number of ways to call , get responses through ORDS which is more than sufficient. So by going through your article, I made an attempt in correcting my own and yes now I am getting an HTTP status 200 OK , but, I am not able to see any response by calling GET method. So I have made these 3 attempts with the PL/SQL code attached to the API:

Attempt 1: declare l_cursor SYS_REFCURSOR;

begin

 RETRIEVDATA(list => :l_cursor); (l_cursor getting binded to a variable named output in the parameters section)

end;

Attempt 2: declare l_cursor SYS_REFCURSOR;

begin

  RETRIEVDATA(list => l_cursor);

  :ret := l_cursor; (ret getting binded to a variable named output)

end;

Attempt 3: declare l_cursor SYS_REFCURSOR;

ema varchar2(200);

begin

RETRIEVDATA(list => l_cursor);

loop

fetch l_cursor into ema; (ema getting binded to a variable named output)

exit when l_cursor%NOTFOUND;

end loop;

close l_cursor;

end;

The RETRIEVDATA is a stored procedure helping to fetch the list of emails. Here is the code:

create or replace procedure RETRIEVDATA(list out SYS_REFCURSOR)

as

begin

open list for select email from demo;

end RETRIEVDATA;

As we run the stored procedure using loop in the SQL command section, it is able to fetch the list of mails visible in the screen but its failing to get the response when the GET API is being called with the above 3 attempts, calling the stored procedure. The status 200 OK is coming but without the response body.

Thanks and Regards

Ankan

thatJeffSmith-Oracle

In scenario 1 is :ret parameter defined as a RESPONSE OUT RESULTSET ?