Can I use a dynamic query inside a with statement-CodePudding

I have a string which represents a query. For example: query varchar2(100):='select 1 from dual' .

I would like to execute this query and reuse it inside a with statement like this

with from_dynamic_query as(
   execute immediate query
) select count(*) from from_dynamic_query;

is it possible? and what is the syntax?

CodePudding user response：

Dynamic SQL is a PL/SQL thing, so - yes, you can do that, but not exactly as you put it:

SQL> set serveroutput on
SQL> declare
  2    query varchar2(200) := 'select 1 from dual';
  3    l_str varchar2(1000);
  4    l_cnt number;
  5  begin
  6    l_str := 'with from_dynamic_query as (' ||
  7             query ||
  8             ') select count(*) from from_dynamic_query';
  9
 10    execute immediate l_str into l_cnt;
 11
 12    dbms_output.put_line('result = ' || l_cnt);
 13  end;
 14  /
result = 1

PL/SQL procedure successfully completed.

SQL>

If you want to - at least partially - skip PL/SQL, declare a function within a WITH factoring clause (but it'll still have to be dynamic):

SQL> with
  2    function f_test return number
  3    is
  4      query varchar2(100) := 'select 1 from dual';
  5      l_str varchar2(1000);
  6      l_cnt number;
  7    begin
  8      l_str := 'with from_dynamic_query as (' ||
  9               query ||
 10               ') select count(*) from from_dynamic_query';
 11      execute immediate l_str into l_cnt;
 12      return l_cnt;
 13    end;
 14  select f_test
 15  from dual;
 16  /

    F_TEST
----------
         1

SQL>

CodePudding user response：

it works for Oracle 21

with FUNCTION f_test return varchar2 SQL_MACRO is
  query VARCHAR2(100) := 'select 1 from dual';
  ret   VARCHAR2(100) := chr(13) || query || chr(13);
BEGIN
  RETURN ret;
END;
select count(*) from f_test()

code