Hello guys,
in SQLcl 25.1 project-command when running a deployment i received the following error for one table changeset:
create table f_myfact (
f_myfact_id number(38, 0) not null enable,
f_myfact_fk1_id number(38, 0) not null enable,
f_myfact_fk2_id number(38, 0) not null enable,
f_myfact_dla date,
f_myfact_dbrz_id number(38, 0) not null enable,
constraint f_myfact_pk
primary key ( f_myfact_id,
f_myfact_dbrz_id )
using index f_myfact_pk enable
)
Fehlerbericht -
ORA-01418: Angegebener Index ist nicht vorhanden
01418. 00000 - "specified index does not exist"
The create index stmt follows right after the table-stmt:
create unique index f_myfact_pk on
f_myfact (
f_myfact_id,
f_myfact_dbrz_id
);
This faulty sql is part of the snapshot as well as the initial changeset.
Interestingly other table-scripts don't have this problem:
create table d_brz (
d_brz_id number(38, 0) not null enable,
dbrz_berichtsjahr number(8, 0) not null enable,
dbrz_berichtsmonat number(8, 0) not null enable,
dbrz_auswertungsstichtag date,
dbrz_date date default sysdate
);
create unique index dbrz_pk on
d_mydim_brz (
dbrz_id
);
alter table d_mydim_brz
add constraint dbrz_pk
primary key ( dbrz_id )
using index dbrz_pk enable;
So no inline pk-constraint-definition here.
It seems in case of multicolumn-pk definitions the constraint is created as inline-stmt with an explicit index-name, triggering the error.
The base XML-Data looks very similar for both cases, so the culprit seems to be the XML => SQL transformation.
I fixed the changeset for this table manually (moved constr. definition to seperate alter table-stmt), but would be good to have valid code in the snapshot from the beginning.
Another way to fix this is to leave out the index name in the inline definition and additionally remove the trailing index ddl stmt.
Cheers
Moritz