Forum Stats

  • 3,815,960 Users
  • 2,259,119 Discussions
  • 7,893,347 Comments

Discussions

Generate WITH clause from resultset using SQL Developer?

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?

User_1871

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,803 Red Diamond

    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
    User_1871 Member Posts: 112 Green Ribbon

    FYI - This post is a duplicate of my original:

    Idea: Generate WITH clause from resultset

  • User_KACDL
    User_KACDL Member Posts: 15 Green Ribbon
    edited Apr 28, 2022 11:41AM

    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.