Oracle REF CURSOR

Good day!

Does SQL Server has aby analog of ORACLE's Ref Cursors? ex: "type [name] is ref cursor"?

Thanks you!

No. SQL Server has a cursor data type that you can use as output parameter and only within TSQL code. You cannot bind to the cursor parameters from any of the client library like in Oracle. In SQL Server, resultsets are returned by just writing a SELECT statement without any INTO clause whereas in Oracle you typically use REF cursors or tables. Are you trying to convert some Oracle code to SQL Server? Can you explain your problem more clearly? It will be easier to suggest a workaround or migration strategy if I know your requirements.

Below are some links that will help you understand the migration problems:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0761.mspx?mfr=true

http://www.microsoft.com/downloads/details.aspx?FamilyId=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en

http://www.microsoft.com/sql/solutions/ssm/ssmav2.mspx

|||

Yes, I have a task to convert the Oracle code to Sql Server. Original code is quite large and complicated. It is divided into 15 packages (like: actpack, alarmpack etc.). Usually REF cursors are used within one package and I think that there can not be any problems with this. But almost all the packages bind to a cursor which is declared in "onepack". Some procedures return it, ex:

procedure pcoldomsel (cidl in varchar, nf out number, cout out onepack.ct);

I believe that the strategy can be quite simple. We are not to make any client libraries (in result it will be a "collection" of procedures), that is why I think that we just need to make all the cursors' names unique (if required).

|||You could use the output cursors for your scenario. But there are more efficient ways to do this in SQL Server using temporary tables for example. In most cases you don't really need a cursor. So if you want to retain the exact logic, you could use output cursors but there are so many differences between PL/SQL (more richer programming language) & TSQL that it is not going to be a simple conversion. And there is no package object in TSQL so you will have to either create a schema instead of package but this just provides a namespace mechanism. You will have to handle permissions for procedures accessing objects in other schema for example. To begin with, you could try the Oracle Migration Assistant to see how it converts the code and then optimize the migrated code.