This page (revision-18) was last changed on 26-Nov-2021 10:22 by JMyers

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
18 26-Nov-2021 10:22 9 KB JMyers to previous
17 26-Nov-2021 10:22 10 KB JEscott to previous | to last
16 26-Nov-2021 10:22 9 KB JAiken to previous | to last
15 26-Nov-2021 10:22 9 KB JEscott to previous | to last
14 26-Nov-2021 10:22 13 KB JEscott to previous | to last
13 26-Nov-2021 10:22 15 KB JEscott to previous | to last
12 26-Nov-2021 10:22 14 KB JEscott to previous | to last
11 26-Nov-2021 10:22 13 KB JEscott to previous | to last
10 26-Nov-2021 10:22 14 KB JEscott to previous | to last
9 26-Nov-2021 10:22 14 KB JEscott to previous | to last
8 26-Nov-2021 10:22 14 KB JEscott to previous | to last
7 26-Nov-2021 10:22 8 KB JEscott to previous | to last
6 26-Nov-2021 10:22 8 KB JEscott to previous | to last
5 26-Nov-2021 10:22 8 KB JEscott to previous | to last
4 26-Nov-2021 10:22 2 KB JEscott to previous | to last
3 26-Nov-2021 10:22 1 KB JEscott to previous | to last
2 26-Nov-2021 10:22 1 KB JEscott to previous | to last
1 26-Nov-2021 10:22 1 KB JEscott to last

Page References

Incoming links Outgoing links
USING SQL LOADER
USING SQL LOADER

Version management

Difference between version and

