A Simple Guide to ORACLE SQLPLUS
Adapted from A
Simple Guide to ORACLE SQLPLUS, from Prof. Kalpakis's CMSC 661 Fall 2004 class.
Topics
See Oracle at UMBC Guide
To enter the ad hoc query system, invoke the sqlplus command.
dbs1[2]% sqlplus
SQL*Plus: Release 3.3.2.0.0 - Production on Thu Nov 7 15:23:29 1996
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter user-name: finin
Enter password:
Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release
With IRIX Enhancements, PL/SQL Release 2.3.2.0.0 - Production
SQL> help;
HELP
HELP [topic] | help
HELP displays information on the commands and conventions of SQL*Plus,
SQL, and PL/SQL. Type "help", a space, all or part of any topic, and
then press Enter.
Typing only part of a topic (for example, HELP SE) will cause all help
topics that match the string (SE) to be displayed on your screen in
alphabetical order (for example, HELP SE will retrieve help on the
SELECT statement, the SET command, and the Set Operators--in that order).
ENTER THIS... TO DISPLAY THIS...
--------------------------------------------------------------
help commands a list of SQL*Plus, SQL, and PL/SQL commands
help comm information on commands, comments, and commit
help help this screen
SQL> exit;
Disconnected from Oracle7 Server Release 7.3.2.1.0 - Production Release
With IRIX Enhancements
PL/SQL Release 2.3.2.0.0 - Production
dbs1[3]%
You can change your ORACLE password using the SQL grant command:
grant connect to <user id> identified
by <password>;
For example, grant connect to finin
identified by ragnarok;
The semi-colon at the end of the command is required.
Type Exit while inside SQLPLUS followed by the
Enter key.
To get help from the SQL prompt, you can simply type `help something',
where `something' is a command (e.g. `help select;'), a function
(e.g. `help to_char;'), or a datatype (e.g. `help char;'). This is
the fastest and easiest way to get help with commands. There is
another, more comprehensive documentation program (oradocm) which has
not yet been installed.
You should execute the commit command before you change
your database. When you are satisfied with your work, you could
save it in the database permanently by executing the commit
command, or you could discard all changes you made since the last
commit command by typing rollback command.
- Check relations you created in the database:
select *
from system.tab;
- Check the definition of a relation you created:
SQL> describe <relation name>
SQL> describe student
- If you forget a specific SQL command you could enter
SQL> help <the SQL command>
You could also find out all commands by entering:
You could prepare all your SQL commands, such as creation of relations
and queries, into a text file and execute the SQL commands in this
file when you are in SQLPLUS.
- Use your favorite editor to type in your SQL queries.
EXAMPLE:
cat myoutput.lst
create table myrelation
(mykey char(4), name char(30), address char(50));
insert into myrelation values
('1020', 'John Smit', '1 Main Street');
insert into myrelation values
('1030', 'Linda Jade', '1 Main Street');
insert into myrelation values
('1040', 'Tom Dube', '1 Main Street');
select *
from myrelation;
drop table myrelation;
spool out
- Save the queries into a file. If you use a word processor, such
as WordPerfect or Word for Windows, make sure to save the file
in text format. Assume the above example is saved as:
Once the file is transformed into UNIX, you can run the SQL
statements after you log into SQLPLUSi. Assume that you name
the SQL file as myquery.sql.
SQL> start <your sql file name>
EXAMPLE:
- If there are errors in your SQL command file, ORACLE will display
the error and discard that command. It tries to execute the following
SQL command if possible. If situations like this happen, you may
want to clear the database to discard the changes you made. If
you execute commit before running this file, the rollback
command can restore the database for you. You then should go back
to edit your file and fix all errors. The screen editor of
DOS can be invoked by enter:
SQL> edit <your SQL file name>
EXAMPLE:
After errors are fixed, you may repeat the above steps again.
- To be useful, you should organize your queries into different
files. For example, let one file contain all SQL commands for
creating relations, one file for removing all relations, one file
for inserting tuples into all relations, one file for retrieving
results from the relations, and one file for delete all tuples.
In such a way, you could easily test different queries. You should
avoid to putting all SQL commands into one "monster"
file. It would be very difficult to work with.
- You can save or append the SQL query you type into a text file.
This command is very useful for you to record SQL statements
after you test them one by one.
You last query:
SQL> select *
from student;
To save it into a file:
To append a tested query into the same file:
As the file, working.sql, is kept in the UNIX account,
you should down load it to your PC from the UNIX account and
then print it from your printer or incorporate it into your
document.
- Record your database activities into a file for output or
editing purpose.
SQL> spool <your file name>
EXAMPLE:
All database activities after this command are written into this file.
- End recording database activities and print out them by using
the following commands:
SQL> spool off (stop spooling)
SQL> spool out (stop spooling & send the file
directly to a printer)
Once you have the output file, you could print out the file use
the lpr command of the computer system after you exist
from SQLPLUS.
- Attribute headings by default are attribute names. The column
command defines new attribute headings.
SQL> column heading ;
Example:
SQL> column desc heading description
Two line heading example:
SQL> column Emp# heading 'Employee|Number'
- To change the character used to underline headings, use the set
underline command. For example,
SQL> set underline =
set the underline character to the equal character.
- To format a number attribute to a dollar format, use the column
<attribute> format <9'format>:
SQL> column salary format $999,999
- To indicate the displayed width of a character string attribute,
use the column <attribute> format <A'format>.
For example, set the width of the name attribute to 8 characters.
SQL> column name format A8
If a name is longer than 8 characters, the remaining is displayed
at the second line (or several lines)
- To avoid repeat the leading attribute of a sorted output, use
the break on <attribute> skip <n>. For example,
the following query is:
SQL> select name, item#, amount
2 from inventory
3 order by name;
NAME ITE AMOUNT
---------- --- ----------
Box i4 12.93
Box i8 98.23
Phone i3 54.23
Table i2 23.12
Table i6 54.98
Chair i1 23.84
Chair i5 43.98
Chair i9 23.12
Chair i7 23.45
SQL> break on name skip 1
SQL> select name, item#, amount
2 from inventory
3 order by name;
NAME ITE AMOUNT
---------- --- ----------
Box i4 12.93
i8 98.23
Phone i3 54.23
Table i2 23.12
i6 54.98
Chair i1 23.84
i5 43.98
i9 23.12
i7 23.45
- To add titles to the query result, use the command: ttittle
<position> <title> <position>.
Example: one line title
SQL> ttitle center 'Company Sale Report' skip 1
SQL> select name, item#, amount
2 from inventory
3 order by name;
Company Sale Report
NAME ITE AMOUNT
---------- --- ----------
Box i4 12.93
i8 98.23
. . .
Example: multiple line title
SQL> ttitle center 'Company Sale Report' skip 1 -
> center ================================ skip 1 -
> left 'Personal Report' right 'Sales Department' skip 2
SQL> select name, item#, amount
2 from inventory
3 order by name;
Company Sale Report
===========================
Personal Report Sales Department
NAME ITE AMOUNT
---------- --- ----------
Box i4 12.93
i8 98.23
- The set command can be used to change the default number
of lines per page (14) and the number of characters per line (80).
SQL> ttitle center 'Company Sale Report' skip 1 -
SQL> set pagesize 60
- All formatting remain active until they are cleared or reset.
SQL> clear break
SQL> clear column
SQL> ttitle off (may be turn on by using the 'on'switch)
Adapted from Prof. Finin's CMSC 661 Fall 1996 class.