Sql*plus Basics and Tips

The Sql Buffer
The Sql buffer stores most recently SQL command or most recently PL/SQL block.

SQL*Plus does not store SQL*Plus commands, or the semicolon or slash characters
you type to execute a command in the SQL buffer.

You can execute the command or block in the SQL buffer using the RUN or /(slash)
commands.

RUN commands displays the command or block in the buffer and then execute it.
/(slash) executes the command or block in the buffer but does not display it.

Below is the example.
A simple SQL command.
SQL> select sysdate from dual;
SYSDATE
---------
29-NOV-08

This one (Setting linesize) is SQL*plus command and hence is not stored in SQL buffer.
SQL> set linesize 100

Invoking list will show SQL buffer, not SQL*plus command.

SQL> list
1* select sysdate from dual

Run will execute the command after displaying it.
SQL> run
1* select sysdate from dual

SYSDATE
---------
29-NOV-08

/(Slash) will not display the command. It will only execute it.
SQL> /

SYSDATE
---------
29-NOV-08

Continuing a Long SQL*Plus Command on Additional Lines
Suppose you want to write a long sql*plus command or a sql command, a long identifier or a long word in the SQL*plus. You can put them in one line by entering hyphen(-) at the end and then entering a carriage return. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line.

So hyphen(-) in Sql*plus is considered as continuation character. For example to format a long column you can use hyphen in sql*plus.
SQL> column salary format 99,999 -
> heading 'Monthly Salary'

Similarly,
SQL> select 200--
> 100 from dual;

200-100
----------
100
SQL> select 200-
> -100 from dual;

200-100
----------
100
Hyphen at the last is considered as continuation character. So hyphen at the last is ignored. Hence following will return error.
SQL> select 200-
> 100 from dual;
select 200 100 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Stopping a Command while it is Running
In order to stop displaying a long running output on Sql*plus press CTRL+C.

Displaying output page by page in Sql*plus
Normally whenever we run any SQL comamnd the output of it came all in a row. If the command return 1000 rows then it shows all on the console. You may also could not be able previous output by scrolling. This may be annoying. In order to display output page by page you have to set two variable.

SET PAGESIZE 30 --which will say in a page how many lines will be displayed.
SET PAUSE ON --which will pause the output to display unless you press enter.

Now run sql command like SELECT OBJECT_NAME FROM DBA_OBJECTS; and proceed further after pressing enter. Every new pressing of ENTER keyword will display you a new page.


Saving Changes to database automatically
With the Sql*plus variable AUTOCOMMIT option you can decide when to commit and when to not. By default it is set to OFF which means committing transaction is done whenever you give COMMIT explicitly. If you want committing will happen immediately then invoke,
SET AUTOCOMMIT ON

In order to do committing after 10 DML statements do,
SET AUTOCOMMIT 10
To turn the autocommit feature off again, enter
SET AUTOCOMMIT OFF

Sql*plus Error messages
Sql*plus errors begin will letter SP2.
Like,
SQL> @testing.sql
SP2-0310: unable to open file "testing.sql"

Sql*plus copy command error messages start with CPY.