QuestionQ63

Implement Data Pipelines

The following stored procedure was created to load orders for one date into a target table:

create or replace procedure uspLoadOrdersByDate ()  
returns string  
language javascript  
execute as owner  
as  
'  
sqlCmd = `INSERT INTO ORDERS_BY_DATE_TGT SELECT * FROM ORDERS_BY_DATE WHERE O_ORDERDATE = $dateToProcess`; sqlStmt = snowflake.createStatement(\{sqlText: sqlCmd\}); res = sqlStmt.execute(); res.next(); return res.GetColumnValue(1);  
';  

After executing these statements, the following error is returned:

set dateToProcess = '2020-08-02';  
call uspLoadOrdersByDate();  
Execution error in store procedure USPLOADORDERSBYDATE: Use of session variable '$DATETOPROCESS' is not allowed in owners rights stored procedure At Statement.execute, line 4 position 14  

What changes will allow the procedure to execute successfully?

Choose two
Explanation

An owner’s-rights stored procedure cannot access SQL/session variables created in the caller’s session. A caller’s-rights procedure can read the caller’s session variables, while an owner’s-rights procedure can receive the needed value through an explicit procedure argument rather than reading the session variable directly. Snowflake: Understanding caller’s rights and owner’s rights stored procedures

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!