Implicit Cursor attribute helper
An implicit cursor has attributes that return information about the most recently run SELECT or DML statement. This section explains how this is being emulated using JavaScript.
Overview
These are the attributes that you can use inside Snowflake stored procedures using this helper:
FOUND
NOTFOUND
ROWCOUNT
ISOPEN
In Snowflake code, inside the procedures, you will find the initialization of these attributes:
var SQL = {
FOUND : false,
NOTFOUND : false,
ROWCOUNT : 0,
ISOPEN : false
};
The values of the attributes: FOUND, NOTFOUND, ROWCOUNT are updated inside the EXEC helper.
The attribute ISOPEN is always false, just like in Oracle.
Usage Samples
Input
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1
IS
VAR1 VARCHAR(100) := '';
BEGIN
SELECT COL1 INTO VAR1 FROM TABLE1 WHERE COL1 = 1;
VAR1 := 'Rows affected: ' || TO_CHAR(SQL%ROWCOUNT);
VAR1 := 'Error: ' || SQLERRM;
PKG.TEST_PROC1(SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND);
PKG.TEST_PROC2(SQLCODE);
SELECT SQL%ROWCOUNT FROM DUAL;
END;
Output
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let VAR1 = undefined;
[VAR1] = EXEC(`SELECT
COL1
FROM
TABLE1
WHERE COL1 = 1`);
VAR1 = `Rows affected: ${concatValue((EXEC(`SELECT
TO_CHAR(?)`,[SQL.ROWCOUNT]))[0])}`;
VAR1 = `Error: ${concatValue(SQLERRM)}`;
EXEC(`CALL
PKG.TEST_PROC1(?, ?, ?)`,[SQL.ROWCOUNT,SQL.FOUND,SQL.NOTFOUND]);
EXEC(`CALL
PKG.TEST_PROC2(?)`,[SQLCODE]);
EXEC(`SELECT
?
FROM DUAL`,[SQL.ROWCOUNT]);
$$;
EWIs Related
SSC-FDM-0007: Element with missing dependencies.
Last updated