Create CDT with no sequence associated to it oracle.

Check this documentation which specifies to select sequence for Oracle database.

Have you tried using SQL INTEGER?  That may be the problem right there, because NUMBER allows you to specify the number of digits after decimal point.  I wonder if it only maps to Decimal, and doesn't properly map to XSD int.

I see there is no integer type in oracle Dave.So I am stuck.

What version of Oracle are you using?  The INTEGER keyword should work.

I mean I tried to create a table with integer and the table gets created with datatype as number(38,0) . Isn't interger and number with 0 decimal point are the same datatypes. Do you think there is a difference?

HI Abhishek,

Try creating table in oracle with a sequence (you might have to create a sequence i will be easy if you are using any oracle sql developer) . try inputting the data with sql insert query, if it work fine , then create the cdt from the DB. It will work .

Hi Manish,

Thanks for the input, I am aware of this approach but we are trying to emulate how auto increment cdts creation work for mysql tables. Auto increment works with mysql and even oracle supports that capability but only when I create a CDT to link a table created in oracle,  system wouldn't allow me to publish without providing a sequence. We are trying to create table with autogenerated ids and pass null from appian and db handles the job of creating id value, there by eliminating the dependency of linking sequences and maintaining them.

Can you try to create your CDT in Appian first and see what table gets generated when you publish the data store? That's a good way to identify what the structure of the table should be for it to map correctly. I agree with others that the 38 length seems too large - generally Appian integers are much smaller (since Appian itself doesn't support integer values past 2^32).

Also, what version of Oracle are you using?

Hi Peter, Let me be descriptive with all the steps I performed:

Oracle Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 

As suggested I created CDT in appian and jpa annotation that appian created is

<xsd:element name="id" nillable="true" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@Id @Column(name="ID", nullable=false, columnDefinition="NUMBER")</xsd:appinfo>
</xsd:annotation>
</xsd:element>

The sql asscociated with id is as below.

"ID" NUMBER(2,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,

When I try to make an insert using write to datastore entity smart service, I get the below error:

Details: org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save()

When I do the insert with below query in db it works,

Insert into REF.Table
(ID,col1,col2,IS_ACTIVE,CREATED_DT,CREATED_BY,LAST_MODIFIED_DT,LAST_MODIFIED_BY)
values
(NULL,'test','teste2','Y',to_timestamp('03-MAY-22 11.22.00.860000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'system',to_timestamp('12-MAY-22 01.58.16.200000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'system');

I believe appian needs an @GeneratedValue to understand that the id will be autogenerated but when I used below JPA annotation and tried to update the CDT, it errors with error :

The data source schema does not match the type mappings: Missing sequence or table: Table_sq  

<xsd:element name="id" nillable="true" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@Id @GeneratedValue @Column(name="ID", nullable=false, columnDefinition="NUMBER")</xsd:appinfo>
</xsd:annotation>
</xsd:element>

Did you check the box for "Auto-generate the next unique identifier when new records are written to a data store entity" when creating your CDT? That should add that annotation automatically and you shouldn't need to update your CDT and include that annotation afterwards.