Is it possible to use the Oracle sql WITH CLAUSE on multiple unrelated queries within the same file? -

i have sql script generates report through sql plus. report generated running multiple unrelated queries 1 after other. of queries have common code try , define once. example, assume report generation file contains following 2 scripts

select a, b, c x in (     select x     d x '%abc%' ) /  select a, d, y z in (     select x     d x '%abc%' ) / 

the 2 queries different both use same subquery. avoid having rewrite subquery each independent query.

the clause can used not sure how use on 2 different queries

with t1 (  select x  d x '%abc%' ) select a, b, c x, t1 t1.x = x.a 

the above resolves first query. how can use same statement second query? or there alternative approach achieve this?

edit

one other option use refcursors. might work might tedious build strings each query , concatenate generic string query. plsql block, difficult generate headers between different queries.

declare     v_sql varchar(2000); begin     v_sql := 'select 1 dual';     dbms_output.put_line(v_sql);     open :x v_sql; end; / print x; 

thanks

you cannot use common table expression (cte) multiple queries. functionality single query.

i recommend view or temporary table.