Juan Antonio Ruiz
Sometimes in migration projects, due to lack of time, budget or other reasons, it is essential to skip the best practices in EIM loads and we are forced to perform INSERTs directly in the Siebel base tables, without using the corresponding EIM tables.
An example of this need is for example when we have to make a load in a custom table (CX_***) and we have not had the services of Oracle Expert Services to build the necessary EIM table mapping (in another entry of this blog it was explained how this could be done directly by editing XML files and "tricking" Siebel Tools to build EIM mappings against custom tables or foreign keys custom):
http://www.autanacrm.com/index.php/es/blog/eim-en-tablas-customizadas-parte-ii
Oracle clearly warns of the danger of doing this type of DML (INSERT, UPDATE, DELETE) actions on base tables, as they can cause inconsistencies and lack of referential integrity.
Siebel generates with its own internal algorithm each ROW_ID of each row created in each base table or repository table, so inserting rows inventing our own ROW_ID is very dangerous since we do not ensure uniqueness in a robust way.
Well, if despite all the warnings, we are sure that we want to safely insert records in Siebel base tables, we can proceed as follows, without having the danger of causing inconsistencies in the ROW_ID.
During the installation of the Siebel database, a function is created in the S_SEQUENCE_PKG package called GETNEXTROWID(). This function does not receive input parameters and returns as output a unique ROW_ID that we can use in our insert.
Thus, for example, if we want to load in Siebel, in a custom table, from an external data source, such as a street map, we could proceed as follows:
INSERT INTO CX_TABLACUSTOM (ROW_ID, CREATED, CREATED_BY, LAST_UPD, LAST_UPD_BY, DB_LAST_UPD, DB_LAST_UPD_SRC, CONFLICT_ID, MODIFICATION_NUM, CUSTOM_COLUMN1, CUSTOM_COLUMN2)
VALUES (S_SEQUENCE_PKG. GETNEXTROWID(), SYSDATE, '0-1', SYSDATE, '0-1', SYSDATE, 'EIM', 0,0,CUSTOM_VALUE1, CUSTOM_VALUE2);
Thus, although we always have the risk that the inserted data does not comply with some Siebel business rules, we will be loading records in a similar way to the "EIM", without having dedicated EIM tables for it.
In any case, I personally do not recommend using this method except in exceptional circumstances of the project.
