Systems Engineering and RDBMS

Back to the basics: SQL*Plus edit commands

Posted by decipherinfosys on April 5, 2008

If you have worked in Oracle, chances are that you must have used SQL*Plus. In this post, we will re-visit some of the basic edit commands for SQL*Plus session.

SQL*Plus uses system editor, notepad in case of windows and VI in case of Unix. Commands to edit SQLs are shown below. Letters in the parenthesis are abbreviated version of command.

List – (l)
Change – (c)
Input – (i)
Append – (a)
Del – (d)
Edit – (ed)

We will start looking at these one by one but first connect to SQL*Plus using the appropriate userid and password so that you can follow along.

List:

This command is used to list current sql. It is abbreviated as l. LIST or l displays the entire buffer. LIST 1 displays the 1st line. Similarly LIST 3 displays the 3rd line. LIST 1 2 displays first two lines. If this command is executed immediately after connecting to SQL*Plus, it will not display anything.
SQL>
SQL> l
SP2-0223: No lines in SQL buffer.

Now let us write some sql.

SQL> select object_name
2 from user_objects
3 where rownum < 10;

Above query will return first 9 objects from user_objects table. Now we decided to display object_type along with object_name. We need to add object_type in select clause. This will require select statement to be changed. In order to change it, we need to list the 1st line and make it current. l 1 will do that.

SQL> l 1
1* select object_name

Change:

This command is used to change the first occurrence of the text on the current line. Current line is preceded with ‘*’. It is also abbreviated as c. In our previous example, we have already listed the select statement we want to change. So we will change it now.
SQL> c/object_name/object_name,object_Type
1* select object_name,object_Type

We have changed the select clause successfully in above statement. We can see that 1st line is now changed. But still we can make sure by listing entire sqls. Since we have only 3 lines in SQL statement, we will use l 1 3 command to list the lines.

SQL> l 1 3
1 select object_name,object_Type
2 from user_objects
3* where rownum < 10

Insert or Input:

This command is used to insert new line after the current line. It is abbreviate as i. We can add new line at the end to sort the records by object_type.

SQL> i order by object_Type

We can confirm it again by listing entire sql.

SQL> l
1 select object_name,object_Type
2 from user_objects
3 where rownum < 10
4* order by object_Type

Append: This command appends the text to the current line. We can again list 1st line and add status column to the select clause.

SQL> l 1
1* select object_name, object_type
SQL> a ,status
1* select object_name,object_Type,status

One can list entire sql again using list command and execute it. The way we can insert or append the sql statement, we can also delete the line.

Delete:

Deletes the current line. It is abbreviated as DEL. In our example, we will remove the order by clause by deleting the last line. To recap, here is the latest sql after all our changes in previous examples.

SQL> l
1 select object_name,object_Type,status
2 from user_objects
3 where rownum < 10
4* order by object_Type

Here is the command to delete the current line followed by revised sql.

SQL> del

SQL> l
1 select object_name,object_Type,status
2 from user_objects
3* where rownum < 10

We can even open the sql buffer by editing the sql using edit (ed) command.

Edit:

Normally abbreviated ad ed and can edit sqlplus buffer or file. It uses system editor. Notepad in case of windows and vi in case of UNIX.

In our next part, we will cover how to deal with files in SQL*Plus.

One Response to “Back to the basics: SQL*Plus edit commands”

  1. […] Back to the basics: SQL*Plus edit commands […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: