Oracle ORA-00938 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00938: Not Enough Arguments for Function

ORA-00938 is thrown by Oracle when a function call is made with fewer arguments than the function requires. Whether it's a built-in Oracle function or a user-defined function, every function has a minimum number of required parameters, and missing even one will trigger this error immediately at parse time.

Top 3 Causes

1. Missing Required Arguments in Built-in Functions

Oracle's built-in functions have strict minimum argument requirements. Calling them with too few arguments causes ORA-00938 instantly.

-- WRONG: REPLACE requires at least 2 arguments
SELECT REPLACE('Hello World') FROM DUAL;
-- Error: ORA-00938: not enough arguments for function

-- CORRECT: Provide all required arguments
SELECT REPLACE('Hello World', 'World', 'Oracle') FROM DUAL;
-- Result: Hello Oracle

-- WRONG: SUBSTR requires at least 2 arguments (string + position)
SELECT SUBSTR('OracleDB') FROM DUAL;
-- Error: ORA-00938

-- CORRECT
SELECT SUBSTR('OracleDB', 1, 6) FROM DUAL;
-- Result: Oracle

2. User-Defined Function Signature Changed Without Updating Callers

When a UDF gets a new mandatory parameter added during development, all existing call sites must be updated. Forgetting even one causes ORA-00938 at runtime.

-- Original function: get_salary(p_emp_id NUMBER)
-- Updated function: get_salary(p_emp_id NUMBER, p_dept_id NUMBER)

-- WRONG: Old call missing the new required argument
SELECT get_salary(101) FROM DUAL;
-- Error: ORA-00938

-- CORRECT: Updated call with both arguments
SELECT get_salary(101, 10) FROM DUAL;

-- Find all affected objects referencing this function
SELECT name, type, line, text
FROM   all_source
WHERE  UPPER(text) LIKE '%GET_SALARY%'
  AND  owner = 'YOUR_SCHEMA'
ORDER BY type, name, line;

3. Argument Dropped in Dynamic SQL String Assembly

Dynamic SQL built at runtime can silently drop function arguments due to string concatenation bugs. These are harder to catch because Oracle cannot validate them at compile time.

DECLARE
    v_sql    VARCHAR2(1000);
    v_result VARCHAR2(100);
BEGIN
    -- WRONG: Argument accidentally omitted during string build
    v_sql := 'SELECT NVL(' || NULL || ') FROM DUAL';
    DBMS_OUTPUT.PUT_LINE('Bad SQL: ' || v_sql);

    -- CORRECT: Always log and validate the dynamic SQL before execution
    v_sql := 'SELECT NVL(:1, :2) FROM DUAL';
    DBMS_OUTPUT.PUT_LINE('Good SQL: ' || v_sql);

    EXECUTE IMMEDIATE v_sql INTO v_result USING 'VALUE', 'DEFAULT';
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed SQL: ' || v_sql);
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END;
/

Quick Fix Solutions

Check the Oracle documentation for the exact required arguments of any built-in function before use.
Query ALL_ARGUMENTS to inspect a UDF's current parameter list:

SELECT argument_name, position, data_type, in_out, defaulted
FROM   all_arguments
WHERE  object_name = 'YOUR_FUNCTION_NAME'
  AND  owner       = 'YOUR_SCHEMA'
ORDER BY position;

Recompile and validate all dependent objects after changing a function signature:

-- Recompile all invalid objects in a schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'YOUR_SCHEMA', compile_all => FALSE);

-- Check for remaining invalid objects
SELECT object_name, object_type, status
FROM   all_objects
WHERE  status = 'INVALID'
  AND  owner  = 'YOUR_SCHEMA';

Prevention Tips

Enable PL/SQL compile warnings to catch argument mismatches before deployment:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

Always add DEFAULT values to new UDF parameters when possible. This maintains backward compatibility with existing call sites and prevents ORA-00938 when rolling out function changes incrementally.

-- Adding a default makes the new param optional for existing callers
CREATE OR REPLACE FUNCTION get_salary(
    p_emp_id  NUMBER,
    p_dept_id NUMBER DEFAULT NULL   -- backward compatible
) RETURN NUMBER AS
BEGIN
    -- function logic here
    RETURN 0;
END;
/

Related Errors

ORA-00939 — Too many arguments for function (the opposite problem)
ORA-06553 / PLS-306 — Wrong number or types of arguments in a PL/SQL function call
ORA-00904 — Invalid identifier, often appearing alongside ORA-00938 when argument names are also incorrect

📖 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.