Common Oracle and SQL Errors Explained (ORA Codes and Fixes)

ORA-06502 PL/SQL numeric or value error fires when Oracle cannot store a value in a variable because the value is too large for the declared size or is the wrong data type. Fix it by increasing the variable size, anchoring declarations with %TYPE, or using explicit conversion functions such as TO_NUMBER or TO_DATE with a matching format mask.

• ORA-06502 means a value does not fit or cannot be converted into the target variable.
• ORA-00904 fires when Oracle cannot find a column name, usually due to a typo or missing alias.
• SQL error code 17002 is a JDBC network error, not an Oracle database error, so the fix lives in your connection config.
• ORA-01422 means your query returned more than one row where exactly one was expected.
• Most of these errors are preventable with strict variable declarations, explicit type conversions, and disciplined query design.

ORA-06502 and ORA-00904: The Two Errors Developers Hit First

What Triggers ORA-06502 PL/SQL Numeric or Value Error

The ORA-06502 PL/SQL numeric or value error is Oracle’s way of saying “I cannot put this value here.” The three most common triggers are: assigning a string longer than the declared VARCHAR2 length, trying to store a non-numeric string in a NUMBER variable, or hitting an arithmetic overflow. According to Oracle’s official documentation (Oracle Database Error Messages, 19c), this error belongs to the VALUE_ERROR exception family and can be trapped with a standard EXCEPTION WHEN VALUE_ERROR block in your PL/SQL exception handling section.

A typical example: you declare v_name VARCHAR2(10) but then try to assign a 25-character string to it. Oracle does not truncate silently. It throws ORA-06502 PL/SQL numeric or value error immediately. The fix is straightforward: increase the variable size to match the actual data, or add a SUBSTR call before assignment to control the length explicitly. In Indian banking core systems such as Finacle or BaNCS, this error commonly fires during batch processing when account number fields exceed their declared VARCHAR2 bounds after a schema change.

Another common trigger of the ORA-06502 PL/SQL numeric or value error is the “literal does not match format string” variant. This appears when you use TO_DATE or TO_NUMBER with a format mask that does not match the incoming string. For example, passing '15-JAN-2024' with a format of 'YYYY-MM-DD' will throw ORA-06502 with the literal mismatch message. Always validate your format masks against real data before deploying to production.

The most reliable long-term fix for ORA-06502 PL/SQL numeric or value error is to anchor all variable declarations using %TYPE. Instead of writing v_salary NUMBER(8,2), write v_salary employees.salary%TYPE. If the column definition changes, your variable changes with it automatically. This single habit eliminates a large category of ORA-06502 errors caused by schema drift, and it is standard practice in Oracle PL/SQL exception handling at enterprise scale.

Fixing ORA-00904 Invalid Identifier

ORA-00904 is almost always a typo or a scope problem. Oracle returns “invalid identifier” when the column or alias name in your SQL statement does not exist in the table, view, or result set being queried. A column name with a reserved word, an unquoted mixed-case identifier, or a reference to a column alias in the same SELECT clause can all cause it.

The fastest fix: run DESC tablename to confirm the exact column names. Oracle column names are case-insensitive unless you created them with double quotes. If someone created a column as "First Name" with a space, you must reference it with double quotes every time. Remove that ambiguity from your schema design wherever you can.

ORA-00904 also appears in WHERE clauses that reference a SELECT alias. Oracle evaluates the WHERE clause before the SELECT list, so the alias does not exist yet at that point. Use a subquery or a CTE to work around it.

ORA-00933, ORA-01422, and SQL Error Code 17002

ORA-00933: SQL Command Not Properly Ended

ORA-00933 means Oracle hit something unexpected at the end of your SQL statement. The usual suspects are an ORDER BY clause inside a subquery or inline view (which Oracle does not allow before version 12c without specific syntax), a semicolon inside a JDBC string, or a clause that belongs to one SQL dialect but not Oracle’s. For example, MySQL’s LIMIT keyword does not exist in Oracle SQL. Use ROWNUM or FETCH FIRST n ROWS ONLY instead.

Check the exact character position Oracle reports in the error. It points directly to where parsing failed. Strip your query back to its simplest form, confirm it runs, then add clauses one at a time until you reproduce the error.

ORA-01422: Exact Fetch Returns More Than Requested Number of Rows

