📋

Description

The ORA-30122 error indicates that a parameter value provided to Oracle Database is outside the allowed minimum and maximum range. This typically occurs during database configuration or when setting session-specific parameters.

💬

Error Message

ORA-30122: value ' string ' for ' string ' must be between ' number ' and ' number '

🔍

Known Causes

3 known causes

⚠️

Incorrect Parameter Value

The user-supplied value for a database parameter is outside the permissible range defined by Oracle.

⚠️

Typographical Error

A typo in the parameter value string can result in the value being interpreted as outside the acceptable range.

⚠️

Configuration Mismatch

Inconsistent configuration settings can lead to parameter values conflicting with other settings, triggering the error.

🛠️

Solutions

3 solutions available

1. Correcting Input Parameter Value easy

Identify and rectify the incorrect input value passed to a SQL statement or PL/SQL procedure.

1

Examine the full ORA-30122 error message to identify the specific parameter name and the out-of-range value it received.

2

Locate the SQL statement, PL/SQL procedure, or application code that is calling the failing operation. This might involve reviewing application logs, SQL trace files, or the source code itself.

3

Determine the valid range for the identified parameter. This information is usually provided in the error message (e.g., 'must be between X and Y'). If not explicitly stated, consult the documentation for the function, procedure, or SQL statement being used.

4

Modify the application code or the SQL statement to provide a value for the parameter that falls within the acceptable range. For example, if a date parameter expects a value between '01-JAN-2023' and '31-DEC-2023' and you're passing '01-JAN-2024', adjust the date accordingly.

UPDATE employees SET hire_date = TO_DATE('01-JUN-2023', 'DD-MON-YYYY') WHERE employee_id = 101;

2. Adjusting Application Logic or Configuration medium

Modify the application or system configuration that is generating the out-of-range parameter.

1

Analyze the context in which the error occurs. Is it a user input form, a batch process, or an API call?

2

If the error originates from user input, implement input validation on the application side to ensure that only valid values are submitted. This can prevent the ORA-30122 error before it even reaches the database.

if (input_value < min_allowed || input_value > max_allowed) { throw new Error('Invalid input value.'); }

3

If the parameter value is derived from a configuration file or system setting, review and update these settings to ensure they are within the acceptable range for the Oracle database operation.

4

For batch processes, ensure that the data being processed conforms to the expected constraints. This might involve data cleansing or transformation steps before processing.

3. Reviewing and Modifying PL/SQL or SQL Code medium

Examine and correct the PL/SQL or SQL code that defines or uses the parameter with the out-of-range value.

1

Identify the specific PL/SQL function, procedure, or SQL statement causing the error. The error message often points to the calling object or statement.

2

If the parameter is defined within a PL/SQL block, check the declaration and any assignments to ensure the values assigned are within the expected range.

DECLARE
  l_my_value NUMBER;
BEGIN
  l_my_value := 500; -- Assuming 500 is out of range for a parameter expecting 1-100
  -- Call procedure with l_my_value
END;

3

If the parameter is part of a SQL statement (e.g., a bind variable or literal value), verify that the value used is correct and within the expected range for the column or function it's being compared against or passed to.

SELECT * FROM my_table WHERE some_column BETWEEN 1 AND 100 AND another_column = :input_value;

4

If the error is due to a constraint or a check in the PL/SQL code itself, adjust the logic to accommodate valid values or to handle invalid values gracefully (e.g., by raising a custom exception with a more informative message).

IF input_value < 1 OR input_value > 100 THEN
  RAISE_APPLICATION_ERROR(-20001, 'Input value must be between 1 and 100.');
END IF;

ORA-30122: Parameter Value Out of Range