SSC-EWI-OR0133

Cursor variable has already been assigned

Severity

Medium

Description

When an OPEN FOR statement is converted, a cursor assignment with the same name as the cursor variable used in the input code is added along with other statements to emulate its functionality. Since it is possible to use multiple OPEN FOR statements with the same cursor variable, there will be multiple cursor assignments with the same name in the output code. Leaving the output code as it is will cause compilation errors when executed in Snowflake.

Example code

Input code

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE open_for_procedure
AS 
	query1 VARCHAR(200) := 'SELECT 123 FROM dual';
	query2 VARCHAR(200) := 'SELECT 456 FROM dual';
	my_cursor_variable SYS_REFCURSOR;
BEGIN
	OPEN my_cursor_variable FOR query1;
	OPEN my_cursor_variable FOR query2;
END;

Output code

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE open_for_procedure ()
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';
		query2 VARCHAR(200) := 'SELECT 456 FROM dual';
		my_cursor_variable_res RESULTSET;
	BEGIN
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
		my_cursor_variable_res := (
			EXECUTE IMMEDIATE :query1
		);
		LET my_cursor_variable CURSOR
		FOR
			my_cursor_variable_res;
		OPEN my_cursor_variable;
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
		my_cursor_variable_res := (
			EXECUTE IMMEDIATE :query2
		);
		!!!RESOLVE EWI!!! /*** SSC-EWI-OR0133 - THE CURSOR VARIABLE NAMED 'my_cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR ***/!!!
		LET my_cursor_variable CURSOR
		FOR
			my_cursor_variable_res;
		OPEN my_cursor_variable;
	END;
$$;

Related EWI

1. SSC-EWI-0030: The statement below has usages of dynamic SQL.

Recommendations

• To solve the compilation errors of the output code the cursor assignments that have the SSC-EWI-OR0133 message should be renamed.

• If you need more support, you can email us at [e-mail]