Product: TIBCO Spotfire®
Error "Failed to create temporary result: ORA-12899: value too large for column "SpotfireDatabase"."TempTable"."ColumnName" (actual: 100, maximum: 101)" seen when accessing a TIBCO Spotfire report
While opening a report in TIBCO Spotfire Analyst Client or through Web Player, the following error might be seen in the "server.log" file. The report however, might open fine without any issue.
ERROR 2019-10-2T12:12:59,571-0500 [*pool-14-thread-10, Domain\spotfiretest, #1234, #687868*] ws.dat.AbstractOperator: Start job failed.
com.spotfire.ws.dat.OperatorException: com.spotfire.ws.im.IMException: Failed to create temporary result: ORA-12899: value too large for column "SpotfireDatabase"."TempTable"."ColumnName" (actual: 101, maximum: 100)
When combining data from different data sources, temporary tables are created in one data source with data that is to be joined. The column "ColumnName" here says it is a 100 character string column so the temporary table creates a string column of size 100 characters. Then the data to be inserted into that column contains (at least) one such string that will need 101 characters in the temporary table database for storing. It is common and known consequence that some databases use more characters to store a "value" than other data sources metadata used.
If none of the data sources allow temporary tables to be created, the data from both data sources are copied to the default join database. In Oracle the only standard 7 bit ASCII character that needs more than one space to store by default is ' (an apostrophe). There would be at least one apostrophe used in case of the above error, in the "ColumnName" column. This is what is producing the exception.
To get rid of the error from the logs, you can use below approaches:
1. Check "Allow writing in temporary tables" in the data source (in the Information Designer) where the "ColumnName" column data is coming from. This would also improve performance since the data from this data source would not have to be copied for the join.
2. Load data from the data sources into the report separately and then perform the join in the report. This can be done by using the "Add Columns" operation (see "How to Add Columns" reference for more details)
Note: There is another way to get rid of this error by using Information Services API but that requires custom development and compiling and deploying of java extensions.
Doc: Data Sources