ORA-01422 fires when a SELECT INTO statement in PL/SQL returns more than one row. PL/SQL’s SELECT INTO expects exactly one row. If your WHERE clause is not selective enough, Oracle throws this error rather than silently picking one row. The fix is to either tighten your WHERE conditions, use an aggregate function like MAX or MIN, or switch to an explicit cursor if multiple rows are a valid business scenario.

Handle it defensively with EXCEPTION WHEN TOO_MANY_ROWS in your PL/SQL block. That lets you log the condition and respond gracefully instead of crashing the procedure. This is the same PL/SQL exception handling pattern used for ORA-06502 PL/SQL numeric or value error.

SQL Error Code 17002: What It Actually Means

SQL error code 17002 is not an Oracle database error. It is a JDBC vendor error that means the Java client could not establish or maintain a network connection to the Oracle server. The full message is usually “IO Error: The Network Adapter could not establish the connection.” According to Oracle Support (MOS Note 1430591.1), the most common causes are a wrong hostname or port in the JDBC URL, a firewall blocking port 1521, or the Oracle listener not running.

Start debugging by pinging the Oracle host, then use telnet hostname 1521 to confirm the port is open. Check that the listener is running with lsnrctl status on the server. If you are connecting from an application deployed on AWS or Azure to an on-premises Oracle instance in India, a common setup for Indian IT companies using hybrid infrastructure, verify that the security group or firewall rules allow outbound traffic on port 1521.

Cross-Database Errors: DB2 SQLCODE -104 and nvarchar to datetime Conversion

DB2 SQLCODE -104 SQLSTATE 42601

DB2’s SQLCODE -104 with SQLSTATE 42601 is the DB2 equivalent of ORA-00933. It means DB2 found an illegal symbol in your SQL statement. Common causes include using Oracle-specific syntax like ROWNUM or SYSDATE in a DB2 query, missing commas between column definitions in a CREATE TABLE, or an unrecognised keyword. The error message usually names the illegal token and lists what DB2 expected to see instead. Read that list carefully; it almost always points straight to the fix.

Error Converting Data Type nvarchar to datetime

This error is SQL Server-specific, not Oracle, but it comes up frequently in Indian IT projects where teams work across both Oracle and SQL Server environments. It fires when SQL Server tries to implicitly convert an nvarchar string into a datetime column and the string format is not one SQL Server recognises. Strings like '30-01-2024' or '2024/30/01' will fail because SQL Server’s implicit conversion expects formats like '2024-01-30' (ISO 8601) or locale-specific formats set at the server level.

The fix is to use CONVERT(datetime, '30-01-2024', 103) with the explicit style code (103 is British/French dd/mm/yyyy), or use TRY_CONVERT to handle bad data gracefully without crashing the query. Never rely on implicit conversion for date strings. Always cast explicitly.

Quick Reference: Common Oracle and SQL Error Codes

A 2023 Stack Overflow Developer Survey found that Oracle is used by 11.5% of professional developers globally, and SQL Server by 34.7%. Both databases remain dominant in Indian enterprise IT, particularly in BFSI (banking, financial services, and insurance) and government projects, which means these errors are something you will genuinely encounter on the job, not just in exams.

According to Redgate’s 2023 State of the Database report, 68% of database incidents in production are caused by human error in SQL code, not infrastructure failures. That figure underlines why understanding ORA-06502 PL/SQL numeric or value error and related codes at a practical level is a genuine career differentiator. NASSCOM’s 2023 India IT Workforce Report notes that Oracle and SQL Server skills remain among the top five most requested technical competencies in Indian BFSI and government IT hiring, confirming that these errors are not academic edge cases.

How to Prevent These Errors Before They Hit Production

The single most effective habit is to declare PL/SQL variables using %TYPE anchoring. Instead of v_salary NUMBER(8,2), write v_salary employees.salary%TYPE. If the column definition changes, your variable changes with it automatically. This alone eliminates a large category of ORA-06502 PL/SQL numeric or value error instances caused by schema drift, and it is the foundation of sound PL/SQL exception handling.

For date conversions, establish a team standard. Pick one format, document it, and enforce it through code review. In Oracle, that usually means using TO_DATE(string, 'YYYY-MM-DD') consistently. In SQL Server, it means always using CONVERT with a style code or using the datetimeoffset type with ISO 8601 strings. Consistency across a team prevents the “literal does not match format string” class of ORA-06502 PL/SQL numeric or value error almost entirely.

