DB Best blog

In this article, I am going to cover the situation when REF CURSOR type is used in output parameter of a stored procedure. Often this method is chosen to return the result set from a procedure to an application. Still, in many cases, the cursor reference can be returned from the stored procedure to outer calling PL/SQL program (procedure, function or trigger) where it will further be used. When you return a cursor, you can convert it as SQL Server cursor data type. A REF CURSOR is basically a data type in Oracle and a variable of such a type is to be declared. Similarly, you can declare a cursor variable in SQL Server.

Transact-SQL stored procedures can use the cursor data type only for OUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT keywords are required in SQL Server. The following are definitions of an output cursor parameter for both DBMS platforms.

Oracle:
CREATE OR REPLACE PROCEDURE p_cursor_proc
(
pn_dept IN NUMBER,
emp_refcur OUT SYS_REFCURSOR)
IS …

SQL Server:
CREATE PROCEDURE dbo.p_cursor_proc
@pn_dept INT,
@emp_refcur CURSOR VARYING OUTPUT
AS …

Note that Oracle supports the declaration of a cursor variable using SYS_REFCURSOR built-in data type as well as a type of REF CURSOR. Both types are converted the same way. Once a cursor variable is declared, it must be opened with an associated SELECT command. The OPEN FOR statement specifies the SELECT command to be used to create the result set in Oracle. In SQL Server, you should assign a cursor definition to a cursor variable and then open it. Thus, opening of a cursor variable in a procedure body in Oracle will be as follows:

OPEN emp_refcur FOR SELECT emp_code, emp_last_name FROM emp;

The similar code of the corresponding procedure body in Transact-SQL will be, for example:

 

Such a procedure will return a reference to variable with an opened cursor. This variable can be used in any code outside the procedure. Below, I will show how to convert the usage of a cursor variable in another stored procedure which fetches rows from the cursor referenced by the variable and processes them. To refer the cursor, returned by a subprogram, a local variable is declared in Oracle:

emp_refcur SYS_REFCURSOR;

The corresponding local variable which will refer the cursor is declared in SQL Server as follows:

DECLARE @emp_refcur CURSOR

The reference to the opened cursor is received by executing the procedure with the output cursor parameter. For example, to execute the above defined p_cursor_proc procedure, use the following calling code in SQL Server:

EXEC p_cursor_proc @pn_dept = @dept, @emp_refcur = @emp_refcur OUTPUT

After receiving the reference to the cursor, we can operate the cursor variable to fetch rows and we can apply cursor attributes. We can fetch rows from the cursor variable as from a usual cursor. This can be illustrated by the following loop:

Oracle:
FETCH emp_refcur INTO emp_code, emp_last_name;
LOOP
FETCH emp_refcur INTO emp_code, emp_last_name;
EXIT WHEN emp_refcur%NOTFOUND;
END LOOP;

SQL Server:
FETCH NEXT FROM @emp_refcur INTO @emp_code, @emp_last_name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @emp_refcur INTO @emp_code, @emp_last_name
END

As you see, SQL Server @@FETCH_STATUS function can be used to convert Oracle’s %NOTFOUND attribute.

After the cursor usage is finished, the cursor referenced by the cursor variable should be closed. In Oracle, CLOSE command is used for this purpose. In SQL Server, you should first close the cursor using CLOSE < cursor_variable > and then deallocate the memory using DEALLOCATE < cursor_variable > command. For example, you can close the cursor referenced by @emp_refcur variable as follows:

CLOSE @emp_refcur
DEALLOCATE @emp_refcur

There are, however, situations when a select statement for a REF_CURSOR variable is built dynamically. A select statement text is assigned to a string variable and this variable is used in an OPEN … FOR statement. Thus, the procedure that constructs a select statement in Oracle will be as follows:

CREATE PROCEDURE p_cursor_proc
(
pn_dept IN INT,
emp_refcur OUT SYS_REFCURSOR
)
IS
sql_stmt VARCHAR(256);
BEGIN
sql_stmt := ‘SELECT emp_code, emp_last_name FROM emp WHERE dept = ‘ ||
pn_dept;
OPEN emp_refcur FOR sql_stmt;
END;

Thus, a string variable is to be declared and its value should be assigned in SQL Server. The code assigning the select statement in SQL Server will look like:

DECLARE @sql_stmt VARCHAR(256)
SET @sql_stmt = ‘SELECT emp_code, emp_last_name FROM emp WHERE dept = ‘ +
CAST(@pn_dept as VARCHAR(10))

To convert OPEN … FOR statement additional string variable should be declared to build a cursor definition and to open the cursor inside of dynamic SQL. The code assigned as a value of this variable should be executed by means of sp_executesql built-in SQL Server procedure. A cursor variable should be passed as an output parameter to this procedure. The following Transact-SQL code shows the result of conversion of the above OPEN … FOR dynamic statement:

DECLARE @cursor_def_sql NVARCHAR(256)
SET @cursor_def_sql = N’SET @cur = CURSOR FOR ‘ + @sql_stmt + ‘; OPEN @cur’
EXEC sp_executesql
@cursor_def_sql,
N’@cur cursor OUTPUT’,
@emp_refcur OUTPUT

Written by Galina Snurnikova

Tagged as Conversion, cursor, Migration, Oracle, output, parameter, ref cursor, SQL Server, sys_refcursor + Categorized as Newsletters, Newsletter #6, February 3, 2009