How to use Oracle SQL*Plus



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

Oracle SQL*Plus

What is Oracle Sql*plus:

1) SQLPlus, 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 Oracle Sqlplus:

1) When you start SQLPlus, it prompts you for the username, password, and connect string. The connect string is the database alias name. If you omit the connect string, SQLPlus tries to connect you to the local database defined in the ORACLE_SID variable.So  you need username ,password and connect-string 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 in Oracle sqlplus

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 Oracle SQL*Plus COMMANDS

• DESCRIBE
• 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, SQLPlus 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

Oracle sql*plus download

1) Download the zip files containing the SQL*Plus Instant Client package, and the OCI package from the OTN Instant Client page at http://www.oracle.com/technetwork/apps-tech/winx64soft-089540.html

Both packages must be from the same version such as 11.2.0.4.

Oracle sql*plus download

Oracle sql*plus download

2) Create a new directory, for example, /home/instantclient10_2 on UNIX or c:\instantclient10_2 on Windows.

3) Unzip the two packages into the new directory.

4) Add the directory containing the Instant Client files to the PATH system environment variable. Remove any other Oracle directories from PATH.

5) et the user environment variable SQLPATH to the directory containing the Instant Client files so glogin.sql is found

6) setup tnsnames.ora and set the variable TNS_ADMIN to that path

Now sqlplus is ready to connect




Leave a Reply