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.
[…] 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 […]
LikeLike