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 3 changed one line
A large number of .CTL files have been provided with Personality to be used by the SQL Loader utility for loading comma delimited files into the interface tables of the target database. These files are located in the CONVERSION\CTL_FILES directory of the shared, mapped directory on the database server (this should be P:\P2K\CONVERSION\CTL_FILES if the default settings are used.).
SQL Loader is a utility created by Oracle and installed onto the database server during the installation of the software, as well as a large number of other programs. The utility has one purpose; load flat files into tables within an Oracle database. SQL Loader has a large number of options for loading in different types of files. In order to simplify the loading of these files, High Line has chosen to make use of only one of these load methods. For a complete list of options, with examples of each, please refer to the SQL Loader manual provided [by Oracle|http://www.oracle.com/technology/documentation/index.html|target='_blank'] on their documentation web site.
At line 5 changed one line
There is one .CTL file for each conversion interface file defined within the P2K schema of the database. Each of these files has the same basic format with the name of the input file, the target table in the database and the list of columns available changing for each file.
A large number of .CTL files have been provided by High Line to be used by the SQL Loader utility for loading comma delimited files into the interface tables of the target database. These files are located in the CONVERSION\CTL_FILES directory of the shared, mapped directory on the database server (this should be P:\P2K\CONVERSION\CTL_FILES if the default settings are used from the installation manual).
At line 7 changed one line
The LOAD DATA statement is required at the beginning of the control file.
There is one .CTL file for each conversion interface file defined within the P2K schema of the database. Each of these files has the same basic format with the name of the input file, the target table in the database and the list of columns available changing for each file. The following example is a portion of the PERSONALS.CTL file and is used for loading data into the INF_CV_PERSONALS:
At line 9 removed 14 lines
INFILE specifies that the data is found in an external file and not in the control file.
TRUNCATE indicates that the contents of the table will first be purged before loading of the data. If this line did not exist, the interface table can be appended to.
The INTO TABLE statement is required to identify the table to be loaded ([INF_CV_PERSONALS]) into. By default, SQL*Loader requires the table to be empty before it inserts any records.
FIELDS TERMINATED BY specifies that each field is terminated by a comma and may also be enclosed by quotation marks. Data types for all fields default to CHAR.
TRAILING NULLCOLS allows you to terminate the record before the end of all the fields. Any fields not specified will be set to NULL
Specifies the names of each column to be loaded and the order the must be coded. SQL code can also be added to the columns to translate the data as it’s loaded into the interface table
The following example is a portion of the PERSONALS.CTL file and is used for loading data into the [INF_CV_PERSONALS]:
At line 51 changed one line
The settings within this file will cause SQL Loader to read the comma delimited data from the PERSONALS.CSV file into the [INF_CV_PERSONALS] tables within the database. If there are any records in this interface table when SQL Loader runs, the contents of the table will be truncated (deleted) prior to the new records being loaded. Also, if any of the records being loaded do not have enough columns to fill the records in the table, the columns not provided in the input file will be left as NULL.
The settings within this file will cause SQL Loader to read the comma delimited data from the PERSONALS.CSV file into the INF_CV_PERSONALS tables within the database. If there are any records in this interface table when SQL Loader runs, the contents of the table will be truncated (deleted) prior to the new records being loaded. Also, if any of the records being loaded do not have enough columns to fill the records in the table, the columns not provided in the input file will be left as NULL.
At line 53 changed one line
The data from the comma delimited file will be loaded into the Oracle table using the column order defined in the .CTL file. In this example, the first column in the input file will be loaded into the [PERSON_CODE] column of the interface table, the second column will be loaded into the [USER_NAME] column and so on through the entire input record. If the input file does not match this format, exactly, the order of these columns can be changed to match the actual input file format.
The data from the comma delimited file will be loaded into the Oracle table using the column order defined in the .CTL file. In this example, the first column in the input file will be loaded into the PERSON_CODE column of the interface table, the second column will be loaded into the USER_NAME column and so on through the entire input record. If the input file does not match this format, exactly, the order of these columns can be changed to match the actual input file format.
At line 55 changed one line
If the column being loaded into the interface table needs to have the data manipulated during the load, a modification token can be added to the column line, as in the [EFFECTIVE] and [EXPIRY] columns listed above. The column read from the input file will be modified by the SQL functions, before being inserted into the interface table. The functions used here will set the column to a default value if a value has not been provided in the input record. These tokens can use any SQL function available within the database, including custom stored functions, as long as the entire token does not exceed 256 characters, 258 characters including the beginning and ending quotation marks.
If the column being loaded into the interface table needs to have the data manipulated during the load, a modification token can be added to the column line, as in the EFFECTIVE and EXPIRY columns listed above. The column read from the input file will be modified by the SQL functions, before being inserted into the interface table. The functions used here will set the column to a default value if a value has not been provided in the input record. These tokens can use any SQL function available within the database, including custom stored functions, as long as the entire token does not exceed 256 characters, 258 characters including the beginning and ending quotation marks.
At line 61 removed 11 lines
The example shown below is an example of the PERSONALS.DAT file. It contains only the first few fields and a limited number of records. A complete data file would require all of the mandatory fields, as well as providing commas for all the fields that are to be set to a value of NULL.
{{{1234,MASTER,333-22-4444,John,Doe,01-Apr-1960,Male,…,01-Jan-0001,31-Dec-3999,Initial Record,…
1235,GUEST,123-45-6789,Jane,Doe,29-Feb-1964,Female,…,01-Jan-0001,31-Dec-3999,Initial Record,…
.
.
.
54321,,987-65-4321,John,Smith,25-Dec-1966,Male,…,01-Jan-0001,31-Dec-3999,Initial Record,…}}}
As you can see from the example, each record is fully contained on one line within the data file and terminated by a carriage return. Since all fields are treated as character fields by SQL Loader, we are not required to enclose the text within quotes (‘’). However, this can be done if the tool you are using to create the data files does require them.
At line 118 added one line
!!Adding Translations Lexicons to Control Files
At line 144 changed 3 lines
----
![Notes|Edit:Internal.USING+SQL+LOADER]
[{InsertPage page='Internal.USING+SQL+LOADER' default='Click to create a new notes page'}]
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
}}}
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.
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.
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.
{{{-- 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
@P2K_SVGLI.sql
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;
host sqlldr p2k/p2k@PROD Translate.ctl
}}}
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.
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:
{{{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)))"
}}}
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.
This page (revision-18) was last changed on 26-Nov-2021 10:22 by JMyersTop