Oracle SQL*Plus: Basics part 1

Here in this post,I would be giving introduction to Oracle sqlplus  which is Oracle tools to connect to database .

Oracle SQL*Plus: Basics part 1

What is Sqlplus:

1) SQL*Plus, widely used by DBAs and developers, is a powerful and straightforward tool from Oracle to interact with the database.
2) SQL*Plus has its own formatting commands to make your queries look better, as well as commands to set up the environment.
3) SQL*Plus is available on all platforms like AIX,Solaris,Unix ,windows,HP etc 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.
How to begin with Sqlplus:
1) 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.So  you need username ,password and connectstring to connect to the database

2) You can login to other user or database,once you are in SQL*Plus using the command CONNECT USERNAME/PASSWORD@CONNECTSTRING. The slash separates username and password; the connect string following @ is the database alias name. If you omit the password, you will be prompted to enter it. You may omit the connect string to connect to a local database.

3) EXIT command is used to exit from sqlplus .You may also use the QUIT command to complete the session. EXIT and QUIT are synonymous.

How the commands are Entered and Executed
1) 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.
2) 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:
a)End with a semicolon (;). The command is completed and executed.

select * from emp;
b)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.

select * from emp

c) 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.
Simple SQL*Plus COMMANDS  

• SAVE “file command”   : It saves the file to Filesystem on Unix
• GET “file command” : It open the file on the sqlplus prompt
• EDIT “file command” : You can edit the file
• SPOOL “file command” : You can spool the log of any command to the file on filesyste,
• INPUT “editing command”
• LIST “editing command” : It shows the last run command
• RUN “editing command” : It executes the command in sql buffer
• APPEND “editing command” : You can add  any text to the command in sql buffer using this
• CHANGE “editing command”
• CLEAR BUFFER “editing command” : It clears the sql buffer
• DEL “editing command”

Note Only SQL commands and PL/SQL blocks are stored in the SQL buffer, SQL*Plus commands are not stored in the buffer.
A hyphen (-) is used to indicate command continuation. Although SQL commands can be continued to the next line without a continuation operator, SQL*Plus commands cannot be continued

Leave a Reply