The Power of Less: Exploring the ROWLIMIT Command in SQLPlus

Have you ever dealt with large datasets, and just needed a quick peek at the first few rows? Or wanted to limit the output for testing purposes?

The ROWLIMIT SQLPlus command can help you with that! It is an overlooked command, and maybe not all of you are aware of it either. ROWLIMIT was introduced in 18c, so it has been here for a while. Gone are the days of always having to modify your SQL queries with WHERE ROWNUM <= n just to see a sample. SET ROWLIMIT provides a more convenient and less intrusive way to control the number of rows displayed by your subsequent queries within your current SQL*Plus session.

How to turn it ON

The SET ROWLIMIT command provides a simple and elegant way to limit the number of rows returned by your subsequent SELECT statements. The syntax is straightforward:

SQL> SET ROWLIMIT 20
SQL> SELECT * FROM large_table; 

The above query will return the first 20 rows from your query. Please note this setting will apply to all your queries until you either turn ROWLIMIT OFF or you disconnect your session.

This command is useful for quick data exploration, to return a few rows to see how the data looks and for testing purposes. Once you set a ROWLIMIT value, SQLPlus will automatically stop fetching and displaying rows after it reaches that limit for any subsequent SELECT statements you execute in the same session.
It’s important to note that this limitation is applied after the SQL query is executed by the database.

How to turn it OFF

When you no longer need the row limit, you can easily disable it by setting it to 0 or OFF:

SQL> SET ROWLIMIT 0  
-- Or 
SQL> SET ROWLIMIT OFF

SQL> SELECT * FROM large_table; -- Now displays all matching rows

What are the current settings in my session?

You can always check the current ROWLIMIT setting using the SHOW ROWLIMIT command:

SQL> SHOW ROWLIMIT
rowlimit 10 (on)

SQL> SET ROWLIMIT OFF

SQL> SHOW ROWLIMIT
rowlimit 0 (off)

Let’s look at an example: You want to see the first 3 employees from the EMPLOYEES table.

SQL> SET ROWLIMIT 3
SQL> SELECT employee_id, first_name, last_name FROM employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        101 Neena                Kochhar
        102 Lex                  De Haan

3 rows selected.
SQL> SET ROWLIMIT OFF

In conlcusion, the ROWLIMIT command provides a simple way to control the number of rows displayed in your SQLPlus session!