generate child id
Insert-select function
This function is an alternative to gen_and_save_child_id for backward compatibility with older mappings. However, it is recommended that the gen_and_save_child_id function be used when possible.
insert…select SQL statements. This can only be used to map expressions in TableSet targets.
The generate_child_id generates a unique child ID for the parent_id and validates that the new ID is unique in the target_k_table. It does not update the target_k_table, but uses an internal table, CXT_RANDOM_VAL. To create the CXT_RANDOM_VAL table, run the following:
create table cxt_random_val(cxt_table_name varchar2(30 byte),
cxt_id varchar2(128 byte),
primary key (cxt_table_name, cxt_id));
Parameters
| Parameter | Description |
|---|---|
| row_num | Determines if it is in the first row to perform its initializations. (From the old doc, but it doesn't make sense to me.) Type: number |
| id_name | Name of the ID. Type: varchar2 |
| id_size | Specifies the length of the ID to generate. Type: number |
| target_k_table | Specifies where the generated child and inherited IDs will be inserted. Type: varchar2 |
| parent_id | Specifies the parent ID used to generate the child and inherited IDs. Type: varchar2 |
| thread_number | Specifies the actual thread number if the function is invoked from multiple threads to prevent generating duplicate IDs, which will result in unique constraint errors at the time of commit. Type: number Default: 1 |
| thread_count | Specifies the actual thread count if the function is invoked from multiple threads to prevent generating duplicate IDs, which will result in unique constraint errors at the time of commit. Type: number Default: 1 |
| inherited_id_size | Specifies the number of digits to copy from the parent_id to generated_id. If the id_size is more than the inherited_id_size, the remaining digits are randomly generated.Type: number Default: 6 |
| max_retries | Specifies the number of retries the generation will run if a duplicate ID already exists in the target_k_table.Type: number Default: 5 |
| generated_id | Indicates the generated ID after the function is executed. Type: varchar2 |
Example
The following mapping expression generates a new 10-digit SA_ID and validate that the generated SA_ID does not exist on the CI_SA_K table. The first 6 digits of the SA_ID came from the ACCT_ID and the remaining 4 digits are randomly generated.
ria_dih.generate_child_id(rownum,
'SA_ID',
10,
'CI_SA_K',
ACCT_ID,
{$threadNumber},
{$threadCount})
The {$threadNumber} and {$threadCount} variables allow the generation function to work in multiple threads.
Read-only function
This function generates a unique child ID for the parent_id and validates it in thetarget_k_table. It does not validate the ID against a target table, so it may generate duplicate values and fail on a unique constraint error, which would require a restart.
TableCursor targets mappings, which are executed in a read-only loop.
Parameters
| Parameter | Description |
|---|---|
| id_name | Name of the ID. Type: varchar2 |
| id_size | Specifies the length of the ID to generate. Type: number |
| target_k_table | Specifies where the generated child and inherited IDs will be inserted. Type: varchar2 |
| parent_id | Specifies the parent ID used to generate the child and inherited IDs. Type: varchar2 |
| thread_number | Specifies the actual thread number if the function is invoked from multiple threads to prevent generating duplicate IDs, which will result in unique constraint errors at the time of commit. Type: number Default: 1 |
| thread_count | Specifies the actual thread count if the function is invoked from multiple threads to prevent generating duplicate IDs, which will result in unique constraint errors at the time of commit. Type: number Default: 1 |
| inherited_id_size | Specifies the number of digits to copy from the parent_id to generated_id. If the id_size is more than the inherited_id_size, the remaining digits are randomly generated.Type: number Default: 6 |
| max_retries | Specifies the number of retries the generation will run if a duplicate ID already exists in the target_k_table.Type: number Default: 5 |
| generated_id | Indicates the generated ID after the function is executed. Type: varchar2 |
Example
The following mapping expression generates a new SA_ID and validates that it does not exist on the CI_SA_K table. The first 6 digits of the SA_ID came from the ACCT_ID and the remaining 4 digits are randomly generated. The function will not validate the new ID against the CI_SA table, so it could potentially generate duplicate values and fail on a unique constraint error, which would require a restart.
ria_dih.generate_child_id('SA_ID',
10,
'CI_SA_K',
ACCT_ID,
{$threadNumber},
{$threadCount})
The {$threadNumber} and {$threadCount} variables allow the generation function to work in multiple threads.