Sqlplus Command

Here, you can find some usefull sql plus command, to enhance or fix how you query the database.

Limit the amount of line of a query.

At the begining, I used sqlplus, but also used plsql developper to do some query, and one usefull things with pl/sql developper was that it retrieve only the first row of a table. I found it really nice ! And moreover with sqlplus , if you don't format the output, and let sqlplus output all the line of your query, it won't be always easy to read your output. So, my collegue at work found how to limit the amount of an sqlplus query. When you execute a query, simply add this :

where rownum < 2;

if you want to limit the output to the first. So, with a complete query, if you want to limit the output to the 10 first line, execute this :

SQL> select * from emp where rownum < 11;

Enjoy it !!

Execute shell command in sqlplus prompt

If you want to execute a shell command, simply add, in front of you command, this sign :

!

By instance, list the content of the current directory, simply execute this in sqlplus prompt :

SQL> !ls

A command that I use a lot, is to clear the screen, so let's simply do it with :
SQL> !clear
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License