Unable to see Spotfire reports after database upgrade from Oracle 12 to Oracle 19

Keywords: Tibco Spotfire Server

Details:

This issue could occur in environments where Spotfire Oracle database was updated  from 12.2 to 19.1.

After database upgrade, when one tries to modify a Spotfire Library analysis file in Spotfire Analyst or Web Player, the following error can occur:

-----------------------

Error message: Could not publish <report name>.

LibraryException at Spotfire.Dxp.Framework:
Illegal argument. The property value cannot be empty (HRESULT: 80131500)

----------------------------------------

The following article might be helpful in troubleshooting this issue:

https://community.tibco.com/s/question/0D54z00007mxZQnCAM/saving-file-to-library-fails-with-the-error-message-the-analysis-could-not-be-saved-to-the-library-n-illegal-argument-the-property-value-cannot-be-empty

As per above article, the issue could be due to Oracle JDBC driver "Oracle (DataDirect Driver)".

Resolution:

The Oracle driver version should be the jar file that matches Java version of your Spotfire Server version. If you're using Spotfire 10 or newer, then ojdbc8.jar is usually a good choice. 

Steps to resolve this error:

1. Make a backup of the Spotfire Server database schema before running the SQL query below.

2. Run this query in Spotfire Server database schema:

UPDATE LIB_PROPERTIES set PROPERTY_BLOB_VALUE = null where dbms_lob.getlength(PROPERTY_BLOB_VALUE) = 0;

3. Commit the change to database, then restart Tibco Spotfire server Windows service and test the behavior.

This should resolve the issue, but if you still see the same problem, please share with us Oracle data source template screenshot from Spotfire Server Configuration Tool (in Configuration tab).

The root cause of this issue is one or more zero-length values in LIB_PROPERTIES.PROPERTY_BLOB_VALUE column, for example:

"ERROR 2022-05-18T13:30:11,115-0400 [<username>, #B-20,#844] ws.library.LibraryService: An exception occurred in method 'loadItems'.
com.spotfire.ws.library.StorageException: ILLEGAL_ARGUMENT, The property value cannot be empty"

Spotfire expects it to be either 'null' or some value, but not an empty cell with zero length. So, updating these values and setting them to 'null' using the UPDATE query above resolves the issue.