I want to generate a WITH clause/UNIONs — for the purpose of easily sharing small samples of data (10-20 rows).

I want to do that without creating tables or inserting rows.

---

Example:

Take a table or query like this:

...and generate this:

with cte as(
select 10 as asset_id, 1 as vertex_num, 118.56 as x, 3.8 as y from dual
union all
select 10 as asset_id, 2 as vertex_num, 118.62 as x, 1.03 as y from dual
union all
select 10 as asset_id, 3 as vertex_num, 121.93 as x, 1.03 as y from dual)

 --There are lots more rows. But it's too much work to write them all out.
   
select * from cte

Using SQL, how can I automatically generate a WITH clause/UNIONs from the resultset?

• I believe there is OOTB export functionality in Toad that can do that. But I don't think there are any tools in SQL Developer that can do it, which is what I'm using.
• When attempting this with SQL, I think the main challenge is to loop through n columns. I'm not sure how to do that.

CodePudding user response：

It would be easier to use xmltable or (json_table for Oracle 12 ) for such purposes.

Example with xmltable:

1. Just aggregate all the required data into xmltype: you can use xmltype(cursor(select...from...)):

select xmltype(cursor(select * from test)) xml from dual;

or dbms_xmlgen.getxmltype(query_string):

select dbms_xmlgen.getxmltype('select * from test') xml from dual;

2. then you can use the returned XML with

xmltable('/ROWSET/ROW' passing xmltype(your_xml) columns ...)

Example:

select *
from xmltable(
        '/ROWSET/ROW'
        passing xmltype(q'[<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ASSET_ID>10</ASSET_ID>
  <VERTEX_NUM>1</VERTEX_NUM>
  <X>118.56</X>
  <Y>3.8</Y>
 </ROW>
 <ROW>
  <ASSET_ID>10</ASSET_ID>
  <VERTEX_NUM>2</VERTEX_NUM>
  <X>118.62</X>
  <Y>1.03</Y>
 </ROW>
</ROWSET>
        ]')
    columns 
        asset_id,vertex_num,x,y
) test

Full example on DBFiddle: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=036b718f2b18df898c3e3de722c97378

CodePudding user response：

You could use dbms_sql to execute a query against your real table, interrogate the data types, and use that information to generate the CTE and its inner queries.

As a first stab:

create or replace procedure print_cte (p_statement varchar2) as
  -- dbms_sql variables
  l_c pls_integer;
  l_col_cnt pls_integer;
  l_rows pls_integer;
  l_desc_t dbms_sql.desc_tab;

  l_first_row boolean := true;
  l_varchar2 varchar2(4000);
  l_number number;
  l_date date;
  -- etc.
begin
  -- ideally add some checks for p_statement being a sinple query

  l_c := dbms_sql.open_cursor;
  dbms_sql.parse(c => l_c, statement => p_statement, language_flag => dbms_sql.native);
  l_rows := dbms_sql.execute(c => l_c);

  dbms_sql.describe_columns(c => l_c, col_cnt => l_col_cnt, desc_t => l_desc_t);

  -- define columns, and output CTE columns at the same time
  dbms_output.put('with cte (');

  for i in 1..l_col_cnt loop
    case l_desc_t(i).col_type
      when 1 then
        dbms_sql.define_column(c => l_c, position=> i, column => l_varchar2, column_size => 4000);
      when 2 then
        dbms_sql.define_column(c => l_c, position=> i, column => l_number);
      when 12 then
        dbms_sql.define_column(c => l_c, position=> i, column => l_date);
      -- etc. plus else to skip or throw error for anything not handled
    end case;

    if i > 1 then
      dbms_output.put(', ');
    end if;
    dbms_output.put('"' || l_desc_t(i).col_name || '"');
  end loop;

  dbms_output.put(') as (');

  while dbms_sql.fetch_rows(c => l_c) > 0 loop
    if (l_first_row) then
      l_first_row := false;
    else
      dbms_output.put('  union all');
    end if;
    dbms_output.new_line;
 
    for i in 1..l_col_cnt loop
      if i = 1 then
        dbms_output.put('  select ');
      else
        dbms_output.put(', ');
      end if;
 
      case l_desc_t(i).col_type
        when 1 then
          dbms_sql.column_value(c => l_c, position => i, value => l_varchar2);
          dbms_output.put(q'[']' || l_varchar2 || q'[']');
        when 2 then
          dbms_sql.column_value(c => l_c, position => i, value => l_number);
          dbms_output.put(l_number);
        when 12 then
          dbms_sql.column_value(c => l_c, position => i, value => l_date);
          dbms_output.put(q'[to_date(']'
            || to_char(l_date, 'SYYYY-MM-DD-HH24:MI:SS')
            || q'[', 'SYYYY-MM-DD HH24:MI:SS')]');
        -- etc. plus else to skip or throw error for anything not handled
     end case;
    end loop;

    dbms_output.put(' from dual');
    dbms_output.new_line;
  end loop;

  dbms_output.put_line(')');
  dbms_output.put_line('select * from cte;');
  
  dbms_sql.close_cursor(c => l_c);
end print_cte;
/

and then you can do:

begin
  print_cte('select * from your_table');
end;
/

which produces:

with cte ("ASSET_ID", "VERTEX_NUM", "X", "Y") as (
  select 10, 1, 118.56, 3.8 from dual
  union all
  select 10, 2, 118.62, 1.03 from dual
  union all
  select 10, 3, 121.93, 1.03 from dual
)
select * from cte;

Your client has to be configured to handle dbms_output, of course.

As noted in the inline comments you should check the passed-in statement isn't going to do something nasty; and you need to add handling for other data types. This is just a starting point.

db<>fiddle