Siebel Life Sciences Guide > Importing Data into Life Sciences > Importing, Extracting, and Routing Syndicated Data >

Using SQL*Loader to Load the Data

Data records in Siebel Business Applications do not refer to the row ID field in the S_SYND_DATA table. You can insert any value in this field if it is unique in the record. Therefore, you can use a native database utility to populate this table. This method discusses the use of the Oracle SQL*Loader utility. As with the Visual Basic and COM method, using SQL*Loader requires that you resolve the foreign key references either before or after the data has been loaded into the table.

To use SQL*Loader to load the data

1. Resolve the foreign keys before loading.

   Process the input file one line at a time and convert each foreign key value into a row ID. To save time, first load the data directly into the table as it is stored in the flat file, then update the foreign key fields using SQL statements.

2. Load the data file using a .ctl format file.

   The following is an example of loading a data file:

   options (rows=100)
   load data
   infile 'c:\myfile.txt'
   badfile 'c:\myfile.bad'
   append
   into table S_SYND_DATA
   fields terminated by ","
   trailing nullcols
   (ROW_ID SEQUENCE (MAX,1),
   CREATED SYSDATE, CREATED_BY CONSTANT "1-0",
   LAST_UPD SYSDATE, LAST_UPD_BY CONSTANT "1-0",
   MODIFICATION_NUM CONSTANT 0,
   CONFLICT_ID CONSTANT "0",
   POSITION_ID, TERR_ID,
   PERIOD_ID, DATA_SRC_CD CONSTANT "RXTer",
   MARKET_ID, PRDINT_ID, ATTRIB_01)

3. Run the SQL*Loader utility from the command line using the following syntax:

   SQLLDR73.EXE database/userid@pw control=c:\my.ctl log=c:\my.log

   NOTE:  The exact file name and syntax of the Loader utility is determined by the version of Oracle being used.

4. When the data has been loaded, resolve the foreign key references for each field. This step makes the data visible to the users, as in the following example.

   UPDATE s_synd_data
   FROM s_period p
   SET s_synd_data.period_id=p.id
   WHERE p.name=s_synd_data.period_id ;

This code updates the Period field in the Syndicated Data Table to the ID of the Period rather than to the textual description of the period. You must modify and run this code for each foreign key field referred to in Table 23. The code only needs to be run for each field that you load, not for all of the fields.