CODEWITHSUNDEEP
sqlsyntax-errorsnowflake-cloud-data-platform
Smart003
Smart003

Reputation: 1119

unable to execute the block

can you help me in rectifying the following code.

execute immediate $$
declare
    TABLE_CATALOG varchar default ('DB');
    TABLE_SCHEMA varchar default ('SCH');
    VIEW_NAME varchar default ('VIEW');
  VEW resultset default (SELECT * from table(GET_OBJECT_REFERENCES(DATABASE_NAME =>:TABLE_CATALOG , SCHEMA_NAME =>:TABLE_SCHEMA, OBJECT_NAME =>:VIEW_NAME)));
 begin
return table(VEW);
end;
$$
;

The above code is written in classic web interface

the following error appears when the above executed SQL compilation error: syntax error line 1 at position 21 unexpected ':'.

when executed only the select statement by replacing bind variables with respective parameters query executing without any hiccups.

SELECT * from table(GET_OBJECT_REFERENCES(DATABASE_NAME =>'DB' , SCHEMA_NAME =>'SCH', OBJECT_NAME =>'VIEW'))

Upvotes: 0

Views: 80

Answers (2)

ambeebe
ambeebe

Reputation: 56

While the GET_OBJECT_REFERENCES does expect a string literal, your variables are already strings, as declared by varchar default. The error actually occurs, because the VEW resultset assignment requires a literal query as a parameter or a string (when used with execute immediate). You can achieve this by constructing the query first as shown below:

execute immediate $$
declare
    TABLE_CATALOG varchar default ('DB');
    TABLE_SCHEMA varchar default ('SCH');
    VIEW_NAME varchar default ('VIEW');
    QUERY varchar default  ('SELECT * from table(GET_OBJECT_REFERENCES(DATABASE_NAME =>'||:TABLE_CATALOG||', SCHEMA_NAME =>'||:TABLE_SCHEMA||', OBJECT_NAME =>'||:VIEW_NAME||'))');
    VEW resultset default (execute immediate QUERY);
begin
    return table(VEW);
end;
$$
;

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

Based on GET_OBJECT_REFERENCES the parameters should be string literals.

Non-parametrized version:

DECLARE
    TABLE_CATALOG varchar default ('DB');
    TABLE_SCHEMA  varchar default ('SCH');
    VIEW_NAME     varchar default ('View');
    VEW           resultset ;
    SQL           TEXT DEFAULT 'SELECT * FROM TABLE(GET_OBJECT_REFERENCES(
                                                    DATABASE_NAME=><DATABASE_NAME>,
                                                    SCHEMA_NAME=><SCHEMA_NAME>,
                                                    OBJECT_NAME=><OBJECT_NAME>))';
BEGIN
  -- checking if object exists(input validation)
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
 INTO TABLE_CATALOG, TABLE_SCHEMA, VIEW_NAME
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_CATALOG ILIKE :TABLE_CATALOG
   AND TABLE_SCHEMA ILIKE :TABLE_SCHEMA
   AND TABLE_NAME ILIKE :VIEW_NAME; 
   
 SQL := REPLACE(REPLACE(REPLACE(SQL
         ,'<DATABASE_NAME>', TABLE_CATALOG)
         ,'<SCHEMA_NAME>', TABLE_SCHEMA) 
         ,'<OBJECT_NAME>', VIEW_NAME);

 VEW := (EXECUTE IMMEDIATE :SQL);
 return table(VEW);
END;

Sample data:

CREATE DATABASE DB;
CREATE SCHEMA SCH;
CREATE TABLE Tab1(id INT);
CREATE  OR REPLACE VIEW View AS SELECT * FROM tab1;

Output:

enter image description here

Upvotes: 1

Related Questions