Introduction to SQL* Plus
SQL*Plus, widely used by DBAs and developers, is a powerful and straightforward tool from Oracle to interact with the database. SQL*Plus has its own formatting commands to make your queries look better, as well as commands to set up the environment. SQL*Plus is available on all platforms on which Oracle runs. It is provided with the Oracle Client installation software for the client machine. It is also provided at the server level with installation software for the Oracle Server. You can execute any database command or PL/SQL block in SQL*Plus, provided that you have the right privilege to do so.
Getting Started with SQL *Plus
To start SQL *Plus on a typical windows machine
Goto programs >>Oracle Home >>Application Development >>SQL *Plus
Or type
SQLPLUSW at the command
When you start SQL*Plus, it prompts you for the username, password, and connect string. The connect string is the database alias name. If you omit the connect string, SQL*Plus tries to connect you to the local database defined in the ORACLE_SID variable.
To exit from SQL*Plus, use the EXIT command. On platforms where a return code is used, you can provide a return code while exiting. You may also use the QUIT command to complete the session. EXIT and QUIT are synonymous.
Entering and Executing Commands
Once you are connected to SQL*Plus, you get the SQL> prompt. This is the default prompt, which can be changed using the SET SQLPROMPT command. Type the command you wish to enter at this prompt. A command can be spread across multiple lines, and the commands are case-insensitive. The previously executed command
will always available in the SQL buffer. The buffer can be edited or saved to a file. You can terminate a command in any of the following ways:
>>End with a semicolon (;). The command is completed and executed.
>>Enter a slash (/) on a new line by itself. The command in the buffer is executed. This method is also used to execute a PL/SQL block.
>>Enter a blank line. The command is saved in the buffer.
The RUN command can be used instead of a slash to execute a command in the buffer. The SQL prompt is returned when the command has completed execution. You can enter your next command in the prompt.
Editing the SQL Buffer
The most recent SQL command executed or entered is stored in the SQL buffer of SQL*Plus. You can run this buffer again by simply typing a slash or using the RUN command. SQL*Plus gives you a set of commands to edit the buffer. Suppose that you want to add another column or add an order by condition. You need not type the entire SQL command again; just edit the existing command, which is in the buffer.
>> Use the command EDIT to write the buffer to an operating system file named afiedt.buf then use a system editor to make changes. You can use your favorite text editor by defining it in SQL*Plus. To make Notepad your favorite editor, just issue this command: DEFINE _EDITOR = NOTEPAD. You need to provide the entire path if the program is not available in the search path.
>> Use the SQL*Plus editing commands. You can make changes, delete lines, and add and list the buffer using these commands. Most editing commands operate on the current line.
