About the xref:populateLookupXRefRow Function

Like the xref:populateXRefRow function, the xref:populateLookupXRefRow function populates a cross reference column with a single value. Unlike the xref:populateXRefRow function, the xref:populateLookupXRefRow function does not throw a unique constraint violation error when records with the same ID are added simultaneously. Instead, it behaves as a lookup and returns the existing source value that caused the error and does not stop the processing flow. Use this function to resolve any concurrency issues that could arise when using the xref:populateXRefRow function.

The xref:populateLookupXRefRow function returns a string value, which is the cross reference value being populated or, with a unique constraint violation, the cross reference value that was already populated by the first committed thread. For example, as shown in Table 44-8, the XREF_CUSTOMER_DATA table has the following columns: EBS, Common, and SBL. The xref:populateLookupXRefRow function is invoked by two threads in parallel with following values:

Thread One: xref: populateLookupXRefRow ("default/xref/example.xref", "EBS", "EBS100", "Common" "CM001", "ADD")
Thread Two: xref: populateLookupXRefRow ("default/xref/example.xref", "EBS", "EBS100", "Common" "CM002", "ADD")

The table is populated as shown in Table 44-11. Since thread one is committed first, thread two returns "CM001" to the caller.

Table 44-11 Cross Reference Table Populated by xref:populateLookupXRefRow

EBS	Common	SBL
`EBS100`	`CM001`

The syntax of the xref:populateLookupXRefRow function is shown in the following example:

xref:populateLookupXRefRow(xrefMetadataURI as string, xrefReferenceColumnName as
 string, xrefReferenceValue as string, xrefColumnName as string, xrefValue as
 string, mode as string) as string

Parameters

xrefMetadataURI: The cross reference table URI.
xrefReferenceColumnName: The name of the reference column.
xrefReferenceValue: The value corresponding to the reference column name.
xrefColumnName: The name of the column to be populated.
xrefValue: The value to be populated in the column.
mode: The mode in which the xref:populateXRefRow function populates the column. You can specify ADD or LINK. Table 44-10 describes these modes and exception conditions for the modes.

Note:

The mode parameter values are case-sensitive and should be specified in upper case only.

Table 44-12 xref:populateLookupXRefRow Function Results with Different Modes

Mode Reference Value Value to be Added Result

Mode	Reference Value	Value to be Added	Result
`ADD`	`Absent` `Present` `Present`	`Absent` `Absent` `Present`	Success Exception (Success only when Exception is Unique constraint violation) Exception (Success only when Exception is Unique constraint violation)
`LINK`	`Absent` `Present` `Present`	`Absent` `Absent` `Present`	Exception Success Exception

ADD

Absent

Present

Absent

Present

Success

Exception (Success only when Exception is Unique constraint violation)

LINK

Absent

Present

Absent

Present

Exception

Success

Exception

Usage Notes

When using a custom table approach, you must add the primary constraint on the columns that must be unique in the cross-reference table. Using Table 44-11 as an example, the SQL statement is similar to the following:
```
alter table xref_customer_data add constraint xref_vnx_data_pk
 primary key (common, ebs);
```
Populate the primary constraint columns first and then populate the remaining columns in subsequent calls.
This function should not be used for inserting cross references for primary objects, since this could mask data inconsistency issues. Only use the function for secondary objects to a main dependent object. For example, do not use the function to determine whether an account already exists when creating customer accounts; but do use it if the addresses in those customer accounts are being synchronized.