Forum Stats

  • 3,839,332 Users
  • 2,262,484 Discussions


Idea: Generate WITH clause from resultset

User_1871 Member Posts: 244 Red Ribbon

I've run a query in Oracle SQL Developer:


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.


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


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



  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,260 Employee
    edited Feb 10, 2022 5:58PM


        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++ ) {
        var DataTypesUtil = Java.type("oracle.dbtools.raptor.utils.DataTypesUtil");
        while( ) {
            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"/temp/identifiers.txt");
    var toolkit = java.awt.Toolkit.getDefaultToolkit();
    var clipboard = toolkit.getSystemClipboard();
    var strSel = new java.awt.datatransfer.StringSelection(accumulatedString);
    clipboard.setContents(strSel, null);

  • User_1871
    User_1871 Member Posts: 244 Red Ribbon
    edited Feb 14, 2022 3:24PM

    @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...


  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,704 Employee

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

  • User_1871
    User_1871 Member Posts: 244 Red Ribbon

    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.


  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,260 Employee
    edited Feb 18, 2022 10:59PM

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

        var ConnectionResolver = Java.type('oracle.dbtools.db.ConnectionResolver'); 
        var connectionList = ConnectionResolver.getConnectionNames();
        for( conn in connectionList )

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

        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
  • Troy Clark
    Troy Clark Member Posts: 4 Blue Ribbon

    Cool! Thanks for sharing!

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    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);
        case dbms_tf.column_type_name(col)
          when 'NUMBER' then val := 'nvl(regexp_replace(to_char('||||',''tm9'',''nls_numeric_characters=.,''),''(^|-)\.'',''\10.''),''null'')';
          when 'DATE'   then val := 'nvl(to_char('||||', ''"to_date(''''"yyyy-mm-dd hh24:mi:ss"'''',''''yyyy-mm-dd hh24:mi:ss'''')"''),''null'')';
                        else val := '''''''''||replace('||||','''''''','''''''''''')||''''''''';
        end case;
        return val||'||'' '||replace(,'''','''''')||'''';
      end format_col;
      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;
    with my_data as (
      select * from scott.emp where rownum<=3
    select * from query_to_with(my_data);
    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.