CODEWITHSUNDEEP
snowflake-cloud-data-platform
Raja Gudipudi
Raja Gudipudi

Reputation: 37

Execution error in stored procedure in Snowflake

I compiled a stored procedure but I'm unable to execute it - getting this error:

Execution error in stored procedure SAMPLE_ETL_MONITORING_PROC: Stored procedure execution error: Requested information on the current user is not accessible in stored procedure. At Statement.execute, line 18 position 45

I have write access to the database but not sure If I have to include any commands/statements in the script. Can anyone please suggest what needs to be done?

Here is the script:

CREATE OR REPLACE PROCEDURE sample_etl_monitoring_proc()
returns string not null
language javascript
as
$$
var insert_cmd = `
truncate table OOBE.monitoring.load_history_1

`
var sql_insert = snowflake.createStatement({sqlText: insert_cmd});
var insert_result = sql_insert.execute();

var stream_select_cmd = `
insert into OOBE.monitoring.load_history_1
select * from (
select * from OOBE.information_schema.load_history
union
select * from snowplow.information_schema.load_history);

`
var sql_select_stream = snowflake.createStatement({sqlText: stream_select_cmd});
var select_stream_result = sql_select_stream.execute();
return '👍';
$$;

Upvotes: 1

Views: 1745

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11086

I reproduced the error and got this to work by adding an explicit caller's right declaration:

CREATE OR REPLACE PROCEDURE sample_etl_monitoring_proc()
returns string not null
language javascript
execute as caller
as

Upvotes: 1

Related Questions