Generate WITH clause from resultset using SQL Developer?

User_PBFCF

I've run a query in Oracle SQL developer 18.

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

• WITHs are also easy to paste into db<>fiddle.

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

Back when I was using Toad 12 for Oracle, I remember having an export option for generating a WITH statement from a resultset. Which was really handy.

Is there equivalent functionality in SQL Developer — to automatically generate a WITH clause from the resultset? Or are we stuck writing WITH clauses manually?

Billy Verreynne

There is no such thing as a dynamically created in-memory result set that persists as the result set for a SQL statement. Just imagine the server's memory requirements where a single SQL in-memory result set can contain millions of rows, and 100s or even 1000s client sessions can exist.

A SQL cursor can and often do use temporary tablespace for sorting rows for example.

A WITH clause can be materialised by the SQL cursor using temp space in certain situations.

PL/SQL deterministic function's results used in the SQL cursor can be cached.

Data blocks (from tables and indexes) I/O'ed by the SQL cursor are typically cached.

But an actual persistent dynamic memory data set created from the SQL cursor's output (aka results) does not exist.

User_1871

FYI - This post is a duplicate of my original:

Idea: Generate WITH clause from resultset

User_KACDL

Outsourcing allows you to cut costs and improve your business operations. Dedicated software development teams are available in a matter of days and are managed directly by the company's owners. The cost of hiring a dedicated software development team is approximately the same as traditional recruitment and can be as high as $20,000 per month. Moreover, you'll be able to communicate directly with them, which can make it easier for you to monitor the development process https://mlsdev.com/blog/how-to-create-a-marketplace.