Issue with Import CSV to Database V5 Smart service

Question

We are using Oracle database and below is the issue: 
 I have a requirement to load the CSV file data into database. For this, I have created one CDT and it&rsquo;s primary key is configured as auto generated. 
 And I am using the &ldquo;Import CSV to Database V5&rdquo; smart service to load the data. 
 Here I am facing an issue and data is not getting inserted into database. This smart service is showing the below error message. 
 &ldquo;ORA-01400: Cannot insert NULL into (&ldquo;APPIANBD&rdquo;.&rdquo;testTable&rdquo;.&rdquo;id&rdquo;)&rdquo; 
 Where &ldquo; testTable &rdquo; is the table name and &ldquo; id &rdquo; is the primary key. 
 The other tables, for which I am loading the data using &ldquo;Write to Datastore entity&rdquo; smart service , they are working fine. Data is getting inserted and also id is generated automatically. 
 Only for these tables, in which I am loading the data using this smart interface, I am facing this issue. 
 Also, If I manually update the CSV file with some id values, then the data is storing in database. Otherwise, it is not auto generating the id values and throwing the above mentioned error.

ankitab0001 · Answer

Hi hemasreekosuru 
 In Write to Datastore entity the cdt has the @generated annotation, that's why the id generates automatically. 
 In this case, you are inserting the data directly. I would suggest to create a sequence(eg: test_sq) against that table and use test_sq.nextval in the csv file against the ID column and then load the csv

zakarym0001 · Answer

I had the same issue as well, but figured it out. 
 I had a CDT called testTable with columns: id, firstName, lastName id was defined as the primary key and set to autogenerate Database table was built from the CDT through appian 
 My spreadsheet has columns for firstName and lastName, but nothing for the id. Looks like the plugin was making a straight insert using the columns and data supplied without knowing or doing anything about the id field. 
 like this... insert into testTable (firstName, lastName) values ("bob", "barker") 
 The trick was to create the table and CDT in the other order. 
 First manually create the table in the database with an auto increment primary key Next create the CDT using the option, 'Create from database table or view' Point your CDT to the newly created table 
 Now the database knows what to do about the primary key and the import will work. 
 Instructions for an auto increment pk in oracle... 
 https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-oracle/ 
 Hope this helps