unable to execute the block-CodePudding

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 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'))

CodePudding user response：

Based on

CodePudding user response：

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;
$$
;