| Lesson 3 | Editing and running a query in SQL*Plus |
| Objective | Change text by using the in-line editor and run a query. |
Editing, Running Query in SQL*Plus
SQL*Plus contains its own line editor to make quick changes to SQL code.
While too clumsy for editing more than a few lines of SQL or PL/SQL code, it can save time when you are debugging ad-hoc queries or other short SQL commands. The SQL*Plus editor is similar to the UNIX line editor called ed. The following table lists the editing commands available.
SQL*Plus saves the current command in its buffer. You can modify the text in the buffer by using these commands:
SQL*Plus saves the current command in its buffer. You can modify the text in the buffer by using these commands:
| Command | Description |
| A[PPEND] text | Append text to end of current line. |
| C[HANGE] separator old [separator [new [separator]] | Change old to new. Separator can be any symbol that is not a letter or number, such as /. If new is omitted, old is simply deleted. |
| DEL [n] | Delete line number n from buffer. If n is omitted, delete the current line. |
| I[NPUT] [text] | Insert a new line with text immediately after the current line. If text is omitted, a blank line is inserted and you can type into it. |
| L[IST] [n] | List line n of the buffer and make it the current line. If n is omitted, list all lines in the buffer. |
Run SQL*Plus from your desktop Client Machine
If you want to run SQL*Plus from your desktop client machine, select the SQL Plus program option from the Application Development menu option under the Oracle software menu option. This starts SQL*Plus. (Note that you do not type the * that is in the middle of the official product name, and the asterisk does not appear in the program name, either.) Because Oracle is careful to guard who can access the data it stores, it requires that you enter an ID and password to connect to it. Oracle will display a copyright message and then ask for your username and password.
Log into your database using the account and password you created to hold the sample tables. If you provide a valid username and password,
SQL*Plus will announce that you are connected to Oracle and then will display this prompt:
You are now in SQL*Plus, and it awaits your instructions.
SQL
You are now in SQL*Plus, and it awaits your instructions.
Read about listing, changing, and running a query in the following section.
Using SQL*Plus Editor Commands
- Let us assume that you have typed in a query and run it, getting the syntax error you see here. You need to change
C.CUST_INtoC.CUST_ID. Begin by making line 3 (the line with the error) the current line. Do this by typing L 3 at the SQL> prompt and pressing Enter. - Now you see line three of your buffer displayed. The next step is to change the incorrect column name. Use the
CHANGEcommand to do this. Type C/IN/ID/ and press Enter. - The line has been changed as you specified. Before you execute the query, list the entire buffer so you can see what the entire query looks like. To view the entire query, simply type L and press Enter.
- Here you see the query. There are four lines, numbered one through four. The line with the asterisk (*) is the current line in the buffer.The query looks okay, so execute the query now by typing a forward slash (/) and pressing Enter.
- The results of the query are displayed on the screen. You have succeeded in correcting the syntax error. This is the end of the simulation. Click Exit.
Direct SQL Access
Many application development environments provide direct SQL access to Oracle databases. The SQL commands shown in this module will work from within those tools, but the commands specific to
SQL*Plus (such as describe) will not.
If the command fails, there are several potential reasons: Oracle is not in your path, you are not authorized to use SQL*Plus, or Oracle has not been installed properly on your computer. If you get the message
either you have entered the username or password incorrectly or your username has not yet been set up properly on your copy of Oracle. After three unsuccessful attempts to enter a username and password that Oracle recognizes, SQL*Plus will terminate the attempt to log on, showing this message:
Assuming everything is in order, and the SQL> prompt has appeared, you may now begin working with SQL*Plus. When you want to quit working and leave SQL*Plus, type this:
The next lesson shows another way to edit your SQL*Plus commands.
If the command fails, there are several potential reasons: Oracle is not in your path, you are not authorized to use SQL*Plus, or Oracle has not been installed properly on your computer. If you get the message
ERROR: ORA-1017: invalid username/password; logon denied
either you have entered the username or password incorrectly or your username has not yet been set up properly on your copy of Oracle. After three unsuccessful attempts to enter a username and password that Oracle recognizes, SQL*Plus will terminate the attempt to log on, showing this message:
unable to CONNECT to ORACLE after 3 attempts, exiting SQL*PlusIf you get this message, contact the database administrator of your company.
Assuming everything is in order, and the SQL> prompt has appeared, you may now begin working with SQL*Plus. When you want to quit working and leave SQL*Plus, type this:
quitor
exit
The next lesson shows another way to edit your SQL*Plus commands.