At line 143 changed 15 lines
!!Adding Translations Lexicons to Control Files
In the situation where a large number of values need to be translated from the legacy system to the application , it may be necessary to build a translation lexicon for the SQL Loader control file to make use of. This is the same process that has already been done for several fields in the conversion process, such as CHANGE_CODE.
Building a translation lexicon can be done easily within Excel and then added to the current list of translations in the system. The following is an example of adding a translation lexicon into the system for the CHANGE_CODE. The first step is to build a comma separated values, or CSV, file that lists all the CHANGE_CODES that will be extracted from the current system and their equivalent value in the application. The following file is a section of one of these files:
{{{TRAN_DCR,.old,.new,P2K Description
TRAN_DCR,01,EMPLOY CAREER,01 EMPLOYMENT - CAREER
TRAN_DCR,02,EMPLOY TERM,02 EMPLOYMENT - TERM
TRAN_DCR,04,EMPLOY TO CAREER,04 CONVERSION TO CAREER (EMPLOYMENT)
TRAN_DCR,05,REHIRE,05 REHIRE
TRAN_DCR,06,REACT ASSIGNMT,06 REACTIVATE ASSIGNMENT
TRAN_DCR,08,TERMINATION,08 TERMINATION OF EMPLOYMENT
TRAN_DCR,15,CAREER FT TO PT,15 CAREER FULL-TIME TO CAREER PART-TIME
TRAN_DCR,16,CAREER PT TO FT,16 CAREER PART-TIME TO CAREER FULL-TIME
!!Viewing / Altering the INF tables
Once the data has been loaded into the interface tables we can use SQL*Plus to view and/or alter the individual rows, or a group of rows. The following script is an example of how to count the number of rows that exist in the PERSONALS interface table:
{{{
SELECT COUNT (*)
FROM INF_CV_PERSONALS;
COUNT (*)
---------
0
At line 152 added one line
This information can be used to verify that all the records in the comma-delimited file were loaded successfully into the interface table. The total number of records within the interface table should be the same number of records in the flat file. If the numbers do not match, the SQL*Loader program would have rejected some of the records.
At line 160 changed one line
The first column is the name of the lexicon that will be created in the [IMLN] screen to hold the translation lexicon values. The second column contains the values that will be extracted from the legacy system, and the third column contains the value that it will be translated to. The fourth column is optional, and can contain the meaning of the value or its original value from the legacy system.
!SQL*Loader Rejects
Records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, SQL*Loader rejects the record. Rejected records are placed in the bad file, if one has been specified within the control file. If you chose to capture rejected records is a “Bad file” you must specify the name of the file within the SQL*Loader control file. The following line will capture the records into a file called BADFILE.RJT.
At line 162 changed one line
Once the comma delimited file has been created, use a text editor to append this list of translations onto the current list in the TRANSLATE.DAT file. The order of the records in this TRANSLATE.DAT file is not important, but should be listed in alphabetical order to make it easier to read and locate entries.
{{BADFILE BADFILE.RJT }}
At line 164 removed one line
The next step is to add the name of the lexicon to the CV_TRAN_LOAD.SQL script so that the entry will be created the next time the script is run. Below is a section of the SQL script that inserts the lexicon names into the database. Add another entry to the end of the script to build the entry.
At line 166 changed 6 lines
{{{-- MODIFICATION HISTORY
-- ----------------------------------------------------
-- 20011015-IB-created to easily delete and rebuild translation tables.
-- loads the P2K_SMGLXID function
-- inserts the TRAN_% Lexicon Names
-- loads in the Lexicon_Values
You may have to qualify the location of the bad file by using a line similar to the following:
At line 173 changed one line
@P2K_SVGLI.sql
{{BADFILE C:\HIGHLINE\P2K\CONVERSION\REJECT\BADFILE.RJT }}
!Oracle Rejects
After a record is accepted for processing by SQL*Loader, a row is sent to Oracle for insertion. If Oracle determines that the row is valid, then the row is inserted into the database. If not, the record is rejected, and SQL*Loader puts it in the bad file. The row may be rejected, for example, because a key is not unique, because a required field is NULL, or because the field contains invalid data for the Oracle data type.
At line 175 changed 25 lines
delete from p2k_am_lexicon_names
where LEXICON_NAME like 'TRAN_%'
and LEXICON_TYPE = 'UC'
/
commit;
insert into p2k_am_lexicon_names
(LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_PPC', 'UC', 5)
/
insert into p2k_am_lexicon_names
(LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_DES', 'UC', 30)
/
.
.
.
insert into p2k_am_lexicon_names
(LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_DTR', 'UC', 30)
/
insert into p2k_am_lexicon_names
(LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_DCR', 'UC', 30)
/
commit;
If the data can be evaluated according to the WHEN-clause criteria (even with unbalanced delimiters) then it is either inserted or rejected.
At line 201 changed 2 lines
host sqlldr p2k/p2k@PROD Translate.ctl
}}}
If a record is rejected on insert, then no part of that record is inserted into any table. For example, if data in a record is to be inserted into multiple tables, and most of the inserts succeed, but one insert fails; then all the inserts from that record are rolled back. The record is then written to the bad file, where it can be corrected and reloaded. Previous inserts from records without errors are not affected.
At line 204 changed one line
In the script listed above, you will see that the last INSERT statement adds the TRAN_DCR entry to the lexicon names. The entry is defined as a User Created lexicon [UC], with a maximum size of thirty characters. Sixteen characters is typically enough for most translations, but can be increased to as much as thirty characters. Once the changes have been made to both the TRANSLATE.CSV file and the CV_TRAN_LOAD.SQL script run SQL Plus, log in as P2K and execute the CV_TRAN_LOAD script. This will delete and recreate all the translation lexicons in the target database.
!SQL*Loader Discards
As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled. The discard file contains records that were filtered out of the load because they did not match any of the record-selection criteria specified in the control file.
At line 206 changed one line
The last step is to alter the SQL Loader control file to make use of the new translation lexicon. The example listed below is a modified version of the ASSIGNMENTS.CTL file, showing the translation of the CHANGE_CODE column as it is being loaded into the INF_CV_ASSIGNMENTS table:
The discard file therefore contains records that were not inserted into any table, up to a specifiable maximum. If a record's data is written to any table, it is not written to the discard file.
At line 208 changed 19 lines
{{{LOAD DATA
INFILE 'assignments.dat'
TRUNCATE
INTO TABLE INF_CV_ASSIGNMENTS
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ENTITY_CODE POSITION(*)
, PERSON_CODE POSITION(*)
, EMPLOYMENT_TYPE POSITION(*)
, HIRE_DATE POSITION(*) "TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')"
, ASSIGNMENT_CODE POSITION(*)
, ASSIGNMENT_TYPE POSITION(*)
, ASSIGNMENT_STATUS POSITION(*)
.
.
.
, CHANGE_CODE POSITION(*) "p2k_smglx('TRAN_DCR', rtrim(ltrim(:change_code)))"
}}}
The discard file is written in the same format as the data file. The discard data can be loaded with the existing control file, after any necessary editing or correcting. These discarded records can, optionally, be placed in a reject file. However, the name and location of the reject file must be added to the control file prior to running SQL*Loader. The following line would need to exist after the INFILE setting in the control file:
At line 228 changed one line
As you can see from this example, the translation is actually performed by a stored procedure created by High Line call P2K_SMGLX. This function takes two parameters and returns a string for the translation. The first parameter is the lexicon name that was created to hold the translation list, the second is the field that contains the coded value that needs to be translated. In this example the field that contains the coded value is the same field that the translation is being performs on, but this is not a requirement. It is possible to use any column, or set of columns, in the records for translation.
{{DISCARDFILE 'BADPERS.RJT'\\ DISCARDMAX 999}}
This identifies the reject file as being located in the current work directory and named BADPERS.RJT. If the number of rejected records exceeds 999, the SQL*Loader will abort the load process for the remaining records in the data file.
This page (revision-18) was last changed on 26-Nov-2021 10:22 by JMyersTop