Symptoms

How to handle inserts in Oracle
 

Diagnoses

In SQL Server we have the IDENTITY field which works with the AutoNumber field in K2. However in Oracle there is no IDENTITY field and we handle incrementing the sequence of the primary key ID field in a trigger on the table. Therefore K2 doesn't create the ID field as an AutoNumber and isn't aware of the underlying trigger on the table. What's the best practice for handling CRUD operations in K2 SmartForms?
 

Resolution

One possible solution is to create a store procedure that will handle the "Create" aspect and then map the out-of-the-box "Create" method to this stored procedure service object the "Read", "Update" and "Delete" will work out-of-the-box. Please see attached document (OracleCreate.docx).

Also see:

https://help.k2.com/onlinehelp/k2blackpearl/userguide/current/webframe.html_oracle_tables.html

"There is no concept of an Identity Key in Oracle. You can only designate a column as a Primary Key. To provide an “auto-increment” value to a Primary Key (or any field you wish) you need to create or use a Sequence. Sequences are scoped database wide. You can ask a Sequence for the next available value which you can use to insert into the Primary key field. To automate it, you create a trigger on INSERT to get the next value from Sequence before inserting.

It is possible for more than 1 table to use the same Sequence. It is therefore good practice to name the sequence something that identifies it with the table name.

For example:
Think of a Sequence as a Next Customer ticket number dispenser. You could have two departments using the same dispenser or they could each have their own.

In K2 a situation could arise where a table might have a Primary key which is NOT NULLABLE.
This means that a field will be required for ServiceObjects and SmartObjects derived from that table.
The problem is that the table might be using a Sequence to populate the Primary key and K2 has no way of deriving that information by interrogating the Database schema.
The workaround would be to send a NULL value as the primary key field value on Create. This gets us past the Required validation and will leave it to the Database to insert the correct value obtained from the Sequence. To send a NULL you can click the null button on the SmartObject tester tool next to the text box. Using the API you can set it to null or set the value itself to “scnull”."