sqlserver to oracle port - multiple ref cursor problem.

Hi,

We are working on an oracle port project where in the application is being moved from sql server database to oracle, without effecting the front end code a bit...which means..I need to do in oracle what happens in sqlserver ...DITTO.

Now I have 1 scenario which I am not sure how to go about with.

SQLSERVER has this funda of of returing recordsets to front end just by using a select statement.eg...

Code:

---

create proc test
begin
select empname,empno from emp
end
/

---

this would return a recordset to front end..

In oracle I need to use ref cursor's for this...

But there are some places where in I have something like this in sqlserver

Code:

---

CREATE PROC totalshyt
BEGIN
DECLARE c1 CURSOR FOR
SELECT empno,empname FROM emp
OPEN AND FETCH THE CURSOR

SELECT empsomething FROM address WHERE empname=@c1.empname
CLOSE CURSOR
END
/

---

Now I am not really sure how many select's are going to be in cursor before hand...so the count of ref-cursor's is unknown.

Can anyone please let me know how to go about with this?

Thanks
Sam

The cursor in side the procedure is opened and closed and it has nothing to do with cursor which is returning the recordset.
Usually last Select statement is returned as recordset.
So u can simply use refcursor for the Select statement for which u want recordset.. see below code

create proc test (declare in out refcursor)
begin
open cursor cur_ref for select empname,empno from emp;
end
/


CREATE PROC totalshyt (declare in out refcursor)
BEGIN
DECLARE c1 CURSOR FOR
SELECT empno,empname FROM emp
OPEN AND FETCH THE CURSOR
CLOSE CURSOR

open cursor cur_ref for SELECT empsomething FROM address WHERE empname=@c1.empname
END
/

Hi Rakesh,

Thanks for the reply.

But that was just an example.

There are procedures in the sql server system where in a cursor is opened and each fetch returns a recordset to the front end.

So, for eg, if the cursor fetches 14 records, 14 recordsets are returned to the front end from sql server.

I want to do something similar in oracle, return multiple recordsets without actually knowling how many (as the cursor is also dynamically created with a where clause)

Hope I am clear this time.

Thanks
Sam

In that case I would suggest to replace the existing Cursor statement with single ref cursor and process the records in front end. Retruning recordset on each Fetch is not good idea....as this would cause network load.

Secondly in Oracle we do not have equivalent feature as of SQLserver.

Thanks