BSI 8.3.5.5 Business Logic Scope fail with ORA-00904

Products

• CA Business Service Insight

Issue/Introduction

BSI Business Logic Scope runs fail while viewing the data, after migrating the underlying database to a new server.

Unexpected COM error: [External error, Caught COM ERROR in operation QUERY ( select dbms_crypto.hash( nvl (timeslot_map_type  ||                               timeslot_week_map          ||                               timeslot_year_day_map      ||                               timeslot_year_hour_map , '0') , 1) timeslot_hash , timeslot_map_type                                        , timeslot_week_map                                        , timeslot_year_day_map                                    , timeslot_year_hour_map                                      from T_Compound_Timeslot_Elements ctse,                                 t_timeslots ts,                                                 t_rules r                                                      where r.rule_id = 1015                                           and r.Compound_Ts_Id = ctse.Compound_Ts_Id                                  and Ctse.Timeslot_Id = ts.Timeslot_Id)  [ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier (HR: 0x80004005) (Unspecified error) (Source = OraOLEDB)][(Error #80004005) (Source = OraOLEDB) (Description = ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier) (NativeError: 388)][(Error #80040e14) (Source = OraOLEDB) (Description = ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier) (NativeError: 388)]
PreviousError:
Caught COM ERROR in operation QUERY ( select dbms_crypto.hash( nvl (timeslot_map_type  ||                               timeslot_week_map          ||                               timeslot_year_day_map      ||                               timeslot_year_hour_map , '0') , 1) timeslot_hash , timeslot_map_type                                        , timeslot_week_map                                        , timeslot_year_day_map                                    , timeslot_year_hour_map                                      from T_Compound_Timeslot_Elements ctse,                                 t_timeslots ts,                                                 t_rules r                                                      where r.rule_id = 1015                                           and r.Compound_Ts_Id = ctse.Compound_Ts_Id                                  and Ctse.Timeslot_Id = ts.Timeslot_Id)  [ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier (HR: 0x80004005) (Unspecified error) (Source = OraOLEDB)][(Error #80004005) (Source = OraOLEDB) (Description = ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier) (NativeError: 388)][(Error #80040e14) (Source = OraOLEDB) (Description = ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier) (NativeError: 388)][LoadForRule] (HR: 0x80004005) (Unspecified error) (Source = TimeSlotUtilsBO.TimeSlotUtils.1)]  

Environment

Release : 8.35

Component : BSI - Ace engine

Resolution

The BSI error states that the underlying problem is again with the copied database:

ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier

The DBMS_CRYPTO package belongs to SYS, but should have been granted to the users that BSI uses. You can see the grants needed in %OG_HOME%\Setup\InstallDB\Build_TBS_Users\Users.sql

1) It creates the role 'role_usr_reg_privs' and puts all the BSI users in it, so please check this survived the migration.

2) It grants execute to this group, oblicore and oblidbadmin.

So recreate that group if necessary, then run

  grant execute on dbms_crypto to role_usr_reg_privs , oblicore , oblidbadmin

assuming that your main oblicore user is indeed called oblicore. You can open %OG_HOME%\Utilities\PassUpdate\Passupdate.exe to see which users it's actually using to connect to the database.