DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01024 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01024: Invalid Datatype in OCI Call — Causes, Fixes & Prevention

ORA-01024 is thrown by Oracle when an OCI (Oracle Call Interface) call is made using a datatype that Oracle does not recognize or cannot process. This error typically originates at the application layer — Java, C, Python, .NET — rather than in SQL itself, making it a driver-level type mismatch issue. Because it occurs between the application and the database, you need to inspect both your application code and your database schema to resolve it effectively.

Top 3 Causes

1. Bind Variable Datatype Mismatch

The most common cause is registering a bind variable with a datatype that doesn't match the target column's Oracle datatype. For instance, binding a VARCHAR2 value to a NUMBER column without proper conversion will cause OCI to reject the call.

-- Safe approach: use explicit conversion inside SQL to avoid OCI type confusion
SELECT employee_id, first_name, salary
FROM employees
WHERE employee_id = TO_NUMBER(:bind_emp_id);

-- Always verify the actual column datatypes before binding
SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;
Enter fullscreen mode Exit fullscreen mode

2. Unsupported Oracle Datatype in the Driver

Some Oracle-specific types like XMLTYPE, INTERVAL, BFILE, or user-defined object types are not fully supported by older or certain OCI driver versions. When the driver encounters one of these types, it flags it as invalid and raises ORA-01024.

-- Convert XMLTYPE to CLOB for broader driver compatibility
CREATE OR REPLACE VIEW v_xml_compat AS
SELECT
    id,
    XMLTYPE.GETCLOBVAL(xml_column) AS xml_as_clob
FROM xml_data_table;

-- Convert INTERVAL to a plain NUMBER for safe OCI retrieval
SELECT
    order_id,
    EXTRACT(DAY FROM (delivery_date - order_date)) AS days_to_deliver
FROM orders
WHERE order_date >= SYSDATE - 90;
Enter fullscreen mode Exit fullscreen mode

3. PL/SQL OUT Parameter Type Not Supported by Driver

When calling stored procedures via OCI, complex OUT parameter types such as RECORD, associative arrays, or REF CURSOR mapped incorrectly can trigger ORA-01024. Wrapping the procedure with simple scalar OUT parameters resolves this.

-- OCI-friendly wrapper procedure using only scalar OUT parameters
CREATE OR REPLACE PROCEDURE get_emp_simple (
    p_emp_id   IN  NUMBER,
    p_name     OUT VARCHAR2,
    p_salary   OUT NUMBER,
    p_status   OUT NUMBER  -- 0 = success, 1 = not found
)
AS
BEGIN
    p_status := 0;
    SELECT first_name || ' ' || last_name, salary
    INTO   p_name, p_salary
    FROM   employees
    WHERE  employee_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name   := NULL;
        p_salary := NULL;
        p_status := 1;
END get_emp_simple;
/

-- Test the wrapper
DECLARE
    v_name   VARCHAR2(200);
    v_salary NUMBER;
    v_status NUMBER;
BEGIN
    get_emp_simple(100, v_name, v_salary, v_status);
    IF v_status = 0 THEN
        DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary);
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • Verify column types with USER_TAB_COLUMNS or ALL_TAB_COLUMNS before setting bind variables.
  • Use explicit type conversion (TO_NUMBER, TO_CHAR, TO_DATE) inside your SQL to reduce OCI-level type guessing.
  • Upgrade your OCI driver to match the Oracle DB server version — check Oracle's official Interoperability Matrix.
  • Replace complex OUT types with scalar equivalents in wrapper procedures for any PL/SQL called via OCI.
  • Test in a staging environment whenever you introduce a new Oracle datatype or upgrade the database version.

Prevention Tips

  1. Standardize datatype mappings early. Create a shared mapping document (or utility class) that defines how each Oracle datatype maps to your application language's types. Centralize all type conversions so there's no ambiguity when developers write new queries or procedures.

  2. Keep OCI drivers up to date and validated. Outdated drivers are the silent culprit behind many OCI-related errors. Establish a regular review cycle to align your Oracle Client version with the server version, and always run a regression test suite that exercises all critical OCI call paths after any driver or database upgrade.

Related Oracle Errors

Error Code Description
ORA-01025 UPI parameter out of range — related OCI parameter issue
ORA-03115 Unsupported network datatype — similar driver-level type problem
ORA-06502 PL/SQL numeric or value error — often accompanies type conversion failures
ORA-00932 Inconsistent datatypes — SQL-level type conflict
ORA-01722 Invalid number — implicit conversion failure due to type mismatch

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)