For ORA-01422, add a ROWNUM = 1 or an explicit FETCH FIRST 1 ROW ONLY only when you are genuinely certain one row is correct. If there is any chance the business rule allows multiple rows, use a cursor. Write the cursor version first, even if it feels like more code. It is safer.

If you are building a career in database development or backend engineering, understanding ORA-06502 PL/SQL numeric or value error and related codes at this level is exactly the kind of practical skill that separates candidates at interview. It is also the kind of knowledge covered in the top technical skills every college student should learn before graduation. Knowing how to read and resolve error codes shows you can work independently without waiting for a senior to explain what went wrong.

Professionals switching into database or backend roles can find structured paths in our guide to the best career options in 2026 for students and professionals. If you are still figuring out which direction to take, the guide on how to choose the right career path after graduation is worth reading before you commit to a specialisation.

For students who want to build these skills without paying for expensive bootcamps, check out the roundup of best free online learning platforms for students in 2025. And if you are looking at which certifications actually pay off, the list of top online courses for high-salary jobs in 2025 breaks it down with salary data.

Fresh graduates and teens who want to build a portfolio with real database projects can find practical starting points in the guide to entry-level jobs for teens and graduates. Pairing that with hands-on SQL practice is one of the fastest ways to build credibility before your first formal role.

3.0 University’s certification courses in Cybersecurity, Ethical Hacking, AI, Blockchain, and Web3 are built for exactly this audience. Whether you are a student in Pune, a working professional in Bengaluru, or a career switcher in Delhi, the 3.0 University courses page has structured, industry-aligned programmes that go well beyond theory. These are skills employers in Indian IT are actively hiring for right now.

Frequently Asked Questions

What causes ORA-06502 PL/SQL numeric or value error?

ORA-06502 PL/SQL numeric or value error fires when Oracle tries to assign a value to a PL/SQL variable but the value is either too large for the declared size or the wrong data type entirely. The most common triggers are a VARCHAR2 variable that is too short for the incoming string, a non-numeric string being assigned to a NUMBER, or a format mask mismatch in TO_DATE or TO_NUMBER. Fix it by anchoring variable declarations with %TYPE and using explicit conversion functions. Trap it in your PL/SQL exception handling block with EXCEPTION WHEN VALUE_ERROR.

What is the difference between ORA-06502 and VALUE_ERROR in PL/SQL?

They refer to the same condition. ORA-06502 is the Oracle error code that appears in the error stack, while VALUE_ERROR is the named PL/SQL exception you use in an EXCEPTION WHEN VALUE_ERROR block to catch it programmatically. When ORA-06502 PL/SQL numeric or value error fires at runtime, Oracle raises the VALUE_ERROR exception internally. You can catch it, log it, and handle it gracefully without the procedure crashing.

How do you fix ORA-00904 invalid identifier?

Run DESC tablename to confirm the exact column names in your table. ORA-00904 usually means you have mistyped a column name, referenced a column alias in a WHERE clause (which Oracle evaluates before the SELECT list), or used a double-quoted identifier inconsistently. Fix the typo, move the alias reference into a subquery, or remove unnecessary double quotes from column names in your schema.

What does SQL error code 17002 mean?

SQL error code 17002 is a JDBC-level network error, not an Oracle database error. It means the Java client could not connect to the Oracle server. Check that your JDBC URL has the correct hostname and port (default 1521), confirm the Oracle listener is running with lsnrctl status, and verify that no firewall or security group is blocking the connection. It has nothing to do with your SQL syntax or ORA-06502 PL/SQL numeric or value error.

How do you resolve ORA-01422?

ORA-01422 means your SELECT INTO returned more than one row. Either add a more selective WHERE condition to guarantee one row, use an aggregate function like MAX or MIN if that fits your logic, or switch to an explicit cursor that can handle multiple rows safely. Add EXCEPTION WHEN TOO_MANY_ROWS to your PL/SQL block to catch this gracefully in production without crashing the procedure.

Why does nvarchar to datetime conversion fail in SQL Server?

SQL Server’s implicit conversion from nvarchar to datetime fails when the string format does not match the server’s expected locale format. Strings like '30-01-2024' are not recognised by default. Use CONVERT(datetime, your_string, style_code) with an explicit style code, such as 103 for dd/mm/yyyy, or use TRY_CONVERT to handle bad rows without throwing an error. Never rely on implicit date string conversion in production queries.

Last updated: September 2026. Reviewed by the 3University editorial team.