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:

• Use the `>` operator to redirect output to a file. This way, you can review the output later if you need to troubleshoot the error. For example:

  "
  sqlplus scott/tiger > output.txt
  "
  

• Use the `SPOOL` command to spool output to a file. This is similar to redirecting output to a file, but it allows you to pause and resume spooling. For example:
  

  "
  sqlplus scott/tiger
  SPOOL output.txt
  SELECT * FROM emp;
  SPOOL OFF
  "
  

• Use the `ROLLBACK` command to roll back all changes made since the last commit. This can be useful if you make a mistake and need to revert your changes. For example:
  

  "
  sqlplus scott/tiger
  BEGIN
    UPDATE emp SET salary = salary * 1.1;
    COMMIT;
  END;
  /
  ROLLBACK;
  "
  

• Use the `SHOW ERRORS` command to display detailed error information. This can be helpful in diagnosing the cause of an error. For example:
  

  "
  sqlplus scott/tiger
  BEGIN
    UPDATE emp SET salary = salary * 1.1 WHERE empno = 100;
  END;
  /
  SHOW ERRORS;
  "
  

• Use the `SQLCODE` and `SQLERRM` variables to get more information about an error. The `SQLCODE` variable contains the error code, and the `SQLERRM` variable contains the error message. For example:
  

  "
  sqlplus scott/tiger
  DECLARE
    v_sqlcode NUMBER;
    v_sqlerrm VARCHAR2(200);
  BEGIN
    BEGIN
      UPDATE emp SET salary = salary * 1.1 WHERE empno = 100;
    EXCEPTION
      WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        v_sqlerrm := SQLERRM;
        RAISE_APPLICATION_ERROR(-20001, v_sqlerrm);
    END;
  END;
  /
  "
  

1. 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.
2. 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.
3. 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.
4. 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.

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, such as help or 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.

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 mistyped 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:

1. Carefully review your commands before executing them.
2. Use the `EXPLAIN PLAN` command to review the execution plan for a query before executing it.
3. Use the `SAVEPOINT` command to create savepoints within a transaction.
4. 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.