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

Last updated on JULY 13, 2023

Applies to:

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

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