{"id":2027,"date":"2025-09-22T21:09:52","date_gmt":"2025-09-23T01:09:52","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=2027"},"modified":"2025-09-18T21:21:39","modified_gmt":"2025-09-19T01:21:39","slug":"solving-ora-00932-inconsistent-datatypes-expected-type1-but-got-type2","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/solving-ora-00932-inconsistent-datatypes-expected-type1-but-got-type2\/","title":{"rendered":"Solving ORA-00932: Inconsistent datatypes: expected “type1” but got “type2”"},"content":{"rendered":"\n

This is an Oracle Database error<\/strong> that happens when Oracle expects one type of data (like a number, date, or string) but finds a different, incompatible type during SQL execution.<\/p>\n\n\n\n

What Triggers It<\/strong><\/h2>\n\n\n\n
    \n
  1. Mismatched Data Types in Expressions or Comparisons<\/strong>\n
      \n
    • Comparing a VARCHAR2<\/code> (string) column to a NUMBER<\/code> column without proper conversion.<\/li>\n\n\n\n
    • Example: WHERE salary = 'ABC'<\/code>.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
    • Invalid Function Arguments<\/strong>\n
        \n
      • Passing a string to a function that expects a number, or vice versa.<\/li>\n\n\n\n
      • Example: TO_CHAR(123, SYSDATE)<\/code> \u2013 here SYSDATE<\/code> is a date, not a number format.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
      • UNION or UNION ALL Queries with Different Column Types<\/strong>\n
          \n
        • Combining queries where corresponding columns don\u2019t share the same data type.<\/li>\n\n\n\n
        • Example: First query returns a NUMBER<\/code>, second query returns a VARCHAR2<\/code> in the same column position.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
        • INSERT or UPDATE with Wrong Type<\/strong>\n
            \n
          • Trying to insert a string into a numeric column.<\/li>\n\n\n\n
          • Example: INSERT INTO employees (employee_id) VALUES ('ABC')<\/code>.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
          • PL\/SQL Assignments or Bind Variables<\/strong>\n
              \n
            • Assigning a cursor column of type DATE<\/code> to a VARCHAR2<\/code> variable without conversion.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

              How to Solve It<\/strong><\/h2>\n\n\n\n
                \n
              1. Check Column and Variable Types<\/strong>\n
                  \n
                • Use DESC table_name<\/code> to inspect column definitions.<\/li>\n\n\n\n
                • Ensure both sides of a comparison or assignment are compatible.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                • Use Explicit Conversion Functions<\/strong>\n
                    \n
                  • TO_NUMBER(string)<\/code> \u2013 convert string to number.<\/li>\n\n\n\n
                  • TO_CHAR(date_or_number, format)<\/code> \u2013 convert date\/number to string.<\/li>\n\n\n\n
                  • TO_DATE(string, format)<\/code> \u2013 convert string to date.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                  • Align Data Types in Set Operations<\/strong>\n
                      \n
                    • In UNION<\/code>, INTERSECT<\/code>, or MINUS<\/code>, cast columns to the same type: SELECT CAST(emp_id AS VARCHAR2(10)) FROM employees UNION SELECT emp_code FROM contractors;<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n
                    • Fix Wrong Inserts or Updates<\/strong>\n
                        \n
                      • Make sure the value matches the column type. Convert if needed: INSERT INTO employees (hire_date) VALUES (TO_DATE('2025-09-18','YYYY-MM-DD'));<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n
                      • Check PL\/SQL Variables<\/strong>\n
                          \n
                        • Declare variables with correct data types or use conversions.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

                          Real-Life Examples<\/strong><\/h2>\n\n\n\n

                          1. Comparing a Number with a String<\/strong><\/h3>\n\n\n\n
                          -- employees.salary is NUMBER\nSELECT * FROM employees WHERE salary = 'high';  \n<\/code><\/pre>\n\n\n\n
                          Oracle expects NUMBER<\/code>, but gets a VARCHAR2<\/code>.<\/strong><\/p>\n\n\n\n
                          Fix:<\/p>\n\n\n\n
                          -- Either compare with a number:\nSELECT * FROM employees WHERE salary = 50000;\n-- Or convert appropriately if stored as string elsewhere.\n<\/code><\/pre>\n\n\n\n
                          2. UNION with Mismatched Types<\/strong><\/h3>\n\n\n\n
                          SELECT emp_id FROM employees      -- emp_id is NUMBER\nUNION\nSELECT emp_code FROM contractors; -- emp_code is VARCHAR2\n<\/code><\/pre>\n\n\n\n
                          Oracle can\u2019t union a number and string.<\/strong><\/p>\n\n\n\n
                          Fix:<\/p>\n\n\n\n
                          SELECT TO_CHAR(emp_id) FROM employees\nUNION\nSELECT emp_code FROM contractors;\n<\/code><\/pre>\n\n\n\n
                          3. Inserting Wrong Data<\/strong><\/h3>\n\n\n\n
                          -- hire_date is DATE\nINSERT INTO employees (hire_date) VALUES ('2025-09-18');\n<\/code><\/pre>\n\n\n\n
                          Oracle sees a string, not a date.<\/strong><\/p>\n\n\n\n
                          Fix:<\/p>\n\n\n\n
                          INSERT INTO employees (hire_date)\nVALUES (TO_DATE('2025-09-18','YYYY-MM-DD'));\n<\/code><\/pre>\n\n\n\n
                          4. Function Argument Type Mismatch<\/strong><\/h3>\n\n\n\n
                          -- TO_CHAR expects a number\/date then format string, but SYSDATE is used incorrectly\nSELECT TO_CHAR(123, SYSDATE) FROM dual;\n<\/code><\/pre>\n\n\n\n
                          Wrong argument types.<\/strong><\/p>\n\n\n\n
                          Fix:<\/p>\n\n\n\n
                          SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;\n<\/code><\/pre>\n\n\n\n
                          Summary in Simple Terms<\/strong><\/h2>\n\n\n\n
                            \n
                          • Oracle is strict about data types.<\/li>\n\n\n\n
                          • This error means \u201cI expected apples but you gave me oranges.\u201d<\/strong><\/li>\n\n\n\n
                          • It happens in comparisons, unions, inserts, or functions where types don\u2019t match.<\/li>\n\n\n\n
                          • Solution:<\/strong>\n
                              \n
                            1. Check column and variable types.<\/li>\n\n\n\n
                            2. Use the correct conversion (TO_NUMBER<\/code>, TO_CHAR<\/code>, TO_DATE<\/code>).<\/li>\n\n\n\n
                            3. Make sure combined queries use matching types.<\/li>\n\n\n\n
                            4. Insert\/update values that match the column definitions.<\/li>\n<\/ol>\n<\/li>\n<\/ul>\n\n\n\n
                              By consistently aligning your data types or using explicit conversions, you can avoid ORA-00932<\/code>.<\/p>\n","protected":false},"excerpt":{"rendered":"
                              This is an Oracle Database error that happens when Oracle expects one type of data (like a number, date, or string) but finds a different, incompatible type during SQL execution. What Triggers It How to Solve It Real-Life Examples 1. Comparing a Number with a String Oracle expects NUMBER, but gets a VARCHAR2. Fix: 2. […]<\/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-2027","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2027","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=2027"}],"version-history":[{"count":2,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2027\/revisions"}],"predecessor-version":[{"id":2031,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2027\/revisions\/2031"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=2027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=2027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=2027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}