Column of type=memo in eligibility criteria

[lukkyluke]

Hello everyone,

I have extended the NmsRecipient schema with a listDefinition column. The column will contain concatenated list names. This column needs to be of type NCLOB in Oracle DB since it could be more than 2000 chars in the listDefinition. My plan is to use this column as a eligibility filter on each offer.

i.e IF NmsRecipient.listDefinition contains 'LST43' --> customer is eligible for a given offer

When I define the listDefinition column as type="memo" in Adobe Campaign, filtering with "contains" goes fine (including preview, which shows the expected two eligible recipients), until I try to "approve content", then I get the following error message:

Error when converting the 'data LIKE '%' + 'LST43' + '%'' expression into javaScript: Unsupported type 'memo' of xpath expression 'data'
An error occurred when converting expressions into JavaScript
SOP-330011 Error while executing the method 'CompileRealTimeScript' of service 'nms:interaction'.
(1:1) : Invalid document structure
XML-110018 Error while parsing XML string ''
[nms:webApp Error] SOP-330011 Error while executing the method 'CompileRealTimeScript' of service 'nms:interaction'.
(1:1) : Invalid document structure
XML-110018 Error while parsing XML string ''

Defining the column as type="string" and length=4000 seems to solve the problem above but this leads me to another problem when trying to sync the column value to from marketing to interaction. I have tried using a custom additional SQL function TO_CLOB to solve the problem below.

The update data gives me the following error:

08/05/2019 13:00:07    writer3    ORA-210000 Oracle error: ORA-00932: inconsistent datatypes: expected NCLOB got NCHAR

Any thoughts on this?

[Jonathon_wodnicki]

Hi,

Oracle is 4000 bytes for varchar2, and 32k if you're on a newer build, would that be enough?

Interaction can't do 1:n queries, so normalizing the data won't help here; though I did once create a variant of Interaction without that limitation.

Thanks,

-Jon

[lukkyluke]

Hi and thanks for your fast reply!
In this case we are using nvarchar, so 4000 bytes is 2000 characters. I suspect that 16k characters is not enough either. In which build version was varchar 4k bytes limitation updated to 32k?

What I don't understand is why the filtering with contains-function works fine (when the column is defined as memo) when just making normal queries/preview, and does not work when trying to use it as eligibility. However the eiligibility with contains-function works just fine with 100k+ characters, when the listDefinition column is defined as string with length>2000 (in Adobe Campaign Schema), and as NCLOB in Oracle schema.

For me it looks like I will have to:

1. Define the column as string with length > 2000, and as NCLOB in Oracle (automatically made by Campaign)

2. Grant read access on the marketing nmsRecipient table, to interaction db schema.

3. Solve the copying of the nclob value with plain SQL in a js-activity.

[Jonathon_wodnicki · Accepted solution]

In which build version was varchar 4k bytes limitation updated to 32k?

Oracle 12c was when extended varchar was introduced.

What I don't understand is why the filtering with contains-function works fine (when the column is defined as memo) when just making normal queries/preview, and does not work when trying to use it as eligibility. However the eiligibility with contains-function works just fine with 100k+ characters, when the listDefinition column is defined as string with length>2000 (in Adobe Campaign Schema), and as NCLOB in Oracle schema.

The Interaction module has its own query engine, written in js, and has assorted issues and limitations. Sounds like you found a good workaround. AFAICT there aren't any issues with what you're doing, query could use some improvement though- LIKE '%' + 'LST43' + '%' will capture LST430, so include delimiters in the search: i.e. for | delimiter, contains '|LST43|' with | as the first and last char in the nclob.

[lukkyluke]

Thanks for the explanation!
Yes, I forgot to mention that the filter I use includes a comma at the end

[lukkyluke]

Hi again,

Just want to give you an update. I have not been able to solve the problem yet.

Listdef column defined as string (in Campaign, with length 8000) and nclob (in Oracle):

- I have solved the copying of the listdefinition column between marketing and interaction DB by granting access and merge'ing the columns with sqlExec() in js. For testing purposes I am populating the listdef column with 100k+ characters.

- However when I make an eligibility criteria with a listdef that is matching on an index larger than approx 3400, the offer eligibility seems to fail. As long as I choose a list name that is in the first 3400 characters it works fine.

Defining the list definition column as type memo does not work either, as described above. Does anyone have a workaround for this? I don't want to be limited to ~3400 characters in my list definition. Any input would be very appreciated

[Jonathon_wodnicki]

If your real-world data has a defined limit, you can just use buckets of 3400- e.g. for 15k chars, repeat the query 5 times for 5 cols the key could exist in. More sophisticated would be to hash the keys and label the buckets with prefixes for their contents, e.g. key starts with ‘af’. Beyond that you’re looking at precomputed aggregate type queries which add a new layer of complexity that sounds avoidable here.

[lukkyluke]

Thanks for the feedback! Please share your thoughts on how we can avoid this

We are using specific lists for every offer and the list synchronization is taking a lot of time.

The aggregated list definition column was a suggestion from a Adobe SME to avoid the list synchronization problems.

[Jonathon_wodnicki]

Avoid by sorting your content into buckets that fit in nvarchar2, and searching the buckets in your query.

[lukkyluke]

Hmm, yes that is possible, but then the eligibility-criteria would consist of nested "OR is included in listDefXX".