Error ORA-06550 When Connecting CA ERwin Data Modeler to Model Manager

Introduction

One of the most commonly used databases with AllFusion ERwin Data Modeler is Oracle. ORA-06550 is one of the most common errors users experience when connecting from AllFusion ERwin Data Modeler to AllFusion Model Manager Repository, hosted on Oracle, after an upgrade.

Examples of the Error Message

Here are some examples of the ORA-06550 error message that users might experience when connecting from AllFusion ERwin Data Modeler to AllFusion Model Manager:

Oracle Vers. 8.xx/9i error 6550, severity 16:
ORA-06550: line 1, column 7: PLS-00201: identifier 'MM_INITCACHEDID' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Or

Oracle Vers. 8.xx/9i error 6550, severity 16:
ORA-06550: line 1, column 7: PLS-00201: identifier 'MM_CLEARCACHEDID' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Or

ORA-06550, line 1 column 7, PLS-00201: Identifier 'xxx.MM_Repository_status' must be declared.
PL/SQL: Statement ignored

Or

ORA-06550: line 1, column 7:
PLS-00201: identifier 'M7_SERIALNUMBER' must be declared

Possible Causes for These Errors

A Non Schema Owner Performed a Recent Update

When upgrading AllFusion Model Manager from one version to another, and when the ModelMart connection box appeared during this process, an AllFusion Model Manager user other than the schema owner was used to connect and perform the update.

Role other than MMUSER was selected during recent update

When upgrading AllFusion Model Manager from one version to another, after getting connected to the AllFusion Model Manager repository, in the ModelMart Role drop down list, a Role other than MMUSER was selected.

How to Recover

Either of the two causes above may have produced one or both effects i.e. either the AllFusion Model Manager user does not have sufficient privileges to connect to the repository (database), or the public synonyms no longer exist.

Here are two scripts that resolve this connection problem in most cases. If it is not clear which situation caused the error message, there is no harm in running both scripts to cover both situations.

Caution: If you are not the DBA, please consult your DBA before running any of the following scripts. Although no objects will be removed, it is always a good idea to back up your database and consult your DBA before modifying any production environment. If you are the DBA, and you still have concerns or questions, please feel free to contact Computer Associates Technical Support before running these scripts.

Granting AllFusion Model Manager users the privileges necessary to access objects in an AllFusion Model Manager repository.

Caution: If the installer used a different name than the default MMUSER role for the Model Manager users, you will need to replace the MMUSER role name with the correct role name in the following script:

This script works for Both 3.0.2 ModelMart and 4.1 ModelManager:

In Oracle SQL*Plus, login as schema owner of AllFusion Model Manager:

set pagesize 1000
set verify off
set feedback off
set heading off
spool c:\mmgrants.sql
select 'grant ALL on ' || object_name || ' to MMUSER;' from all_objects
where (object_name like 'ERW%' or object_name like 'IDW%' or object_name like 'MM%') and object_type in ('TABLE', 'PROCEDURE') 
order by object_name;
spool off
set heading on
set pagesize 24
set verify on
set feedback on
@c:\mmgrants.sql

This script works for versions 7.x and 8.x of CA ERwin Model Manager:

-- Run as schema owner, DBA role is NOT required. Thanks.
   set pagesize 1000
   set verify off
   set feedback off
   set heading off
   spool c:\mmgrants.sql
   select 'grant ALL on ' || object_name || ' to MMUSER ;' from user_objects
   where (object_name like 'M7%')
   and object_type in ('TABLE', 'PROCEDURE','VIEW','PACKAGE')
   order by object_name;
   spool off
   set heading on
   set pagesize 24
   set verify on
   set feedback on
   @c:\mmgrants.sql

Creating all necessary public synonyms on the repository so all users will be able to access repository objects:

NOTE: in many cases Technical Support has found that some of the public synonyms on a mart have been broken. Therefore, if you suspect that to be the case, please first run this script to drop all of the public synonyms before recreating them using the script below. Please change the name 'MODELMART' to be the name of your Model Manager schema owner before executing the drop script.

This drop script works for both 3.0.2 ModelMart and 4.1 ModelManager

set pagesize 1000
set verify off
set feedback off
set heading off
spool c:\mmdropps.sql
select 'drop public synonym ' || object_name || ';'
from all_objects
where owner = 'MODELMART' and (object_name like 'MM%' or object_name like 'MM_%')
and object_type in ('TABLE','PROCEDURE')
order by object_name;
spool off
set heading on
set pagesize 24
set verify on
set feedback on
@c:\mmdropps.sql

This drop script works for versions 7.x and r8.x of CA ERwin Model Manager

set pagesize 1000
set verify off
set feedback off
set heading off
spool c:\mmdropps.sql
select 'drop public synonym ' || object_name || ';'
from all_objects
where owner = 'MODELMART' and (object_name like 'M7%' or object_name like 'M7_%')
and object_type in ('TABLE','PROCEDURE')
order by object_name;
spool off
set heading on
set pagesize 24
set verify on
set feedback on
@c:\mmdropps.sql

This create script works for both 3.0.2 ModelMart and 4.1 ModelManager

Caution: The following script uses the generic name MODELMART as the owner value. Please replace the owner value with your schema owner name.

set pagesize 1000
set linesize 2000
set verify off
set feedback off
set heading off
spool c:\mmps.sql
select 'create public synonym' || object_name || ' FOR ' || object_name || ';'from all_objects
where owner = 'MODELMART' and(object_name like 'ERW%' or object_name like 'MM%' or object_name like 'IDW%' or object_name like 'EVT%'
or object_name like 'SMP%')
and object_type in ('TABLE','PROCEDURE')
order by object_name;
spool off
set heading on
set pagesize 24
set verify on
set feedback on
@c:\mmps.sql

This create script works for versions r7.x and r8.x of CA ERwin Model Manager:

NOTE: 'MMADMIN' needs to be replaced with the name of the user who is the AllFusion Model Manager schema owner. CAPITALS / case counts. If you are using Oracle 9.x, please remember to capitalize the owner name.

   set pagesize 1000
   set linesize 2000
   set verify off
   set feedback off
   set heading off
   spool c:\mmps.sql
   select 'create public synonym' || object_name || ' for ' || object_name || ';'
   from all_objects
   where owner = 'MMADMIN'  and (object_name like 'M7%')
   and object_type in ('TABLE','PROCEDURE','VIEW','PACKAGE')
   order by object_name;
   spool off
   set heading on
   set pagesize 24
   set verify on
   @c:\mmps.sql

Conclusion

If you initialize and upgrade your AllFusion Model Manager using schema owner and the correct role (MMUSER), as described in the manual, these error messages will be avoided. However, if you do encounter the error messages above or similar to above (ORA-06550), both the scripts of grants and public synonyms should help you recover without having to restore and perform the upgrade again.

For further reference, please also see Article ID: 626 Export/Import of an Oracle Database for AllFusion Model Manager R7.