📋

Description

This error indicates an incorrect `lower_bound` value was provided to the `dbms_sql.define_array` procedure. The `lower_bound` must be a valid integer (positive, negative, or zero), and cannot be NULL.

💬

Error Message

ORA-29254: Invalid lower_bound argument passed to procedure dbms_sql.define_array

🔍

Known Causes

3 known causes

⚠️

NULL Lower Bound

The `lower_bound` argument was inadvertently set to NULL, which is not a valid value.

⚠️

Incorrect Variable Type

The variable passed as `lower_bound` is not an integer or a type that can be implicitly converted to an integer.

⚠️

Logic Error

A calculation or logical error resulted in a non-integer value being assigned to the `lower_bound` variable.

🛠️

Solutions

3 solutions available

1. Correct Array Initialization in PL/SQL easy

Ensure the lower bound of your array is correctly defined before passing it to dbms_sql.define_array.

1

Locate the PL/SQL code where `dbms_sql.define_array` is being called.

2

Identify the variable being passed as the `lower_bound` argument. This is typically an integer.

3

Verify that the `lower_bound` is a valid integer. In PL/SQL, arrays are typically 1-based or 0-based. Ensure the value is not null, undefined, or a value outside the expected range for an array index.

DECLARE
  l_array_lb PLS_INTEGER := 1; -- Or 0, depending on your array's intended indexing
BEGIN
  -- ... other dbms_sql operations ...
  dbms_sql.define_array(cursor_handle, column_position, data_array, array_size, l_array_lb);
  -- ...
END;

4

If the `lower_bound` is being dynamically determined, ensure the logic correctly assigns a valid integer value before the `dbms_sql.define_array` call.

2. Validate `dbms_sql.define_array` Arguments medium

Check all arguments passed to `dbms_sql.define_array` for correctness, especially the `array_size` and `lower_bound`.

1

Review the signature of `dbms_sql.define_array` to understand each parameter's purpose and expected data type: `PROCEDURE define_array (cursor_handle IN INTEGER, position IN INTEGER, field IN OUT NOCOPY DESC_COL_ARRAY, size IN INTEGER, lower_bound IN PLS_INTEGER);`

2

Ensure `cursor_handle` is a valid cursor handle obtained from `dbms_sql.open_cursor`.

3

Verify `position` is a valid column position for the cursor's select list.

4

Confirm `field` is a valid `DESC_COL_ARRAY` type, which is an array of `DBMS_SQL.DESC_COL` records.

TYPE DESC_COL_ARRAY IS TABLE OF DESC_COL INDEX BY BINARY_INTEGER;

5

Check that `size` is a positive integer representing the maximum number of elements the array can hold.

6

Crucially, ensure `lower_bound` is a valid PLS_INTEGER. Common mistakes include passing `NULL`, an uninitialized variable, or a non-integer value.

3. Review Array Type Definition and Usage medium

Confirm that the PL/SQL collection type used for the array is correctly defined and its elements are accessed appropriately.

1

Examine the declaration of the PL/SQL collection type used with `dbms_sql.define_array`. It should be a `TABLE OF` type, indexed by `BINARY_INTEGER` or `PLS_INTEGER`.

TYPE my_string_array IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

2

When populating or accessing elements of this array, ensure you are using valid indices. If the `lower_bound` is 1, then valid indices are 1, 2, 3, etc. If the `lower_bound` is 0, valid indices are 0, 1, 2, etc.

my_array my_string_array;
-- Assuming lower_bound is 1:
my_array(1) := 'some value';
my_array(2) := 'another value';

-- Assuming lower_bound is 0:
my_array(0) := 'first value';
my_array(1) := 'second value';

3

If the array is being populated by a query result, ensure the query is returning a consistent number of rows and that the array is sized accordingly. If the query returns no rows, an empty array should be handled gracefully.

ORA-29254: Invalid Array Lower Bound