Oracle Editing Commands

Main Page >> Oracle and SQL >> Workbook >> SQL Overview >> Editing Commands

Editing Commands

Inline editing

By now, you may have made some typing mistakes. Oracle has a built-in command line editor, which can be used to correct errors but is tedious to use.

Since Oracle 11g, however, the arrow keys can be used to return to an error and can be corrected. Once corrected, you will have to press return to re-run the amended version.

For example, type in the following:

 selet * from temp;

This should generate an error message along the lines:

SP2-0734: unknown command beginning "selet * fr..." - rest of line ignored.

There are two issues with the above command:

1. selet should be select
2. temp should be emp

Use the up-arrow to return to the previous command, then use the left-arrow to move to the errors and amend so the line now says:

 select * from emp;

Note: you do not need to return to the end of the line to press return, you can press enter when the SQL statement is fully corrected.

If your SQL command went over more than one line, you need to re-run the whole command and correct as appropriate. For example, type in the following (press enter after the first line):

 select table_name
 from usr_tables;

This time the error is on the second line:

ERROR at line 2:
ORA-00942: table or view does not exist

The first line is fine. To correct and re-run the command, press the up-arrow key twice to retrieve the first line and press return. The system should respond as follows:

SQL> select table_name
  2

Press up-arrow again until the line: from usr_tables; is seen. Before pressing return use the arrow keys to correct the statement to:

 from user_tables;

Once corrected, press return and the command will now run again.

Notepad

Later on your commands may be more complex and editing this way will be more difficult. Alternatively, you can edit using a screen editor. Under Windows the default editor is Notepad. If using the server directly, e.g., via putty and mi-linux, the default editor is vi. If you are not familiar with vi, you may prefer to use Notepad on your PC and copy & paste your commands between the PC and the Linux server.

Notepad is a Windows text editor, the main command you need to know is File>Save found on the window menu bar to save any amendments and File>Exit to return to Oracle.

Type in the following:

select * from salgade;

To invoke the editor from within Oracle (either Windows or Linux) type:

ed

This will show the last command typed in (saved to a file called afiedt.buf). The arrow keys can be used to move to any typing mistakes.

Correct the error, so the command in Notepad now says:

select * from salgrade
/

Note: Oracle will change the semi-colon (;) to a forward slash (/), which has been placed on a new line. Do not change this!

Return to Oracle by selecting:

File>Save 

then

File>Exit. 

The amended file will appear in the Oracle Window. To run the amended file, type a forward slash:

/

Note: this command can be used at any time to re-run the last command again, not necessarily after correcting a mistake.

Next Step

Getting information from a table.