{"id":2046,"date":"2025-10-09T04:03:21","date_gmt":"2025-10-09T08:03:21","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=2046"},"modified":"2025-10-07T04:06:05","modified_gmt":"2025-10-07T08:06:05","slug":"oracle-sql-error-cheat-sheet-common-errors-and-fixes","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/oracle-sql-error-cheat-sheet-common-errors-and-fixes\/","title":{"rendered":"Oracle SQL Error Cheat Sheet: Common Errors and Fixes"},"content":{"rendered":"\n<p>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>1. ORA-00904: Invalid Identifier<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Column name or alias does not exist, is misspelled, or reserved words are used incorrectly.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT moleculename FROM molecules;\n<\/code><\/pre>\n\n\n\n<p>If the actual column is <code>molecule_name<\/code>, this triggers ORA-00904.<\/p>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Verify column names with <code>DESCRIBE table_name;<\/code><\/li>\n\n\n\n<li>Avoid using reserved keywords as column names<\/li>\n\n\n\n<li>Use double quotes if a column name contains special characters:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \"Moleculename\" FROM molecules;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>2. ORA-00936: Missing Expression<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL statement is incomplete or missing a key component.<\/li>\n\n\n\n<li>Often occurs with <code>SELECT<\/code>, <code>INSERT<\/code>, or <code>WHERE<\/code> clauses.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT FROM molecules;\n<\/code><\/pre>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensure proper syntax is used, including column names:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT molecule_name FROM molecules;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>3. ORA-00933: SQL Command Not Properly Ended<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Incorrect syntax or extra characters such as trailing semicolons in subqueries or incorrect <code>JOIN<\/code> usage.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT molecule_name FROM molecules WHERE id = 1;;\n<\/code><\/pre>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Remove extra semicolons inside queries<\/li>\n\n\n\n<li>Check <code>JOIN<\/code> syntax carefully:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT a.molecule_name, b.formula\nFROM molecules a\nJOIN formulas b ON a.id = b.molecule_id;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>4. ORA-00932: Inconsistent Datatypes<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Column and value types do not match, such as trying to insert text into a numeric column.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO molecules (id, molecule_name) VALUES ('one', 'Water');\n<\/code><\/pre>\n\n\n\n<p>If <code>id<\/code> is NUMBER, this triggers ORA-00932.<\/p>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Convert values to the correct type using <code>TO_NUMBER<\/code> or <code>TO_CHAR<\/code> as needed:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO molecules (id, molecule_name) VALUES (1, 'Water');\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>5. ORA-00001: Unique Constraint Violated<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Trying to insert a duplicate value into a column defined as <code>PRIMARY KEY<\/code> or <code>UNIQUE<\/code>.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO molecules (id, molecule_name) VALUES (1, 'Water');\n<\/code><\/pre>\n\n\n\n<p>If <code>id = 1<\/code> already exists, this triggers ORA-00001.<\/p>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Check existing values with:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM molecules WHERE id = 1;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use a different primary key or implement <code>SEQUENCE<\/code> for automatic IDs.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>6. ORA-01400: Cannot Insert NULL into Column<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Attempting to insert a NULL value into a <code>NOT NULL<\/code> column.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO molecules (id, molecule_name) VALUES (2, NULL);\n<\/code><\/pre>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Provide a valid value for the column<\/li>\n\n\n\n<li>Or, remove the <code>NOT NULL<\/code> constraint if appropriate<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO molecules (id, molecule_name) VALUES (2, 'Ethanol');\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>7. ORA-02291 \/ ORA-02292: Integrity Constraint Violated<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ORA-02291: Foreign key references a non-existent primary key<\/li>\n\n\n\n<li>ORA-02292: Attempt to delete a record referenced by a foreign key<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO formulas (molecule_id, formula) VALUES (99, 'H2O');\n<\/code><\/pre>\n\n\n\n<p>If <code>molecule_id = 99<\/code> does not exist in <code>molecules<\/code>, triggers ORA-02291.<\/p>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensure referenced keys exist before inserting<\/li>\n\n\n\n<li>Use <code>ON DELETE CASCADE<\/code> if appropriate<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO molecules (id, molecule_name) VALUES (99, 'Water');\nINSERT INTO formulas (molecule_id, formula) VALUES (99, 'H2O');\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>8. ORA-06502: PL\/SQL: Numeric or Value Error<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Mismatch between variable and column types in PL\/SQL, or data too large for variable.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  name_var VARCHAR2(5);\nBEGIN\n  name_var := 'MoleculeName';\nEND;\n<\/code><\/pre>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Increase variable length or truncate data appropriately:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  name_var VARCHAR2(20);\nBEGIN\n  name_var := 'MoleculeName';\nEND;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>9. ORA-01017: Invalid Username\/Password<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database login failure due to incorrect credentials.<\/li>\n<\/ul>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Verify username and password<\/li>\n\n\n\n<li>Check database service and connection string<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>sqlplus username\/password@DBService\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>10. ORA-12154: TNS: Could Not Resolve Service Name<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle client cannot locate database service.<\/li>\n<\/ul>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Verify <code>tnsnames.ora<\/code> configuration<\/li>\n\n\n\n<li>Ensure service name matches case and spelling<\/li>\n\n\n\n<li>Test connectivity using:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>tnsping DBService\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>11. ORA-01722: Invalid Number<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Conversion of a string to a number fails during insert or select.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM molecules WHERE id = 'abc';\n<\/code><\/pre>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensure numeric fields receive numeric values<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM molecules WHERE id = 10;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>12. ORA-01555: Snapshot Too Old<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Large queries or long-running operations cause undo tablespace to overflow.<\/li>\n<\/ul>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Increase undo tablespace<\/li>\n\n\n\n<li>Break query into smaller batches<\/li>\n\n\n\n<li>Avoid long transactions<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>13. ORA-06512: PL\/SQL Line Error<\/strong><\/h2>\n\n\n\n<p><strong>Cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Generic line error in PL\/SQL block, often accompanies another error.<\/li>\n<\/ul>\n\n\n\n<p><strong>Fix:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Check the referenced line number in the PL\/SQL block<\/li>\n\n\n\n<li>Fix underlying error before rerunning<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Tips for Avoiding Oracle Errors<\/strong><\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Use <code>DESCRIBE<\/code><\/strong> to verify table structures.<\/li>\n\n\n\n<li><strong>Validate input types<\/strong> before inserting or updating data.<\/li>\n\n\n\n<li><strong>Test queries in small steps<\/strong> to isolate errors.<\/li>\n\n\n\n<li><strong>Use transactions (<code>COMMIT<\/code>\/<code>ROLLBACK<\/code>)<\/strong> to avoid partial data issues.<\/li>\n\n\n\n<li><strong>Enable SQL*Plus error stack (<code>SHOW ERRORS<\/code>)<\/strong> for PL\/SQL debugging.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Quick Reference Table<\/strong><\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Error Code<\/th><th>Meaning<\/th><th>Quick Fix<\/th><\/tr><\/thead><tbody><tr><td>ORA-00904<\/td><td>Invalid column or identifier<\/td><td>Check column names, reserved words<\/td><\/tr><tr><td>ORA-00936<\/td><td>Missing expression<\/td><td>Complete SQL statement syntax<\/td><\/tr><tr><td>ORA-00933<\/td><td>SQL command not properly ended<\/td><td>Remove extra semicolons, fix JOIN<\/td><\/tr><tr><td>ORA-00932<\/td><td>Inconsistent datatypes<\/td><td>Match data types using TO_NUMBER \/ TO_CHAR<\/td><\/tr><tr><td>ORA-00001<\/td><td>Unique constraint violated<\/td><td>Ensure primary key uniqueness<\/td><\/tr><tr><td>ORA-01400<\/td><td>Cannot insert NULL<\/td><td>Provide valid value or remove NOT NULL<\/td><\/tr><tr><td>ORA-02291<\/td><td>Foreign key violation<\/td><td>Ensure referenced key exists<\/td><\/tr><tr><td>ORA-02292<\/td><td>Child record exists<\/td><td>Delete dependent records first<\/td><\/tr><tr><td>ORA-06502<\/td><td>Numeric or value error<\/td><td>Adjust variable size \/ type<\/td><\/tr><tr><td>ORA-01017<\/td><td>Invalid username\/password<\/td><td>Verify credentials<\/td><\/tr><tr><td>ORA-12154<\/td><td>TNS service not found<\/td><td>Check <code>tnsnames.ora<\/code><\/td><\/tr><tr><td>ORA-01722<\/td><td>Invalid number<\/td><td>Ensure numeric values for numeric columns<\/td><\/tr><tr><td>ORA-01555<\/td><td>Snapshot too old<\/td><td>Increase undo tablespace or batch queries<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>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: Example: If the actual column is molecule_name, this triggers ORA-00904. Fix: 2. ORA-00936: [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,515],"tags":[],"class_list":["post-2046","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=2046"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2046\/revisions"}],"predecessor-version":[{"id":2047,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2046\/revisions\/2047"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=2046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=2046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=2046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}