Oracle SQL Error Cheat Sheet: Common Errors and Fixes

Oracle SQL is powerful but can be tricky. Even experienced users frequently encounter errors due to syntax issues, data type mismatches, or database constraints. This cheat sheet explains the most common errors, their causes, and actionable fixes.

1. ORA-00904: Invalid Identifier

Cause:

• Column name or alias does not exist, is misspelled, or reserved words are used incorrectly.

Example:

SELECT moleculename FROM molecules;

If the actual column is molecule_name, this triggers ORA-00904.

Fix:

• Verify column names with DESCRIBE table_name;
• Avoid using reserved keywords as column names
• Use double quotes if a column name contains special characters:

SELECT "Moleculename" FROM molecules;

2. ORA-00936: Missing Expression

Cause:

• SQL statement is incomplete or missing a key component.
• Often occurs with SELECT, INSERT, or WHERE clauses.

Example:

SELECT FROM molecules;

Fix:

• Ensure proper syntax is used, including column names:

SELECT molecule_name FROM molecules;

3. ORA-00933: SQL Command Not Properly Ended

Cause:

• Incorrect syntax or extra characters such as trailing semicolons in subqueries or incorrect JOIN usage.

Example:

SELECT molecule_name FROM molecules WHERE id = 1;;

Fix:

• Remove extra semicolons inside queries
• Check JOIN syntax carefully:

SELECT a.molecule_name, b.formula
FROM molecules a
JOIN formulas b ON a.id = b.molecule_id;

4. ORA-00932: Inconsistent Datatypes

Cause:

• Column and value types do not match, such as trying to insert text into a numeric column.

Example:

INSERT INTO molecules (id, molecule_name) VALUES ('one', 'Water');

If id is NUMBER, this triggers ORA-00932.

Fix:

• Convert values to the correct type using TO_NUMBER or TO_CHAR as needed:

INSERT INTO molecules (id, molecule_name) VALUES (1, 'Water');

5. ORA-00001: Unique Constraint Violated

Cause:

• Trying to insert a duplicate value into a column defined as PRIMARY KEY or UNIQUE.

Example:

INSERT INTO molecules (id, molecule_name) VALUES (1, 'Water');

If id = 1 already exists, this triggers ORA-00001.

Fix:

• Check existing values with:

SELECT * FROM molecules WHERE id = 1;

• Use a different primary key or implement SEQUENCE for automatic IDs.

6. ORA-01400: Cannot Insert NULL into Column

Cause:

• Attempting to insert a NULL value into a NOT NULL column.

Example:

INSERT INTO molecules (id, molecule_name) VALUES (2, NULL);

Fix:

• Provide a valid value for the column
• Or, remove the NOT NULL constraint if appropriate

INSERT INTO molecules (id, molecule_name) VALUES (2, 'Ethanol');

7. ORA-02291 / ORA-02292: Integrity Constraint Violated

Cause:

• ORA-02291: Foreign key references a non-existent primary key
• ORA-02292: Attempt to delete a record referenced by a foreign key

Example:

INSERT INTO formulas (molecule_id, formula) VALUES (99, 'H2O');

If molecule_id = 99 does not exist in molecules, triggers ORA-02291.

Fix:

• Ensure referenced keys exist before inserting
• Use ON DELETE CASCADE if appropriate

INSERT INTO molecules (id, molecule_name) VALUES (99, 'Water');
INSERT INTO formulas (molecule_id, formula) VALUES (99, 'H2O');

8. ORA-06502: PL/SQL: Numeric or Value Error

Cause:

• Mismatch between variable and column types in PL/SQL, or data too large for variable.

Example:

DECLARE
  name_var VARCHAR2(5);
BEGIN
  name_var := 'MoleculeName';
END;

Fix:

• Increase variable length or truncate data appropriately:

DECLARE
  name_var VARCHAR2(20);
BEGIN
  name_var := 'MoleculeName';
END;

9. ORA-01017: Invalid Username/Password

Cause:

• Database login failure due to incorrect credentials.

Fix:

• Verify username and password
• Check database service and connection string

sqlplus username/password@DBService

10. ORA-12154: TNS: Could Not Resolve Service Name

Cause:

• Oracle client cannot locate database service.

Fix:

• Verify tnsnames.ora configuration
• Ensure service name matches case and spelling
• Test connectivity using:

tnsping DBService

11. ORA-01722: Invalid Number

Cause:

• Conversion of a string to a number fails during insert or select.

Example:

SELECT * FROM molecules WHERE id = 'abc';

Fix:

• Ensure numeric fields receive numeric values

SELECT * FROM molecules WHERE id = 10;

12. ORA-01555: Snapshot Too Old

Cause:

• Large queries or long-running operations cause undo tablespace to overflow.

Fix:

• Increase undo tablespace
• Break query into smaller batches
• Avoid long transactions

13. ORA-06512: PL/SQL Line Error

Cause:

• Generic line error in PL/SQL block, often accompanies another error.

Fix:

• Check the referenced line number in the PL/SQL block
• Fix underlying error before rerunning

Tips for Avoiding Oracle Errors

1. Use DESCRIBE to verify table structures.
2. Validate input types before inserting or updating data.
3. Test queries in small steps to isolate errors.
4. Use transactions (COMMIT/ROLLBACK) to avoid partial data issues.
5. Enable SQL*Plus error stack (SHOW ERRORS) for PL/SQL debugging.

Quick Reference Table