Skip to Main Content

SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQLcl project-command: deployment of table with multicol-pk fails with ORA-01418

Moritz I.Jul 2 2025

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

Comments
Post Details
Added on Jul 2 2025
3 comments
65 views