The ORA-14702 error encountered when testing a for statement for a for statement expressed by an Oracle partition (bottom)

Specify a partition in addition to using the partition name, many times you can use a for statement.

This article describes the ORA-14702 error encountered while testing the for statement.

Still use an example from the previous article:

sql> CREATE TABLE T_part_range

2 (ID number,

3 NAME VARCHAR2 (30),

4 create_date DATE)

5 PARTITION by RANGE (create_date)

6 (PARTITION P1 VALUES less THAN (to_date (' 2009-1 ', ' yyyy-mm ')),

7 PARTITION P2 VALUES less THAN (to_date (' 2009-4 ', ' yyyy-mm ')),

8 PARTITION P3 VALUES less THAN (to_date (' 2009-7 ', ' yyyy-mm '));

Table has been created.

The following is intended to merge the P2 and P3 partitions by means of a for statement:

sql> ALTER TABLE T_part_range

2 MERGE partitions

3 for (to_date (' 2009-4 ', ' yyyy-mm ')),

4 for (to_date (' 2009-7 ', ' yyyy-mm '))

5 into PARTITION P3;

ALTER TABLE T_part_range

*

Line 1th Error:

ORA-14702: Invalid or out of range for partition number

A ORA-14702 error occurred in the statement querying Oracle's error document:

Ora-14702:the partition number is invalid or out-of-range

cause:attempted to-use nonnumerical value or is out of the partitions.

Action:use a valid partition number.

According to the description of the error document, it is felt that the partition key value specifies an error and queries the partition information:

Sql> SELECT Partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' T_part_range '

4 ORDER by 1;

Partition_name High_value

-------------- ----------------------------------------------------------------------------------

P1 to_date (' 2009-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

P2 to_date (' 2009-04-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

P3 to_date (' 2009-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

Is there a problem with the partitioning key value designation:

sql> ALTER TABLE T_part_range

2 MERGE partitions

3 for (to_date (' 2009-04-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian '),

4 for (to_date (' 2009-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian '))

5 into PARTITION P3;

ALTER TABLE T_part_range

*

Line 1th Error:

ORA-14702: Invalid or out of range for partition number

Completely modeled on the partitioning definition in the User_tab_partitions view, the error remains.

The problem was eventually found, and the for statement was not the value used for the partition definition, but rather the value stored in the current partition:

sql> ALTER TABLE T_part_range

2 MERGE partitions

3 for (to_date (' 2009-1 ', ' yyyy-mm ')),

4 for (to_date (' 2009-4 ', ' yyyy-mm '));

The table has changed.

Sql> SELECT Partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' T_part_range '

4 ORDER by 1;

Partition_name High_value

--------------- ---------------------------------------------------------------------------------