Query Schema error adding columns for a query at workbench channel

[isaac_ariza_cruz]

Hello experts,

For our customer, we have created a custom query and it has been included into the query group Master Agreement List to be also used on the Master Agreement List Channel. However, when use tries to add columns, following error is raised:

Do you know if this issue can be fixed? Is there any system property controlling this property?

Thanks in advance and best regards,

Isaac

[former_member190023 · Accepted solution]

Hello Isaac,

This cannot be changed because it's a hard limitation in Oracle DB. 4000 is the max size for VARCHAR2/NVARCHAR2/RAW, and because Sourcing uses dynamic SQL execution, it is forced within this limit.

Oracle 12c introduces a 32767 limit with MAX_STRING_SIZE = EXTENDED, but this should be raised with Sourcing Product Management to implement.

Bogdan

[isaac_ariza_cruz · Accepted solution]

Hi Bogdan,

Thanks a lot for your help. Really appreciated.

However, as you can see on my last reply to Vikram, for the moment we won't modify DB feature because customer will train end users to do not select all the columns.

Thanks again and best regards,

Isaac

[vikram_shanmugasundaram]

Hi Isaac,

I believe it is hardcoded and cannot be changed but I'm checking with our development team and will get back to you.

Couple of follow up questions....

- I understand that the users get the error when they try to add a new column. Do they also get the error when they try to remove a column?

- Do you get the same error when you try to personalize a standard advanced list page query, for example, Search Master Agreement query?

Regards,

Vikram

[isaac_ariza_cruz]

Hi Vikram,

Thanks for your help!

Answering your questions:

1. It is only happening when we are including a large number of columns. Removing columns is not generating any issue.

2. It is also happening when trying to add a large number of columns on the standard query Search Master Agreement.

So as also explained by Bogdan below, I guess that database parameter MAX_STRING_SIZE needs to be extended.

Please, don't hesitate to let me know if there is any additional information.

Thanks and best regards,

Isaac

[vikram_shanmugasundaram]

Hello Isaac,

Thank you for providing the additional details. Some of the saved changes to the advanced list page query is stored as XML data in the database, so as rightly pointed out by Bogdan it appears that you may have hit the limit especially when dealing with large number of columns. Since the limitation is
at the DB level our options for a solution will be limited as well. I’m following up with development team to see if there is a way to optimize the xml to make little bit more room or may be come up with an alternate approach. Either way it would require product code change. It would be helpful if you could open a message and add the details so we can track it.

Regards,

Vikram

[isaac_ariza_cruz]

Hi Vikram,

Thanks a lot for your help.

At the end our customer has decide to avoid this situation by coaching end users to do not select all the columns.

Anyway we will create a message if this topic is back.

Thanks again and best regards,

Isaac