Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Return more than 50 rows in Oracle Live SQL

Regarding the Oracle Live SQL online environment (https://livesql.oracle.com/):

When I run a query, only 50 rows are returned by default.

Is there a way to return more than 50 rows?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel


Example session: https://livesql.oracle.com/apex/livesql/s/nmwyv5cu5vo4p9vofwjsasemh

enter image description here


Source script: https://stackoverflow.com/a/72621332/5576771

--This query should return 220 rows.
with dimension as (
    select 0 as point from dual
      union all 
    select level
    from dual
    connect by level <= 10
), points as (
    select 
      a.point as startpoint, 
      b.point as endpoint,
      c.point as fixed
    from dimension a
    cross join dimension b
    cross join dimension c
    where b.point - a.point = 1
)
select
  startpoint as startpoint_x,
  fixed as startpoint_y,
  endpoint as endpoint_x,
  fixed as endpoint_y
from points
  union all
select
  fixed as startpoint_x,
  startpoint as startpoint_y,
  fixed as endpoint_x,
  endpoint as endpoint_y
from points
order by startpoint_y, endpoint_y, startpoint_x, endpoint_x

>Solution :

This is limitation of LiveSQL. You can partially circumvent it using option Action -> Maximum Rows Preference see screenshot, where you can set the highest limit 5000. It is AFAIK still impossible to set infinity – there is an explanation "This prevents your browser from being overloaded with very large query results. " in the help widget.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading