Say yes to Sql*Plus / Sql*Plus Tips

Many one do not like Sql*plus to write any Sql. Rather they prefer TOAD, tora or any other editing tools to write sql commands. But according to me Sql*plus is first choice. Many people see Sql*plus is a cumbersome one and complicated while editing their commands. But within SQL itself there are flexible option to edit pre-written commands. In SQL*plus there are many environmental variables and by using set …. these variables can be set. To see the settings just use show variable_name. Here I will show some tips while we use SQL*plus.

1. You can end a SQL command in one of three ways

(i)with a semicolon (;)

(ii) with a slash (/) on a line by itself.

(iii)with a blank line (Depend on SET SQLBLANKLINES Settings).

2.(i)Terminate PL/SQL subprograms by entering a period (.) by itself on a new line.

(ii)You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line.

3.Your operating system may have one or more text editors that you can use to write scripts. For example, to define the editor used by EDIT to be vi, enter the following command:
DEFINE _EDITOR = vi

EDIT SALES EDIT adds the filename extension .SQL to the name unless you specify the file extension.

4.You can use a number of SQL*Plus commands to edit the SQL command or PL/SQL block currently stored in the buffer. Some useful SQL*Plus commands that allow you to examine or change the command in the buffer without re-entering the command.

(i)LIST(; or L)-lists all lines in the SQL buffer.LIST n,LIST *,LIST n *,LIST LAST,LIST m n,LIST * n is also available.
(ii)APPEND text(A text)-adds text at the end of the current line.
(iii)CHANGE/old/new(C/old/new)-Changes old to new in the current line
(iv)CHANGE/text(C/text)-Deletes text from the current line
(v)DEL-deletes the current line.DEL n,DEL * ,DEL n *,DEL LAST,DEL m n,DEL * n are also available.
(vi)INPUT(I)-adds one or more lines
(vii)INPUT text(I text)-adds a line consisting of text

Related Documents:
-------------------------------------------------------
How to set Environmental variable to SQL*Plus permanently

Sql*plus Basics and Tips

Use of bind variables in Sql*plus

Working with Sql*plus Scripts

Controlling the Autotrace Report in sql*plus

What is the difference between $ ! and HOST?

Formatting SQL*Plus Reports

Formatting SQL*Plus Reports Part 2

Change Prompt in SQL*Plus