My Oracle Support Banner

Unable to initialize DMW Business Area with logon trigger (Doc ID 2764665.1)

Last updated on APRIL 04, 2021

Applies to:

Oracle Health Sciences Data Management Workbench - Version 2.4.3 to 2.4.3 [Release 2.4]
Information in this document applies to any platform.

Symptoms

When trying to initialize a DMW Business Area in LSH database using a logon trigger created for a user, the following error occurs:

"Error granting SELECT access to view DISCREPANCIES_V in BA_DMW.
Error: ORA-30511: invalid DDL operation in system triggers"

The following steps are performed to replicate the problem:

1. User e.g. <USER_NAME> logs into the database using SQL*Plus.

2. Logon trigger e.g. TEST_USER.TRG_TEST_USER_LOGON is fired and executes the code to initialize the BA e.g. BA_DMW Business Area (hardcoded in the trigger).

3. In the same SQL*Plus session, the <USER_NAME> performs a select count(*) from BA_DMW.<TABLE_NAME> table -> 0 rows returned.

4. The table <USER_NAME>.<TARGET_TABLE> gets populated with an error row saying that it was not possible to grant access to DISCREPANCIES_V view.

When the logon trigger code is executed directly from the SQL Developer or SQL*Plus, it seems to work fine for the same Business Area - select count(*) from BA_DMW.AE table returns > 0 rows.

Error returned by the trigger is :

"Error granting SELECT access to view DISCREPANCIES_V in BA_DMW.
Error: ORA-30511: invalid DDL operation in system triggers"

Additional information:
1. The problem occurs when trying to initialize "DMW Business Area" (example: BA_DMW).

2. The problem does not occur when initializing "LSH Business Area" (example: BA_LSH).

In case of LSH BAs, there is no DISCREPANCIES_V, but the observation is that before initialization, select statement on BA_DMW.<TABLE_NAME> table returns 0 rows and after initialization
by the trigger, the same statement returns > 0 rows. What's the difference in case of LSH and DMW BAs, assuming that in both cases, at least grants on tables would be issued?

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.