Workaround for cursors using parameters or procedure variables
Description
This section describes how to simulate the usage of cursor parameters and procedure variables inside the query of a cursor. The name of the variables or parameters is replaced with bindings using the ? sign. Then, when the cursor is opened, the values should be passed with the USING clause.
Some parts in the output code are omitted for clarity reasons.
Cursor with local variables
Use bindings for the query in the cursor for variable or procedure parameter used and open the cursor with the USING clause.
IN -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE oracle_cursor_sample
AS
like_value VARCHAR(255);
CURSOR c1 IS SELECT region_name FROM hr.regions WHERE region_name LIKE like_value ORDER BY region_name;
r_name VARCHAR(255);
BEGIN
like_value := 'E%';
OPEN c1;
FETCH c1 INTO r_name;
CLOSE c1;
like_value := 'A%';
FOR r1 IN c1 LOOP
NULL;
END LOOP;
END;
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE oracle_cursor_sample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
like_value VARCHAR(255);
c1 CURSOR
FOR
SELECT
OBJECT_CONSTRUCT('REGION_NAME', region_name) sc_cursor_record FROM
hr.regions
WHERE region_name LIKE ?
ORDER BY region_name;
r_name VARCHAR(255);
BEGIN
like_value := 'E%';
OPEN c1 USING (like_value);
FETCH c1 INTO
:r_name;
CLOSE c1;
like_value := 'A%';
OPEN c1 USING (like_value);
--** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
FOR r1 IN c1 DO
LET r1 OBJECT := r1.sc_cursor_record;
NULL;
END FOR;
CLOSE c1;
END;
$$;
Cursor with parameters
Use bindings for the query in the cursor for each parameter used and open the cursor with the USING clause. Keep in mind that a parameter that is used multiple times on a single cursor may require passing the variable multiple times in the USING clause.
IN -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE parametrized_cursor_sample AS
CURSOR cursor1(param1 number) IS SELECT region_name FROM hr.regions where region_id = param1 ORDER BY region_name;
var1 integer;
BEGIN
OPEN cursor1(123);
FETCH cursor1 INTO var1;
CLOSE cursor1;
FOR r1 IN cursor1(456) LOOP
NULL;
END LOOP;
END;
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE parametrized_cursor_sample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
cursor1 CURSOR
FOR
SELECT
OBJECT_CONSTRUCT('REGION_NAME', region_name) sc_cursor_record FROM
hr.regions
where region_id = param1 ORDER BY region_name;
var1 integer;
BEGIN
OPEN cursor1;
FETCH cursor1 INTO
:var1;
CLOSE cursor1;
OPEN cursor1;
--** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
FOR r1 IN cursor1 DO
LET r1 OBJECT := r1.sc_cursor_record;
NULL;
END FOR;
CLOSE cursor1;
END;
$$;
