execute immediate with $$

Question

I have a long query that returns 1 column and 1 row:

Query 1:

select test_query from (
SELECT 
     LISTAGG('...') ... AS xx,
     LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
     .....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx);

The output looks like this. It has a query that I want to run. Note that it consists of quotation marks. The original query 1 also has quotation marks in the LISTAGG.

output:

test_query   
select "col1", "col2" from stg.new          

I wanted to extend the Query A such that I can select and run the test_query and return the final output of the test_query instead of queryA. I tried using execute immediate with $$:

execute immediate 
$$select test_query from (
SELECT 
     LISTAGG('...') ... AS xx,
     LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
     .....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx)$$;

but instead of running the test_query it outputs the same thing as Query1.

What else can I try?

Answer 1

Here's an attempted conversion of your SQL template. You can extend it to add the entire SQL statement:

execute immediate
  $$
    declare 
         rs resultset default (
select test_query from (
SELECT 
     LISTAGG('...') ... AS xx,
     LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
     .....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx);
         );
         c cursor for rs;
         sqlStatement string;
         rsFinal resultset;
    begin
         for rowContents in c do
            sqlStatement := rowContents.SQL_STATEMENT;
         end for;
         rsFinal := (execute immediate :sqlStatement);
         return table(rsFinal);
    end;
  $$
  ;

In case that's not working because of context, etc., here is a completely self-contained sample that's working:

create or replace transient table T1 as
select 'Hello world' as s;

create or replace transient table SQL_TO_RUN as
select 'select S from T1 SQL_STATEMENT' SQL_STATEMENT
;

execute immediate
  $$
    declare 
         rs resultset default (select SQL_STATEMENT from SQL_TO_RUN);
         c cursor for rs;
         sqlStatement string;
         rsFinal resultset;
    begin
         for rowContents in c do
            sqlStatement := rowContents.SQL_STATEMENT;
         end for;
         rsFinal := (execute immediate :sqlStatement);
         return table(rsFinal);
    end;
  $$
  ;

Output:

S
Hello world