Concepts

---

Q: What is a natural identifier?

A: A natural identifier is used to identify the primary key of your source data. It is used when updating and deleting, as if in a "Where" clause. This will most often match the primary key in your target database for output tables, but may differ.

Let's say your legacy system has no concept of employee id and uniquely identifies an employee by the combination of last name and first name. Your target system assigns an employee id to each unique employee and uses that as the unique identifier (so employee id is the primary key of your target table). Your natural identifier is last name, first name. Now let's say your input file has two records with the same last name and first name (duplicates in the legacy system). If you run the transaction in add mode, it will try to add the record twice and will not fail since the primary key is not violated. (NOTE: If you plan to run in add mode, you can get around this problem by adding a group function that returns the newly assigned employee id if there is a match on the last name and first name). If you run the transaction in update mode, the engine will recognize these two records as duplicates and will update the second record instead of creating a new one.

You assign a natural identifier by checking off the corresponding column on the column mapping screen. You may select multiple columns as part of the natural identifier for a particular output.

Since the natural identifier, like a primary key, should not generally be updated, if you select a column as a natural identifier, the allowable action type column will default the allowable actions on this column to "Add" only (the default for other fields is "Add, Update"). You can override this value, but it is not recommended.

A natural identifier is required for each output table. The engine will not run without one.

Column Mapping

---

Q: What is the difference between a default value and a group function?

A: Default value is intended to be used when you wish to hard-code a value in one of your output columns. It performs a "select from dual" behind the scenes. Some uses are constant values (e.g., indicating the source of the record as coming from "source"), Oracle system values (e.g., SYSDATE or USER) and derived values (e.g., UPPER(:input_tab.input_col)).

On the other hand, a group function is meant to retrieve an aggregate value using one of Oracle's aggregate functions (e.g., SUM, COUNT). A group function can be any SQL statement that returns only one value.

All default values can be written as a group function using a "select from dual". Group functions cannot be written as default values.

Usage

---

Q: I have a default value converting my input to upper case, but the result is always in lower case. Help!

A: An important thing to remember is the order of operations for a column mapping record:

• (T)ransformation
• (G)roup function
• (M)apping
• (S)equence
• (D)efault value

If you put the upper case conversion in the default value column, you must remove the direct mapping or it will always be done first and the default value will never be used.

Q: I selected an input table/file from the list, but then I left the record and it disappeared. Where did it go?

A: Because the input table/file is filled in by default (if you have only one input), it is sometimes in the column mapping when it need not or should not be. If the input column is blank (e.g., if you want to eliminate the direct mapping in order to use another derivation), then leaving the input table/file filled in is confusing. Another inconsistent state occurs if you have a variable name in the "input column" column. In these cases, the input table/file is automatically cleared out so as not to be confusing or cause incorrect results.

Q: I know my output has a column "x", but it is not showing in the output column list. Where is it?

A: The list of output columns diminishes. You cannot map to the same output column multiple times (although you can use the same input column multiple times in a mapping). For this reason, each time you choose an output column, it is removed from the list of available ones. If you map all of the output columns, you will eventually see an empty list.

Running Transactions

---

Q: I want to do error processing, but the statistics show that there are no errors to process, even though I know there are errors. What am I doing wrong?

A: Make sure that you have opted to "write errors to database" when submitting your transaction, otherwise the errors will only be written to the ".err" file and not to the database.

Similarly, ensure that you have not chosen to run in debug mode because that results in a rollback and the errors will not be saved in the database.

Database Objects

---

Q: I have a table/view/sequence in the database, but I can't see it from the SQL Power Loader. What do I do?

A: When you log in to the SQL Power Loader Suite, a snapshot of the entire catalog of database tables/views and their objects is taken. If you add a new database object after this snapshot is taken, it will not show up in the SQL Power Loader. Choose "Refresh" from the "Database" menu to reload this snapshot.

Some screens also take their own snapshot of database objects as you enter them. After "Refreshing" the database, close and re-open the screen that you were on.

Ensure that you can see the object from the account that you are using to log on to the SQL Power Loader. You will need the appropriate grants on the object and a synonym if you are not the owner of the object.