Idea: Generate WITH clause from resultset

User_1871

I've run a query in Oracle SQL Developer:

Scenario:

I want to share the resultset data on a forum — in a format that's easy for answerers to grab and use.

From my experience, WITH clauses are preferred by forum members, since WITHs avoid the need to create data in a local Oracle environment.

Example:

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

Idea:

Could functionality be added to SQL Developer that automatically generates a WITH clause from the resultset?

Vadim Tropashko-Oracle

Code:

script
    var DriverManager = java.sql.DriverManager;
    var conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/xe", "hr", "hr");
    var stmt = conn.createStatement();
    var rs = stmt.executeQuery('select * from departments');
    var rsmd = rs.getMetaData();
    var cols = [];
    for( i = 1; i <= rsmd.getColumnCount(); i++ ) {
	cols.push(rsmd.getColumnName(i));
    }

    var DataTypesUtil = Java.type("oracle.dbtools.raptor.utils.DataTypesUtil");
    while( rs.next() ) {
        var record = 'select ';
        for( var i = 1; i <= rsmd.getColumnCount(); i++ ) {
           var o = rs.getObject(i);
           var s = DataTypesUtil.stringValueChecked(o,conn);
	   record += /*(1<i?",":"")*/',\"'+s+'\" AS '+cols[i-1];
	}
        print(record + ' from dual');
        print(' union all ');
    }
/         

There could be variations of this, for example you can get the active oracle connection like this:

    var ConnectionResolver = Java.type('oracle.dbtools.db.ConnectionResolver'); 

    var connectionList = ConnectionResolver.getConnectionNames();

    var conn = ConnectionResolver.getConnection('IdeConnections%23local_hr');

The output can be channeled to the other streams as well:

var out = new java.io.PrintWriter("/temp/identifiers.txt");
out.println(accumulatedString);
out.flush();
out.close();

javax.swing.JOptionPane.showConfirmDialog(null,accumulatedString);

var toolkit = java.awt.Toolkit.getDefaultToolkit();
var clipboard = toolkit.getSystemClipboard();
var strSel = new java.awt.datatransfer.StringSelection(accumulatedString);
clipboard.setContents(strSel, null);

User_1871

@Vadim Tropashko-Oracle

Thanks very much. I imagine that script will be useful to DBAs, etc.

If I understand correctly, the script (JavaScript) would be used in SQLcl (command line). Unfortunately, I'm not able to use SQLcl. I'm a data analyst, not a dba, so I'm not able to use tools like that. I use SQL Developer exclusively.

So I think my original suggestion still applies: Would it be possible for Oracle to add it as OOTB functionality to SQL Developer? That functionality is available in other SQL clients like Toad, etc...

Cheers!

thatJeffSmith-Oracle

Anything's possible. And there's no reason why you can't use what vadim showed you in the SQL Worksheet.

User_1871

I'm a novice, so I could be missing something. But I couldn't find a way to do it without using Java thin (I want to use my existing SQL Developer connections).

As far as I can tell, the only way to do this is via SQLcl.

@thatJeffSmith-Oracle

Vadim Tropashko-Oracle

You can use connections that are already available in SQLDeveloper. You need to know their internal SQLDeveloper names, which can be accomplished like this:

script
    var ConnectionResolver = Java.type('oracle.dbtools.db.ConnectionResolver'); 
    var connectionList = ConnectionResolver.getConnectionNames();
    for( conn in connectionList )
        print(connectionList[conn])
/         

After identifying a connection name, e.g. "IdeConnections%23local_hr" , you can reference any available connection like this:

script
    var ConnectionResolver = Java.type('oracle.dbtools.db.ConnectionResolver'); 
    var conn = ConnectionResolver.getConnection('IdeConnections%23local_hr');
/  

Please be aware that the list contains all the connections that you have on the connection panel, and if you want to run SQL statement across any on them, please open it (that is connect) in SQLDeveloper first.

Troy Clark

Cool! Thanks for sharing!

User_H3J7U

To work with the current connection in the script you can use ctx.getCurrentConnection() or util.ExecuteReturnList(). Instead of print() use a ctx.write(), this will send the output to Script Output pane of current worksheet.

SQL solution 19.8+:

create function query_to_with(qry dbms_tf.table_t) return varchar2 sql_macro as
  sell varchar2(32767 byte);
  resq varchar2(32767 byte);
  function format_col(col dbms_tf.column_metadata_t) return varchar2 as
    val varchar2(32767 byte);
  begin
    case dbms_tf.column_type_name(col)
      when 'NUMBER' then val := 'nvl(regexp_replace(to_char('||col.name||',''tm9'',''nls_numeric_characters=.,''),''(^|-)\.'',''\10.''),''null'')';
      when 'DATE'   then val := 'nvl(to_char('||col.name||', ''"to_date(''''"yyyy-mm-dd hh24:mi:ss"'''',''''yyyy-mm-dd hh24:mi:ss'''')"''),''null'')';
                    else val := '''''''''||replace('||col.name||','''''''','''''''''''')||''''''''';
    end case;
    return val||'||'' '||replace(col.name,'''','''''')||'''';
  end format_col;
begin
  sell := format_col(qry.column(1).description);
  for i in 2..qry.column.count loop
    sell := sell || '||'', ''||' || format_col(qry.column(i).description);
  end loop;
  resq := 'select ''with sample_data as ('' with_query from dual
  union all select case when rownum>1 then ''union all '' end||''select ''||'||sell||'||'' from dual'' from qry
  union all select '')'' from dual
  union all select ''select * from sample_data;'' from dual';
  return resq;
end;
/

with my_data as (
  select * from scott.emp where rownum<=3
)
select * from query_to_with(my_data);

WITH_QUERY                                                                                        
--------------------------------------------------------------------------------------------------
with sample_data as (
select 7839 "EMPNO", 'KING' "ENAME", 'PRESIDENT' "JOB", null "MGR", to_date('1981-11-17 00:00:00','yyyy-mm-dd hh24:mi:ss') "HIREDATE", 5000 "SAL", null "COMM", 10 "DEPTNO" from dual
union all select 7698 "EMPNO", 'BLAKE' "ENAME", 'MANAGER' "JOB", 7839 "MGR", to_date('1981-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') "HIREDATE", 2850 "SAL", null "COMM", 30 "DEPTNO" from dual
union all select 7782 "EMPNO", 'CLARK' "ENAME", 'MANAGER' "JOB", 7839 "MGR", to_date('1981-06-09 00:00:00','yyyy-mm-dd hh24:mi:ss') "HIREDATE", 2450 "SAL", null "COMM", 10 "DEPTNO" from dual
)
select * from sample_data;

The function implements only three datatypes. DBMS_TF supports more sql types.

User_1871

@User_H3J7U Thanks!