oracle-databaseresultsetrowcount

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?


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.