CODEWITHSUNDEEP
sqlstored-proceduressnowflake-cloud-data-platformresultsetcursors
madrarua
madrarua

Reputation: 21

Unknown syntax error with Snowflake SQL procedure

I am writing a Snowflake SQL procedure to drop row access and masking policies from a database once it is cloned.

CREATE or REPLACE PROCEDURE DBMGT.DBADMIN.Q_DROP_MASKING_and_ROP_ON_BKP_DB("sourceDB" varchar, "clonedDB" varchar)
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER 
AS 
$$
DECLARE
    ref_sql VARCHAR;
    rs RESULTSET;
    cmd VARCHAR DEFAULT '''';
    cmd2 VARCHAR DEFAULT '''';
    PATH VARCHAR DEFAULT '''';
    qry VARCHAR default '''';
    s string default '''';
    ts := CONVERT_TIMEZONE('UTC', current_timestamp())::timestamp_ntz;

BEGIN
ref_sql := ''SELECT DISTINCT REF_ENTITY_DOMAIN, REF_SCHEMA_NAME, REF_ENTITY_NAME, REF_COLUMN_NAME
              FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
              WHERE REF_DATABASE_NAME = ?
              AND REF_COLUMN_NAME IS NOT NULL;'';

  --Execute above query passing in bind variables
  rs := (EXECUTE IMMEDIATE :ref_sql USING(sourceDB));

  --Create a CURSOR on the result set above to be able to loop for col masking pols
  LET policy_cur CURSOR FOR rs;

  create or replace temporary table drop_masking_rop_log(sql_stmt varchar);

  --Loop through each row
  FOR ref IN policy_cur DO
    PATH := clonedDB  || ''.'' || ref.REF_SCHEMA_NAME || ''.'' || ref.REF_ENTITY_NAME;
    cmd := ''ALTER '' || ref.REF_ENTITY_DOMAIN || '' '' || PATH || '' MODIFY COLUMN '' || ref.REF_COLUMN_NAME || '' UNSET MASKING POLICY;''; --unset column masking pols

cmd2 :=  ''ALTER '' || ref.REF_ENTITY_DOMAIN || '' '' || PATH || '' DROP ALL ROW ACCESS POLICIES;''; --unset row masking pols

INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES (:cmd, :ts); 
INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES(:cmd2, :ts); 
INSERT INTO drop_masking_rop_log VALUES (:cmd);
INSERT INTO drop_masking_rop_log VALUES (:cmd2);



BEGIN
            execute immediate cmd;
        EXCEPTION WHEN OTHER THEN
                LET LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                insert into DBMGT.DBADMIN.POLICYLOG values (:s , :ts);
                insert into drop_masking_rop_log values (:s);
        END;   


BEGIN
            execute immediate cmd2;
        EXCEPTION WHEN OTHER THEN
                LET LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                insert into DBMGT.DBADMIN.POLICYLOG values (:s , :ts);
                insert into drop_masking_rop_log values (:s);
        END;   

end for;

rs := (select * from drop_masking_rop_log);
return table(rs);



END;
$$
;

I get an error when I try to create the procedure:

Syntax error: unexpected ':='. (line 25)
Syntax error line 13 at position 46 unexpected ','. (line 25)

No idea why this is happening, I can't identify any syntax errors myself. A previous version of this script which ran within another procedure worked fine, very confused about why this error is occurring, any help is much appreciated.

I have referred back to previous working versions of the script and tried to ensure that there are no obvious syntax errors, still can't figure out what the issue is here..

Upvotes: 0

Views: 100

Answers (1)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1616

I removed the double quote and add ";" at one place and was able to compile it.

CREATE or REPLACE PROCEDURE Q_DROP_MASKING_and_ROP_ON_BKP_DB("sourceDB" varchar, "clonedDB" varchar)
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER 
AS 
$$
DECLARE
    ref_sql VARCHAR;
    rs RESULTSET;
    cmd VARCHAR DEFAULT '''';
    cmd2 VARCHAR DEFAULT '''';
    PATH VARCHAR DEFAULT '''';
    qry VARCHAR default '''';
    s string default '''';
    ts := CONVERT_TIMEZONE('UTC', current_timestamp())::timestamp_ntz;
BEGIN

ref_sql := 'SELECT DISTINCT REF_ENTITY_DOMAIN, REF_SCHEMA_NAME, REF_ENTITY_NAME, REF_COLUMN_NAME '
            || '  FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES '
            || '  WHERE REF_DATABASE_NAME = ? '
            || '  AND REF_COLUMN_NAME IS NOT NULL;';
  --Execute above query passing in bind variables
  rs := (EXECUTE IMMEDIATE :ref_sql USING(sourceDB));

  --Create a CURSOR on the result set above to be able to loop for col masking pols
  LET policy_cur CURSOR FOR rs;
  create or replace temporary table drop_masking_rop_log(sql_stmt varchar);

  --Loop through each row
  FOR ref IN policy_cur DO
    PATH :=  clonedDB  || '.' || ref.REF_SCHEMA_NAME || '.' || ref.REF_ENTITY_NAME;
    cmd := 'ALTER ' || ref.REF_ENTITY_DOMAIN || '  || ' || ' PATH ' || ' MODIFY COLUMN ' 
    || ref.REF_COLUMN_NAME || ' UNSET MASKING POLICY;'; --unset column masking pols;

cmd2 :=  'ALTER ' || ref.REF_ENTITY_DOMAIN || ' ' || PATH || ' DROP ALL ROW ACCESS POLICIES;'; 
--unset row masking pols

INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES (:cmd, :ts); 
INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES(:cmd2, :ts); 
INSERT INTO drop_masking_rop_log VALUES (:cmd);
INSERT INTO drop_masking_rop_log VALUES (:cmd2);



BEGIN
            execute immediate cmd;
        EXCEPTION WHEN OTHER THEN
                LET LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                insert into DBMGT.DBADMIN.POLICYLOG values (:s , :ts);
                insert into drop_masking_rop_log values (:s);
        END;   


BEGIN
            execute immediate cmd2;
        EXCEPTION WHEN OTHER THEN
                LET LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                insert into DBMGT.DBADMIN.POLICYLOG values (:s , :ts);
                insert into drop_masking_rop_log values (:s);
        END;   

end for;

rs := (select * from drop_masking_rop_log);
return table(rs);



END;
$$
;

Upvotes: 1

Related Questions