Cursor Variables

Translation reference for cursor variables and the OPEN FOR statement

Description

A cursor variable is like an explicit cursor that is not limited to one query.

(Oracle PL/SQL Language Reference Cursor Variable Declaration)

Oracle Syntax

Ref cursor type definition

TYPE type IS REF CURSOR
  [ RETURN
    { {db_table_or_view | cursor | cursor_variable}%ROWTYPE
    | record%TYPE
    | record_type
    | ref_cursor_type
    }
  ] ;

Cursor variable declaration

cursor_variable type;

OPEN FOR statement

OPEN { cursor_variable | :host_cursor_variable}
  FOR select_statement [ using_clause ] ;

Snowflake Scripting has no direct equivalence with cursor variables and the OPEN FOR statement, however, they can be emulated with different workarounds to get functional equivalence.

Sample Source Patterns

1. OPEN FOR statement with dynamic SQL inside a VARCHAR variable

Oracle Example

CREATE OR REPLACE PROCEDURE procedure1
AS
	query1 VARCHAR(200) := 'SELECT 123 FROM dual';
	cursor_var SYS_REFCURSOR;
BEGIN 
	OPEN cursor_var FOR query1;
	CLOSE cursor_var;
END;

Snowflake Scripting Exaple

CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	DECLARE
		query1 VARCHAR(200) := 'SELECT 123 FROM dual';
		cursor_var_res RESULTSET /*** MSC-WARNING - MSCEWI1036 - SYS_REFCURSOR DATA TYPE CONVERTED TO RESULTSET ***/;
	BEGIN
		--** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
		cursor_var_res := (
			EXECUTE IMMEDIATE :query1
		);
		LET cursor_var CURSOR
		FOR
			cursor_var_res;
		OPEN cursor_var;
		CLOSE cursor_var;
	END;
$$;

2. OPEN FOR statement with dynamic SQL inside a string literal.

Oracle Example

CREATE OR REPLACE PROCEDURE procedure2
AS
	cursor_var SYS_REFCURSOR;
BEGIN
	OPEN cursor_var FOR 'SELECT 123 FROM dual';
	CLOSE cursor_var;
END;

Snowflake Scripting Example

CREATE OR REPLACE PROCEDURE procedure2 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	DECLARE
		cursor_var_res RESULTSET /*** MSC-WARNING - MSCEWI1036 - SYS_REFCURSOR DATA TYPE CONVERTED TO RESULTSET ***/;
	BEGIN
		--** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
		cursor_var_res := (
			EXECUTE IMMEDIATE 'SELECT 123 FROM dual'
		);
		LET cursor_var CURSOR
		FOR
			cursor_var_res;
		OPEN cursor_var;
		CLOSE cursor_var;
	END;
$$;

3. OPEN FOR statement with SELECT statement

Oracle Example

CREATE OR REPLACE PROCEDURE procedure3
AS
	cursor_var SYS_REFCURSOR;
BEGIN
	OPEN cursor_var FOR SELECT 123 FROM dual;
	CLOSE cursor_var;
END;

Snowflake Scripting Example

CREATE OR REPLACE PROCEDURE procedure3 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	DECLARE
		cursor_var_res RESULTSET /*** MSC-WARNING - MSCEWI1036 - SYS_REFCURSOR DATA TYPE CONVERTED TO RESULTSET ***/;
	BEGIN
		LET cursor_var CURSOR FOR SELECT 123 FROM dual;
		OPEN cursor_var;
		CLOSE cursor_var;
	END;
$$;

4. Cursor Variable declared with REF CURSOR type

Oracle Example

CREATE OR REPLACE PROCEDURE procedure4
AS
    TYPE cursor_ref_type1 IS REF CURSOR;
	query1 VARCHAR(200) := 'SELECT 123 FROM dual';
	cursor_var cursor_ref_type1;
BEGIN 
	OPEN cursor_var FOR query1;
	CLOSE cursor_var;
END;

Snowflake Scripting Example

CREATE OR REPLACE PROCEDURE procedure4 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL REF CURSOR TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--        TYPE cursor_ref_type1 IS REF CURSOR;
        query1 VARCHAR(200) := 'SELECT 123 FROM dual';
        cursor_var_res RESULTSET /*** MSC-WARNING - MSCEWI1036 - cursor_ref_type1 DATA TYPE CONVERTED TO RESULTSET ***/;
    BEGIN
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        cursor_var_res := (
            EXECUTE IMMEDIATE :query1
        );
        LET cursor_var CURSOR
        FOR
            cursor_var_res;
        OPEN cursor_var;
        CLOSE cursor_var;
    END;
$$;

5. OPEN FOR statement with USING clause

Oracle Example

CREATE OR REPLACE PROCEDURE procedure5
AS
	query1 VARCHAR(200) := 'SELECT col1 FROM cursortable1 WHERE col1 = :a';
    	column_filter INTEGER := 1;
	cursor_var SYS_REFCURSOR;
BEGIN 
	OPEN cursor_var FOR query1 USING column_filter;
	CLOSE cursor_var;
END;

Snowflake Scripting Example

CREATE OR REPLACE PROCEDURE procedure5 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	DECLARE
		query1 VARCHAR(200) := 'SELECT col1 FROM
   cursortable1
WHERE col1 = ?';
		   column_filter INTEGER := 1;
		cursor_var_res RESULTSET /*** MSC-WARNING - MSCEWI1036 - SYS_REFCURSOR DATA TYPE CONVERTED TO RESULTSET ***/;
	BEGIN
		--** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
		cursor_var_res := (
			EXECUTE IMMEDIATE :query1 USING ( column_filter)
		);
		LET cursor_var CURSOR
		FOR
			cursor_var_res;
		OPEN cursor_var;
		CLOSE cursor_var;
	END;
$$;

Known Issues

No issues were found.

Related EWIs

1. MSCEWI3133: Cursor variable has already been assigned.

2. MSCEWI3109: Expressions as arguments of Using Clause are not supported by Snowflake Scripting.

3. MSCEWI1036: Data type converted to another data type.

4. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.

5. MSCCP0004: The statement below has usages of dynamic SQL.