SQL*Plus Guide

From bd_en
Jump to: navigation, search

Using SQL*Plus

During Oracle server instalation, on your computers will also be installed an application called SQL*Plus; this can be later accessed also under the Oracle menu as Run SQL Command Line. This is the default console of the Oracle database server and allows you to connect to the database and interact with it.

The commands that can be executed using SQL*Plus are of two types:

  • SQL*Plus specific commands, which are run by this utility without accesssing the database server;
  • SQL commands, which are sent to the SQL server which will execute them and return the answer to SQL*Plus which will just show it to us.

Any SQL command (run on the database server) has to be closed/delimited by ";". In case you pressed Enter but you forgot this symbol, SQL*Plus considers you have not finished the command and waits for you on a new line to type the remainder; you just type ";" and press again Enter.

You can type SQL*Plus specific commands without introducing ";"; however, if you do, you will get no error and the command will be executed.

Any command meant for the SQL server (further called SQL commands) are automatically stored in a buffer by SQL*Plus. Actually, always the latest executed command replaces the previous one in the buffer, so that only the last SQL command is stored. Most of the SQL*Plus commands are designed to work with the content of the buffer.

Next, we list some of the most important SQL*Plus commands.

SQL*Plus commands

The syntax for listing the commands is the following:
- The text outside any parenthesis is mandatory;
- In braces there are several options, delimited by "|"; one option must be used;
- The text within squared brackets is optional;
Buffer manipulation
  • {RUN | /}: re-executes the SQL command already stored in the buffer.
  • {EDIT | ED}: opens the editor for editing the content of the buffer. This editor must be closed to be able to execute the buffer content.
  • APPEND text: appends desired text at the end of the buffer.
Working with files
  • SAVE file [CREATE | REPLACE | APPEND]: saves the content of the buffer in a file
  • GET file [LIST | NOLIST]: takes the content of the file and loads it in the buffer without executing it. Its content is then listed, excepting the case when the command ends with the option NOLIST;
  • SPOOL [file] [CREATE | REPLACE | APPEND][OFF | OUT]: puts in the file "file" all the content that is printed in SQL*Plus. The file is not actually written to disk until you execute SPOOL OFF. For sending the file to the printer you may use SPOOL OUT.
  • {START | @}{file.sql | url}: executes all the commands in the file "file.sql" or from the given url (where, of course, a file containing SQL commands should be stored).
Commands from connecting/disconnecting to/from the database server
  • CONNECT usr[/pass]: allows the user "usr" to connect using password "pass". In case you are already connected with other account, the current connection is closed (it's like a DISCONNECT command is invisibly launched) and a new one is created. If the password is not given (note that it is optional in the connection string) than you will be prompted for it. Be aware that under Linux operating systems it is good practice to omit the password in the connection string to avoid storing it in the commands history.
  • DISCONNECT: disconnects the current user;
  • {EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]) will COMMIT the changes to the database, will disconnect the user and will close SQL*Plus;

Commands for the SQL*Plus screen
  • CLEAR [SCREEN | BUFFER]: clears the screen or the content of the buffer;
  • LINESIZE n: by default, SQL*Plus skips to the next line if the text exceeds 80 characters. In case the size of the window running SQL*Plus is modified (i.e. right click on the window title, then Properties -> Layout and change the numerical values for Width) and SQL*Plus should show more characters (140) on one line; it does not do so until you modify the LINESIZE variable too.
  • PAGESIZE n: as LINESIZE but it is about the number of lines and not columns; by default, 14 rows/lines are shown by SQL*Plus.

Notice that the window which is used to run SQL*Plus is a typical Windows' window (like the ones opened with CMD command). Therefore, you are able to modify the font, the color of the text or of the background, by accessing the Properties menu (right click on the right-top corner). Also with right-click on the window bar you can use copy/paste commands (although, it is recommended to type the commands by your own). You even can make the console editable by right click -> Properties -> Options and checking Quick Edit Mode. From now on you can paste using right click.

You can browse a complete list of SQL*Plus commands. DO NOT mistake them for SQL commands !