| Lesson 8 || When you make a mistake |
| Objective || Learn how to recover from a misspelled command. |
How to recover from Errors in Oracle
There are a few ways to recover from errors at the command line in Oracle if you make a mistake. Here are a few tips:
If you are unable to recover from an error on your own, you may need to consult the Oracle documentation or contact Oracle support for assistance. Here are some additional tips for avoiding errors at the command line in Oracle:
Avoiding errors at the Command Line in Oracle
- Carefully review your commands before executing them. Make sure that you have the correct syntax and that you are specifying the correct values for all parameters.
- Use the `EXPLAIN PLAN` command to review the execution plan for a query before executing it. This can help you to identify and avoid performance problems.
- Use the `SAVEPOINT` command to create savepoints within a transaction. This allows you to roll back to a specific point in time if you make a mistake.
- Use the `BACKUP` and `RESTORE` commands to back up and restore your database regularly. This will help you to recover from errors quickly and easily.
Reasons for SQL*Plus's behavior
Here is what is happening in a situation where the command line does not recognize the user input.
SQL*Plus is designed to let you enter and execute SQL statements. SQL statements may be many lines long, and SQL*Plus must allow for this. When you type in a command that is not one of the SQL*Plus specific commands,
exit, the program assumes that you are entering an SQL statement.
Once SQL*Plus thinks that you are entering an SQL statement, it accepts and buffers all text that you type until you terminate the statement, usually with a semicolon. Only at that point does it try to execute the statement.
How to rectify your mistake
The solution to the problem of mistyping a command is to enter a semicolon to terminate the statement, accept the error that occurs, and retry
your command. When you do this, SQL*Plus returns the word it does not recognize. Here is an example showing how to recover from the
exit command shown earlier:
If you are curious as to why SQL* Plus can not recognize
exite as a bad command, read this
SQL*Plus recognizes when you enter an incorrect command into the command line. It does this by parsing the command and checking it against a list of known commands and syntax rules. If SQL*Plus does not recognize the command or if the command contains invalid syntax, it will display an error message. For example, if you enter the following command:
SELECT * FROM emp WHERE salary > 1000000;
SQL*Plus will display the following error message:
ORA-00904: "SALARY" non-existent column
This error message indicates that SQL*Plus does not recognize the `salary` column in the `emp` table.
In addition to displaying error messages, SQL*Plus also provides a number of features to help you debug errors, such as the `SHOW ERRORS` command and the `SQLCODE` and `SQLERRM` variables. Here are some tips for avoiding errors at the command line in SQL*Plus:
- Carefully review your commands before executing them.
- Use the `EXPLAIN PLAN` command to review the execution plan for a query before executing it.
- Use the `SAVEPOINT` command to create savepoints within a transaction.
- Use the `BACKUP` and `RESTORE` commands to back up and restore your database regularly.
If you do make a mistake, SQL*Plus will provide you with the information you need to debug and fix the error.