T79-2020 Creation of SQL profile in Oracle causes Workbook Logins to Fail

Program

BIOVIA Workbook 2016, 2017, 2017R2, 2018, 2019, 2020

Operating System

All operating systems supported for BIOVIA Workbook

Description

In Workbook 2016 and later, the creation of a SQL profile in the Oracle database on the specified SQL statement below causes no rows to be retrieved and Workbook Logins to fail.

SELECT GUID FROM (SELECT GUID, VAULTOBJECTGUID FROM VAULTCOLLECTION WHERE GUIDOBJTYPE = 'Group' AND VOGUIDOBJTYPE IN ('Group' ,'User')) START WITH VAULTOBJECTGUID = :B1 CONNECT BY NOCYCLE VAULTOBJECTGUID = PRIOR GUID.

The SQL ID is 7bf2jz97kxkzx. This is due to known Oracle bug - 21070321.

Resolution

The resolution is to disable the SQL profile following the steps below:

1. Identify the name of the SQL profile that was created for this statement by querying DBA_SQL_PROFILES.

2. Use the example statement below to disable the SQL profile. Substitute the correct name as identified in the first step.

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => 'SYS_SQLPROF_045d9a6d8d3b0000',

attribute_name => 'STATUS', value => 'DISABLED');

END;

BIOVIA Support

If you have any questions, please contact BIOVIA Support.