PD Bug - Script generation issue with IDENTITY with Primary Key constraint in Oracle

[former_member699267]

Hi,

I'musing PD 16.6 sp3 version.

I'm creating a table with a column as an identiy column and is marked as Primary key.

Script generation from PD:

create table EMP
(
ENO NUMBER(6) not null
generated as identity ( start with 1 nocycle noorder),
ENAME varchar2(10)
);

alter table EMP
add constraint EMP_PK primary key (ENO);

When I try to execute the above script in Oracle, getting the following error.

SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"

The error is due to not null key word is present before identity keyword. Correct script would be

create table EMP
(
ENO NUMBER(6)
generated as identity ( start with 1 nocycle noorder) not null,

ENAME varchar2(10)
);

alter table EMP
add constraint EMP_PK primary key (ENO);

Please fix this issue

[GeorgeMcGeachie]

Arnaud is right, it's time to upgrade PD!

In the meantime you can apply a temporary fix, by editing (a copy of) the Oracle DBMS definition file. You need to change the template at ORA19C::Script\Objects\Column\Add.

From what you say above, one statement appears too early in the template:

[%Identity%?
   generated[ %Generated%] as identity[ (%IdentityOptions%)]]

[arnaud_laurent]

Hello,

Upgrade your installation.

See KBA 2529293 - 2529293 - Error ORA-00907 - Wrong script generated with "identity" column

https://launchpad.support.sap.com/#/notes/2529293
Fixed in:

• SAP PowerDesigner 16.6 SP04 PL03
• SAP PowerDesigner 16.6 SP05 and higher.

KR