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!

As I was struggling to get the syntax to create a referenced table to work so I pulled out the example from the documentation:

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);

And it failed!

After a tedious check and retest of my own syntax, I wondered if Oracle had some severe bugs here. My workaround was to create order_items as a regular table and convert it to a reference partitioned table:

alter table order_items
    modify partition by reference(order_items_fk)
    enable row movement;

At some point, I ran the same original example-code in SQL Developer. And it worked!

A closer examination of the contents of the log pane (services) in DataGrip revealed that DataGrip had parsed and cut my DDL-statement short!

REFPART> 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
[2022-12-20 14:46:30] [42000][906] ORA-00906: missing left parenthesis

Notice how there’s nothing after the “reference” keyword!

This wasn’t the only case. I’ll follow up with another one later.