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

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

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

SQLCODE and SQLERRM are converted into helper variables with the same name and are bound in the same way as the cursor variables.

Known Issues

No issues were found.

Related EWIs

No related EWIs.