My preferred tool for writing PL/SQL code has been DataGrip from JetBrains for the last few years. Working on a case with reference partitioned tables, I came over some strange errors. At first I thought Oracle had a lot of bugs in the partitioning syntax, but it turned out to be DataGrip messing with my DDL!

I was testing out diffent tings around reference partitioned tables in 19c. As previously described in this post, DataGrip cutting DDL short, case 1, I thought Oracle had some errors in the syntax.

Here’s another case where the combination of DataGrip and Oracle gave unexpected results. Teaser: There’s a twist at the end!

I had managed to create two tables like this, but after finding a workaround when doing it in DataGrip:

create table orders
(
    order_id     number(12),
    order_date   date,
    order_mode   varchar2(8),
    customer_id  number(6),
    order_status number(2),
    order_total  number(8, 2),
    sales_rep_id number(6),
    promotion_id number(6),
    constraint orders_pk primary key (order_id)
)
    partition by range (order_date)
(
    partition q1_2005 values less than (to_date('01-APR-2005', 'DD-MON-YYYY')),
    partition q2_2005 values less than (to_date('01-JUL-2005', 'DD-MON-YYYY')),
    partition q3_2005 values less than (to_date('01-OCT-2005', 'DD-MON-YYYY')),
    partition q4_2005 values less than (to_date('01-JAN-2006', 'DD-MON-YYYY'))
);

create table order_items
(
    order_id     number(12) not null,
    line_item_id number(3)  not null,
    product_id   number(6)  not null,
    unit_price   number(8, 2),
    quantity     number(8),
    constraint order_items_fk
        foreign key (order_id) references orders (order_id)
)
    partition by reference (order_items_fk);

I then wanted to test partition splits, checked the documentation and issued this command:

alter table masters
    split subpartition d_1000_def_yearmonth at (to_date('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
    into (subpartition d_1000_jan2022, subpartition d_1000_def_yearmonth);

Surprisingly enough, I got an error “ORA-14150: missing SUBPARTITION keyword”. I rechecked the syntax and found it to be correct. Anyway – I tried removing the parenthesis, since that’s where the IDE indicated the error:

alter table masters
    split subpartition d_1000_def_yearmonth at (to_date('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
    into subpartition d_1000_jan2022, subpartition d_1000_def_yearmonth;

But trying that gave “ORA-00907: missing right parenthesis” as the response.

I fiddled around, rechecked documentation and in desperation tried to just add the final, missing right parenthesis! And it worked! A statement with unmatched parenthesis! If I didn’t have OCD before…:

alter table masters
    split subpartition d_1000_def_yearmonth at (to_date('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
    into subpartition d_1000_jan2022, subpartition d_1000_def_yearmonth);

At a later time I realized that the problem was in the way DataGrip interprets the statements. It can cut some of them short. A close examination of the log shows that this statement gets truncated after the “into” keyword:

alter table masters
    split subpartition d_1000_def_yearmonth at (to_date('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
    into (subpartition d_1000_jan2022, subpartition d_1000_def_yearmonth);

POUG> alter table masters
split subpartition d_1000_def_yearmonth at (to_date('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
into
[2023-05-19 15:18:31] [72000][14150] ORA-14150: missing SUBPARTITION keyword
[2023-05-19 15:18:31] Position: 133

The twist

The original commands runs just fine in Oracle SQL Developer. The interesting bit is that the statement with the unmatched number of parenthesis (2 left and 3 right) also works in SQL Developer! It also works when run through PL/SQL and execute immediate. So – there actually is a small non-important syntax bug in Oracle 19c.

alter table masters
split subpartition d_1000_def_yearmonth at (to_date('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
into subpartition d_1000_jan2022, subpartition d_1000_def_yearmonth);