DataGrip cutting DDL short, case 1

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.

One thought on “DataGrip cutting DDL short, case 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s