Cursor Variables

Translation reference for cursor variables and the OPEN FOR statement

Some parts in the output code are omitted for clarity reasons.

Description

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

(Oracle PL/SQL Language Reference Cursor Variable Declaration)

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 ] ;

Sample Source Patterns

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

IN -> Oracle_01.sql
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;
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		query1 VARCHAR(200) := 'SELECT 123 FROM dual';
		cursor_var_res RESULTSET;
	BEGIN
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - 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.

IN -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE procedure2
AS
    cursor_var SYS_REFCURSOR;
BEGIN
    OPEN cursor_var FOR 'SELECT 123 FROM dual';
    CLOSE cursor_var;
END;
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE procedure2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        cursor_var_res RESULTSET;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - 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

IN -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE procedure3
AS
	cursor_var SYS_REFCURSOR;
BEGIN
	OPEN cursor_var FOR SELECT 123 FROM dual;
	CLOSE cursor_var;
END;
OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE procedure3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		cursor_var_res 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

IN -> Oracle_04.sql
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;
OUT -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE procedure4 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        --** SSC-FDM-0024 - 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;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - 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

IN -> Oracle_05.sql
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;
OUT -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE procedure5 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        query1 VARCHAR(200) := 'SELECT col1 FROM
   cursortable1
WHERE col1 = ?';
        column_filter INTEGER := 1;
        cursor_var_res RESULTSET;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - 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.

Last updated