Spotfire attempts to create a temporary table when running an information link even though the setting “Allow writing to temp tables” is not enabled.

Spotfire attempts to create a temporary table when running an information link even though the setting “Allow writing to temp tables” is not enabled.

When you are running an information link, without having “Allow writing to temp tables” enabled in the data source settings, you can still in some scenarios* see messages in the log file regarding Spotfire creating, truncating and dropping temporary tables. If the user setup on the information link data source does not have privileges to create and drop temporary tables in the database, errors like below will appear in the logs and the information link fails.


In sql.log you will find log lines like:

[data-source="MyDataSource"] CREATE GLOBAL TEMPORARY TABLE SFTMP20323686680 ("VALUE" FLOAT NULL) ON COMMIT PRESERVE ROWS
[data-source="MyDataSource"] TRUNCATE TABLE SFTMP20323686680
[data-source="MyDataSource"] DROP TABLE SFTMP20323686680
Information Link 'My_Info_Link' execution failed

Example of error message in server.log:

DEBUG 2019-01-11T11:40:36,117+0100 [spotfire, #6, #392] ds.sql.SQLBuilder: A temporary table will be used for the conditions for column: MYCOLUMN

DEBUG 2019-01-11T11:40:36,133+0100 [spotfire, #6, #392] ds.sql.SQLBuilder: 12145 rows inserted into SFTMP20323686680
...
ERROR 2019-01-11T11:40:36,133+0100 [*pool-14-thread-8, spotfire, #6, #392*] ds.sql.SQLQuerySession: Error running TRUNCATE TABLE SFTMP20323686680

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
...

Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist
...

ERROR 2019-01-11T11:48:32,283+0100 [*pool-14-thread-3, spotfire, #19, #81*] ws.dat.AbstractOperator: Start job failed.
com.spotfire.ws.dat.OperatorException: com.spotfire.ws.im.IMException: Failed to execute query: ORA-01031: insufficient privileges

The reason this behavior is seen, is because Spotfire still tries to create temporary tables, without the setting “Allow writing to temporary tables” enabled, when the number of values used for limiting data is greater than what is set on “condition-list-threshold”.

*Note: The  “Allow writing to temporary tables” setting does take effect when joining data from multiple data sources. But, it does not apply when running e.g. on-demand queries.

1. Recommended option: Give the data source user permissions to create, truncate and drop temporary tables in the database (the privilege(s) needed will depend on the type of data source used).
2. Increase the “condition-list-threshold” (property found in the data source template). If the data source itself has a limit, a descriptive exception will probably tell you when exceeding that limit. 
3. Set “expand-in-clause” to true. This will make each value into its own comparison. If the data source has a limit on SQL length, this will maybe at some point be an issue as in suggestion (2). This will however give worse performance.
4. Limit the data sent to on-demand in on-demand settings (but remember this means not all selected rows are sent).