SnowFlake: Access rights in SQL vs. procedures

Question

I have the following initial situation in SnowFlake: I want to retrieve the NextVal of a sequence in a procedure and get the following error message when calling the procedure:

Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 29 : SQL compilation error: error line 1 at position 7 invalid identifier 'COMMON.SEQ_LAUF_ID'.

A select with the same user/role works fine:

Example SQL:

 SELECT COMMON.SEQ_LAUF_ID as nv FROM DUAL ;

Example Proc:

create or replace procedure MDP_Codes ( )
returns varchar
language sql
EXECUTE AS CALLER
as
$$
DECLARE
    rs   resultset default ( SELECT COMMON.SEQ_LAUF_ID as nv FROM DUAL ) ;
    hCur cursor for rs ;
    SQL_lauf_id varchar ;
BEGIN
    for hRow in hCur do
      SQL_lauf_id := hRow.nv ;
    end for;
    
    return SQL_lauf_id ;
END;
$$
;

call APPSMITH.MDP_Codes();

Ich habe auch nocht ein 'EXECUTE AS CALLER' in die Procrdure eingefügt um die rechte des aufrufers zu nutzen. Das Ergebnis ist das Gleiche: — UMinky, Apr 19 '22 at 13:13

Lukasz Szozda · Answer 1 · 2022-04-19T13:58:45.977

The code SELECT COMMON.SEQ_LAUF_ID as nv FROM DUAL ; is incorrect as it lacks NEXTVAL

SELECT COMMON.SEQ_LAUF_ID as nv FROM DUAL ; 
-- Invalid reference to sequence object without nextval: COMMON.SEQ_LAUF_ID.

-- should be
SELECT COMMON.SEQ_LAUF_ID.NEXTVAL as nv FROM DUAL ;

To grab the next value from sequence there is no need to use RESULTSET and cursor loop, simple assignment will suffice.

Full demo:

CREATE SCHEMA COMMON;
CREATE SEQUENCE COMMON.SEQ_LAUF_ID;

SELECT COMMON.SEQ_LAUF_ID.NEXTVAL;
 -- 1
 
 create or replace procedure MDP_Codes ( )
returns varchar
language sql
EXECUTE AS CALLER
as
$$
DECLARE
    SQL_lauf_id VARCHAR;
BEGIN
    SQL_lauf_id := (SELECT COMMON.SEQ_LAUF_ID.NEXTVAL);
    return SQL_lauf_id ;
END;
$$
;

CALL MDP_Codes();
-- MDP_CODES
-- 2

SnowFlake: Access rights in SQL vs. procedures

1 Answers1