Issue with Primary Key Column in Oracle12c

Hi

I am working on version 19.2.

One of my existing application's DB related work is done on Appian's MySQL database. We are now migrating the DB to Oracle 12c.

There are many tables in MySQL where the primary key is autogenerated. Below is the XSD:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types" targetNamespace="urn:com:appian:types">
  <xsd:complexType name="MYAPP_DataInsertion">
    <xsd:annotation>
      <xsd:appinfo source="appian.jpa">@Table(name="MYAPP_DATA_INSERTION")</xsd:appinfo>
      <xsd:documentation><![CDATA[File Upload fields for Doc to be uploaded.]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="ID" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Id @GeneratedValue @Column(name="ID")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Type" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="TYPE", nullable=true, columnDefinition="VARCHAR(20)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Action_By" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="ACTION_BY", nullable=true, columnDefinition="VARCHAR(50)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Action_Date" nillable="true" type="xsd:date">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="ACTION_DATE", nullable=true, columnDefinition="DATE")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

When the user does insertion from the UI, the string generated for insertion in table is:

[ID=, Type=Type1,Action_By=dummyUser, Action_Date=14/08/2019]

In my PM, after the UI, Write to data store entity is hit and the above string is inserted in the corresponding table.

In Oracle 12c, the SQL command written to generate the same table is:

 CREATE TABLE "B"."MYAPP_DATA_INSERTION" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 167 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE, 
	"TYPE" VARCHAR2(20 CHAR), 
	"ACTION_BY" VARCHAR2(50 CHAR), 
	"ACTION_DATE" DATE, 
	 PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T_B"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T_B" ;

Now if my Write to data store entity points to this Oracle table and I try to insert the same string as mentioned above, I get the below error:

An error occurred while trying to write to the entity “MYAPP_DataInsertion” [id=0eee4d0c-9afb-431c-b5f1-f264d38f6bcb@237544, type=MYAPP_DataInsertion (id=11724)] (data store: MYAPP Data Store (Oracle)). Details: org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save(): MYAPPInsertProcess14931 Data: TypedValue[it=11725,v={{<null>,Type1,dummyUser,2019-08-14}}]

However, if I run an insert query directly from Oracle which is somewhat like this:

insert into MYAPP_DATA_INSERTION (ID,TYPE,ACTION_BY,ACTION_DATE) values (NULL,'Type1','dummyUser',to_date('14-AUG-19','DD-MON-YY'));

the insertion happens.

Has anyone faced any such problem while using Oracle12c with Appian?

Thanks in advance!!

  Discussion posts and replies are publicly visible

Parents Reply Children