Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 471 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Idea: Generate WITH clause from resultset

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?
Answers
-
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);
-
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!
-
Anything's possible. And there's no reason why you can't use what vadim showed you in the SQL Worksheet.
-
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.
-
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.
-
Cool! Thanks for sharing!
-
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_H3J7U Thanks!