generate id
Insert-select function
The generate_id function is an alternative to gen_and_save_id for backward compatibility with older mappings. However, it is recommended that the gen_and_save_id function be used when possible.
insert…select SQL statements. This can only be used to map expressions in TableSet targets.
The generate_id generates a random ID for the id_name 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:
{% code overflow=“wrap” %}
create table cxt_random_val(cxt_table_name varchar2(30 byte),
cxt_id varchar2(128 byte),
primary key (cxt_table_name, cxt_id));
{% endcode %}
Parameters
| Parameter | Type |
|---|---|
| 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 the table to check any duplicates of the generated ID. 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 |
| 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 PER_ID and validates that the generated PER_ID does not exist on the CI_PER_K table.
{% code overflow=“wrap” %}
ria_dih.generate_id(rownum,
'PER_ID',
10,
'CI_PER_K',
{$threadNumber},
{$threadCount})
{% endcode %}
The {$threadNumber} and {$threadCount} variables allow the generation function to work in multiple threads.
Read-only function
This function generates a random ID for the id_name 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 | Type |
|---|---|
| id_name | Name of the ID. Type: varchar2 |
| id_size | Specifies the length of the ID to generate. Type: number |
| target_k_table | Specifies the table to check any duplicates of the generated ID. 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 |
| 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 ACCT_ID and validates that it does not exist on CI_ACCT_K. It would not validate the new ID against the CI_ACCT table, so it could potentially generate duplicate values and fail on a unique constraint error, which would require a restart.
{% code overflow=“wrap” %}
ria_dih.generate_id('ACCT_ID',
10,
'CI_ACCT_K',
{$threadNumber},
{$threadCount})
{% endcode %}
The {$threadNumber} and {$threadCount} variables allow the generation function to work in multiple threads.