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.).
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 13 changed one line
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.
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.
At line 21 changed one line
The following example is a portion of the PERSONALS.CTL file and is used for loading data into the [INF_CV_PERSONALS]:
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 69 changed one line
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.
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 143 added 10 lines
!!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
}}}
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 144 changed 3 lines
----
![Notes|Edit:Internal.USING+SQL+LOADER]
[{InsertPage page='Internal.USING+SQL+LOADER' default='Click to create a new notes page'}]
!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.
{{BADFILE BADFILE.RJT }}
You may have to qualify the location of the bad file by using a line similar to the following:
{{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.
If the data can be evaluated according to the WHEN-clause criteria (even with unbalanced delimiters) then it is either inserted or rejected.
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.
!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.
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.
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:
{{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