How to convert Oracle cursor when migrating to SQL Server

Oracle 9i introduced the predefined SYS_REFCURSOR type meaning we no longer have to define our own REF CURSOR types. SQL Server doesn’t support SYS_REFCURSOR type and doesn’t have a direct equivalent for it. Thus, SQL Server Migration Assistant (SSMA) for Oracle converts SYS_REFCURSOR variables into varchar (8000) and generates the appropriate warning message: «Warning O2SS0259: Cursor type was converted to varchar».

Oracle’s SYS_REFCURSOR type allows returning recordsets from stored procedures and functions. For example, a procedure can open a cursor and use a ref cursor to return a subset of the records to the caller procedure or anonymous PL/SQL block.

SSMA cannot convert a cursor or cursor variable as a function or procedure call output parameter and generates the following error message: «Error O2SS0264: Unable to convert cursor or cursor variable as a function or procedure call parameter». This blog post covers how you can address this issue.

Check out the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DECLARE
v_cursor SYS_REFCURSOR;
v_id INT;
v_name VARCHAR(100);
BEGIN
TEST_FUNCTIONS.P_RETURN_RECORD_SET (p_id =&amp;gt; 1, p_recordset =&amp;gt; v_cursor);
LOOP
FETCH v_cursor
INTO v_id, v_name;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' | ' || v_name);
END LOOP;
CLOSE v_cursor;
END;

SSMA converts this source Oracle code to the following code with the warning message.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE PROCEDURE dbo.P_RETURN_RECORD_SET
@p_id INT,
/*
* SSMA warning messages:
* O2SS0259: CURSOR type was converted to varchar(8000). */
@p_recordset VARCHAR(8000) OUTPUT
AS
BEGIN
SET @p_recordset = NULL
SELECT T_FK_CONSTR_DIFF_DTTPS_1.ID, T_FK_CONSTR_DIFF_DTTPS_1.NAME
FROM dbo.T_FK_CONSTR_DIFF_DTTPS_1
WHERE T_FK_CONSTR_DIFF_DTTPS_1.ID = @p_id
ORDER BY T_FK_CONSTR_DIFF_DTTPS_1.NAME
END
GO

This warning message should be considered on the next step, which includes the conversion of the call of the above-mentioned procedure.

Possible Remedies

So, now let’s try to convert the statement that includes the call of the P_RETURN_RECORD_SET procedure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

To solve the O2SS0264 error, you should perform the following actions.

1) Declare table variable or create temporary table with the same structure as cursor.

1 2	DECLARE @v_tab TABLE (v_id INT, v_name VARCHAR(100))

Also, you should declare the auxiliary variable for output procedure parameter as far as SSMA converted the CURSOR type on the previous step to varchar(8000).

1	@v_cursor_out VARCHAR(MAX)

2) Uncomment procedure call and add

1	INSERT INTO @v_tab

clause before

EXECUTE…

3) Assign the dataset to cursor variable.

4) Once the batch with a local cursor variable expires, SQL Server destroys the variable and automatically closes and deallocates the cursor. So you could simply remove two following lines:

1 2	CLOSE @v_cursor DEALLOCATE @v_cursor

Thus, the updated SQL Server code will look as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

BEGIN
DECLARE
@v_cursor CURSOR,
@v_id INT,
@v_name VARCHAR(100),
@v_cursor_out VARCHAR(MAX)
DECLARE
@v_tab TABLE (v_id INT, v_name VARCHAR(100))
/* SSMA error messages:
* O2SS0264: Unable to convert cursor or cursor variable as a function
* or procedure call parameter.*/
INSERT INTO @v_tab
EXECUTE dbo.P_RETURN_RECORD_SET @P_ID = 1, @P_RECORDSET = @v_cursor_out OUTPUT
SET @v_cursor = CURSOR FAST_FORWARD FOR
SELECT * FROM @v_tab
OPEN @v_cursor
WHILE 1 = 1
BEGIN
FETCH @v_cursor
INTO @v_id, @v_name
IF @@FETCH_STATUS &amp;lt;&amp;gt; 0
BREAK
PRINT ISNULL(CAST(@v_id AS nvarchar(MAX)), '') + ' | ' + ISNULL(@v_name, '')
END
END
GO

Do you have other questions or concerns about SSMA or Oracle to SQL Server migration issues? Make sure to check out our new Jumpstart for SSMA offer!

Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.

Source of technical information: Oksana Eremenko, Technical Lead at DB Best.

Blog: Oracle to SQL Server migration with SSMA: How to convert cursor or cursor variable as a function or procedure call parameter

Possible Remedies

Related posts

Getting started