Error when having a string concatenation of two columns over 4000 characters data

Subhakara Netala-Oracle

Hi,

We are encountering an error when concatenating two columns in the report(OAC), as the combined number of characters exceeds the 4000-character limit. While we understand that a single VARCHAR2 column in the database has a 4000-character limit,however we think this 4000 char limit may not apply when concatenating columns within OAC(as two columns limit together should be 8000). Please suggest how can we handles this situation in OAC.

Thanks in advance.

Regards,

Subhakara Netala

Sumanth V -Oracle

@Subhakara Netala-Oracle -

• During concatenation, Oracle combines the two strings into a temporary result, allowing the intermediate size to exceed the normal VARCHAR2 limit. This is why concatenating two VARCHAR2(4000 BYTE) columns may produce an 8000-byte result.

• However, if the resulting value is still stored under a single column which exceeds the VARCHAR2 limit of 4000 bytes when stored back into a column, it will raise an error.

• This is DB limitations and not OAC's. Thank you!

Christian Berg -Oracle

I think it's worth being a bit more precise here:

1.) What do you call a "report"? A BI Publisher "report" object"? An Answers "analysis"? A DV "Workbook"?

2.) the response given so far is not necessarily correct. It's absolutely perfectly possible to concatenate two strings. Here's an example with 2 random strings of 3999 characters length each in a DV Workbook:

It renders without any issue.

Now the same as an analysis:

Also works perfectly fine.

Could you please be more precise and specific in what you are trying do achieve, how you are going about it, and where you see what behaviour / result?

Subhakara Netala-Oracle

Thanks @Christian Berg -Oracle for your response.

We are trying to CONCAT two columns in Classic BI report(Analysis). We also checked the length, both columns together just above 4000 chars. Below is the formula from our report,

"Competency Assesment"."Feedback"||' - '|| "Competency Assesment"."Competency Name"

Here is the error screenshot.

Christian Berg -Oracle

Your screenshot shows an analysis. Not a report. They are different object types. Technically and technologically different. There is no such thing as a "Classic BI report(Analysis)".

And you are using a Subject Area sourced from a database table which also was not specified in the inital post. Data Sets, Subject Areas, and how they are built have an impact on how and what works. In your case you can see from the log that it is in fact an OCI (Oracle Call Interface, not Oracle Cloud Infrastructure) error since it is function-shipped to the database for execution and the DB complains.

Counter-example: I have concatenated the day name of a time dimension 800 times to produce a for a similar length and query:

As you can see even that works.

You have to look at your physical SQL as well. Because even if mine is atrocious to look at it simply works and executes. I can't post the SQL because the forum doesn't let me post a string that long. Ironic :)

Christian Berg -Oracle

Ram-Oracle

Hi @Subhakara Netala-Oracle ,During concatenation ,As report is failing due to exceeded characters due to varchar2 datatype ,there is one more datatype with clob datatype.Please refer CLOB Data Type section from below documentation.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

Gianni Ceresa

Hi @Subhakara Netala-Oracle ,

Your screenshot shows "ORA-01489: result of string concatenation is too long".

This is the database kindly telling you that your physical query (on the database) did try to generate a string that is too long for that database.

Your is an Oracle database, and the default maximum size for a varchar2 is 4000 bytes.

When other example were posted saying it does work, it's because it depends on the database settings.

The default max size is 4000 bytes, but if the extended data type is enabled, then the maximum limit for a varchar2 is 32767 bytes (for example in Autonomous DB this is enabled by default). Therefore the database configuration is the one deciding if your limit is 4000 or more.

If your database doesn't have the extended data types enabled, then there isn't much you can do to get a varchar2 of more than 4000 bytes. The only way would be to use a CLOB as posted above, but that data type is a different beast, it comes with it's limits as well.

Hopefully this helps you a bit in identifying the source of the error, and handle it somehow.

Because it's such a long text, you can also consider to not concatenate it in the database (at the end of the day, with such a long text you don't do much other than just displaying it on screen). You could retrieve column1 and column2 separately (both below your max size limit) and then concatenate them on screen, for example with a narrative view in your analysis. The key point is to not ask the database to execute a query it can't execute…

Christian Berg -Oracle

Exactly. Hence: root cause analysis.

Subhakara Netala-Oracle

Thank you all for your suggestions. We have checked the physical sql, problem is with DB. When we run the SQL in DB it is giving the same error. We will check for alternatives like Narrative view as suggested. Thanks.

CLOB data type is not an option for us, there are many limitations if we use CLOB.