NOTE: As of 5.13.16, this will no longer be necessary, as we prepare to move towards postGres syntax in Biotics 6.

-- This is the newer syntax for insertion, which will work in Oracle after -- these changes. This syntax will be required for Postgres. 

INSERT INTO D_SURVEY_TYPE (SURVEY_TYPE_DESC, DISPLAY_ORDER)   values ('New survey type', 5);

DESCRIPTION

The GETNEXTSEQ function is used in an INSERT statement to get the next ID for a particular table for each new row in the table.  If you're inserting rows directly into the database (e.g., using SQL Developer), it's a good habit to always use GETNEXTSEQ to retrieve the correct ID for your insert. That's because it also updates the NEXT available ID in the sequence and prevents Biotics from trying to use the same ID more than once (which will cause errors). This syntax will work for all tables in Biotics, whereas leaving out an explicit value for the ID only works for tables that have insert triggers which include core data tables (HDMS_TABLE where TABLE_TYPE='B' and CORE=1), not extensible tables.

If you are utilizing Biotics 5, this function already exists in your database.

SOLUTION

Usage: getnextseq('TABLE_NAME')

Examples: The following syntax examples work for inserts:

Insert into ELEMENT_SUBNATL_REF (element_subnatl_ref_id, element_subnational_id, reference_id, rec_create_user) values (getnextseq('ELEMENT_SUBNATL_REF'), 12345, 6789, 'username');

Insert into ELEMENT_SUBNATL_REF (element_subnatl_ref_id, element_subnational_id, reference_id, rec_create_user) select getnextseq('ELEMENT_SUBNATL_REF'), 12345, 6789, 'username' from dual;
Note that when you're "selecting" the value returned by the function, so you have to use a "FROM" clause (from dual).  "Dual" is a dummy table name in Oracle.

Insert into ELEMENT_SUBNATL_REF ( element_subnational_id, reference_id, rec_create_user) values (12345, 6789, 'username');

The above syntax utilizes the insert trigger to automatically assign the primary key id (element_subnatl_ref_id) and hence it is left out of the insert statement.  This can only be used with core data tables (HDMS_TABLE where TABLE_TYPE='B' and CORE=1), not extensible tables.