📋

Description

The ORA-30005 error occurs in Oracle Database when the WAIT interval specified in a SQL statement or PL/SQL block is missing, invalid, or zero. This error signals that the database cannot properly execute the wait operation due to incorrect syntax.

💬

Error Message

ORA-30005: missing or invalid WAIT interval

🔍

Known Causes

4 known causes

⚠️

Missing Wait Interval

The `WAIT` keyword is used without specifying an integer value representing the number of seconds to wait.

⚠️

Zero Wait Interval

A value of zero (0) is provided as the wait interval, which is not a valid value for the `WAIT` parameter.

⚠️

Invalid Data Type

A non-integer value (e.g., a string or decimal number) is used as the wait interval, causing a data type mismatch.

⚠️

Syntax Error

The syntax surrounding the `WAIT` keyword is incorrect, preventing the database from correctly interpreting the interval.

🛠️

Solutions

3 solutions available

1. Verify DBMS_LOCK.SLEEP Parameters easy

Ensure the `seconds` parameter passed to DBMS_LOCK.SLEEP is a valid numeric value.

1

Identify any PL/SQL code or scripts that use the `DBMS_LOCK.SLEEP` procedure.

2

Examine the `seconds` argument passed to `DBMS_LOCK.SLEEP`. It must be a non-negative number representing seconds. Ensure it's not `NULL`, an invalid string, or an out-of-range value.

DECLARE
  v_seconds NUMBER := 5.0; -- Example: Valid numeric value
BEGIN
  DBMS_LOCK.SLEEP(v_seconds);
END;
/ 

-- Invalid examples:
-- DBMS_LOCK.SLEEP(NULL);
-- DBMS_LOCK.SLEEP('abc');
-- DBMS_LOCK.SLEEP(-10);

3

Correct any instances where `DBMS_LOCK.SLEEP` is called with an invalid `seconds` argument. If the value is dynamic, add validation to ensure it's a positive number before calling `DBMS_LOCK.SLEEP`.

DECLARE
  v_input_seconds VARCHAR2(10) := 'invalid'; -- Or get from a variable/parameter
  v_sleep_seconds NUMBER;
BEGIN
  BEGIN
    v_sleep_seconds := TO_NUMBER(v_input_seconds);
    IF v_sleep_seconds < 0 THEN
      DBMS_OUTPUT.PUT_LINE('Error: Sleep interval cannot be negative.');
      v_sleep_seconds := 0; -- Or handle error appropriately
    END IF;
  EXCEPTION
    WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric format for sleep interval.');
      v_sleep_seconds := 0; -- Or handle error appropriately
  END;
  
  IF v_sleep_seconds >= 0 THEN
    DBMS_LOCK.SLEEP(v_sleep_seconds);
    DBMS_OUTPUT.PUT_LINE('Slept for ' || v_sleep_seconds || ' seconds.');
  END IF;
END;
/

2. Check Application Code for Incorrect WAIT Calls medium

Review application logic that interacts with Oracle, especially if it involves explicit waits or timeouts.

1

If the ORA-30005 error occurs within an application calling Oracle (e.g., via JDBC, OCI, or an ODP.NET driver), investigate the application's code.

2

Look for any functions or methods that attempt to set a wait interval. This might be in the form of connection timeouts, query timeouts, or explicit sleep calls within the application logic.

Example (Java with JDBC):
// Potentially incorrect usage
connection.setNetworkTimeout(executor, -5000); // Negative timeout
statement.setQueryTimeout(0); // Zero timeout might be valid or interpreted as no timeout depending on driver

// Example of a function that might be misused
Thread.sleep(invalidValue);

3

Ensure that all timeout values passed to Oracle drivers or Oracle-related APIs are positive and within acceptable ranges. Zero might be interpreted differently by various drivers, so verify its behavior.

Example (C# with ODP.NET):
OracleCommand cmd = new OracleCommand("SELECT * FROM dual", connection);
cmd.CommandTimeout = -10; // Invalid timeout
cmd.ExecuteNonQuery();

4

Consult the documentation for the specific Oracle client API or driver being used to understand the expected format and range for wait/timeout parameters.

3. Examine Oracle Scheduler Jobs medium

Verify the configuration of Oracle Scheduler jobs, particularly their retry or delay settings.

1

If the error occurs during the execution of an Oracle Scheduler job, inspect the job's definition.

2

Query the `DBA_SCHEDULER_JOBS` or `USER_SCHEDULER_JOBS` views to find jobs that might be related to the error. Pay attention to columns like `MAX_FAILURES`, `FAILURE_TIME`, `RESTART_TIME`, or any custom logic within the job's PL/SQL block that might call `DBMS_LOCK.SLEEP`.

SELECT JOB_NAME, STATE, MAX_FAILURES, FAILURE_TIME, RESTART_TIME
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'YOUR_JOB_NAME';

-- If the job is a PL/SQL block, you might need to query DBA_SCHEDULER_PROGRAMS and DBA_SCHEDULER_PROGRAM_ARGS as well.

3

If the job has retry logic configured, ensure that the intervals specified for retries or delays are valid numeric values. Also, if the job's PL/SQL code directly uses `DBMS_LOCK.SLEEP`, apply the same validation as in Solution 1.

Example of altering a job to fix a potential issue:
BEGIN
  DBMS_SCHEDULER.SET_JOB_ATTRIBUTE(
    job_name   => 'YOUR_JOB_NAME',
    attribute  => 'RESTART_TIME',
    value      => SYSTIMESTAMP + INTERVAL '5' MINUTE -- Example: valid interval
  );
END;
/

Oracle Error ORA-30005: Invalid WAIT Interval