139673408

SUMMARY:

Troubleshooting Data Modeler:Reverse Engineer - Oracle 

TROUBLESHOOTING TIPS:
 
1. ORACLE ON UNIX OR MAINFRAME
   1.1 - Oracle db on Unix
   1.2 - Oracle db on mainframe
2. SYSTEM CONFIGURATION ISSUES
   2.1 - Is Oracle in your path
   2.2 - Install Windows 2000 SP2
3. ORACLE CONFIGURATION ISSUES
   3.1 - Minimum rights
   3.2 - ERROR ORA-00942 table or view does not exist 
   3.3 - DEFAULT_HOME and MULTIPLE HOMEs
   3.4 - Grant or owner 
4. OLEDB ISSUES
   4.1 - UseOracleOLEDB
   4.2 - Check msdoral.dll
   4.3 - Latest version of MDAC
   4.4 - Testing OLEDB with RowSetViewer
5. DEFECTS IN OLDER VERSIONS OF ROSE 
   (NOT an issue if using Rose 2001A or later!)
   5.1 - Login must be upper case
   5.2 - User name must equal schema name
6. CAN CONNECT AND START REVERSE ENGINEERING BUT THEN IT FAILS
   6.1 - Relational vs. object relational
   6.2 - Getting table unknown error
   6.3 - java.lang.nullpointerexception 
7. QUESTIONS IF PROBLEM CONTINUES
 
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
COMMON PROBLEMS AND SOLUTIONS
 
NOTE: For any and all reverse engineering problems with the
Data Modeler it is highly recommended to use most current release 
of Rose, to rule out any issues that may have been fixed from
previous releases.
 
*-> cannot see any schemas to select from in the Data Modeler 
    reverse engineering wizard
 
    SEE 1.1, 3.4, 4.1  

*-> Test connection or attempting to reverse engineer returns
    error:
    "Unable to connect to the database with the information 
    supplied. Please verify the connection information."
 
    SEE 2.1, 3.1, 4.1 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 
###################################################################
1. ORACLE ON UNIX OR MAINFRAME
###################################################################
 
The only OS that Rational certifies Oracle DB connection on is 
Windows.
 
1.1 - ORACLE DB ON UNIX
 
Connecting to a Oracle DB residing on Unix is not an officially 
supported platform.   Note that this is a sometimes works, sometimes
does not situation. Most common problem is cannot see any schemas to
select from in the Data Modeler reverse engineering wizard. 
 
DefectID: 66759 : Cannot see any schema to select from
 
Development has confirmed that there is a problem using OLE DB to 
get the information from Unix. There are some configuration 
environments that we cannot guarantee are set up correctly to make 
this work.
 
Note this should not be a problem with Rational's other modeling
tool, XDE, which has an alternative method of connecting using
JDBC, which does not have the same problems\limitations.
 
One possible solution is to try setting the "UseOracleOLEDB" 
registry setting as outlined below in this Solution.
Otherwise the other suggested workaround would be to reverse
engineer from a DDL file rather than directly connecting to the
database.
 
1.2 - ORACLE DB ON MAINFRAME
 
Test connection returns: table or view does not exist
 
Hosting Oracle DB on a mainframe is not tested or supported.
 
###################################################################
2. SYSTEM CONFIGURATION ISSUES
###################################################################
 
2.1 - IS ORACLE IN YOUR PATH
 
     \ORANT\BIN        (NT default path)
     \ORAWIN95\BIN
     \ORAWIN98\BIN
 
If Oracle files are not in the path, the following error will
occur:
 
 "Unable to connect to the database with the information supplied.
 Please verify the connection information."
 
If it is in your path, try adding it to the front of the path
for test purposes. In previous case an incorrect item in path,
item being in the end of a long path, or more than one \BIN 
directory before this entry has caused problems.
 
To check on NT\Windows 2000, 
 Go to System Properties, Environment tab, PATH.
   
On Win95, go to a command prompt and type PATH.
  To modify PATH open AUTOEXEC.BAT in editor, edit path
  (or run c:\windows\system\sysedit.exe)
  Save. 
 
After updating path, Reboot machine, then re-test.
 
2.2 - INSTALL WINDOWS 2000 SP2
 
We have seen cases where W2K + SP1 was not working, but upgrading to
SP2 (service pack 2) resolved the problem. So one suggestion if 
working on Windows 2000 is to make sure you have installed at 
least SP2 (you can check this by going to control panel and 
opening the help:about windows menu)

You can obtain Windows updates at:
 
 http://www.microsoft.com/windows2000/downloads/servicepacks/default.asp
 
###################################################################
3. ORACLE CONFIGURATION ISSUES
###################################################################
 
3.1 - MINIMUM RIGHTS
 
Make sure you have minimum rights which are CONNECT and RESOURCE 
role.
 
Insufficient permissions will often return the following error:

 "Unable to connect to the database with the information supplied.
  Please verify the connection information."
 
3.2 - ERROR ORA-00942 TABLE OR VIEW DOES NOT EXIST 
 
This error is often Oracles way of saying there is a permission 
problem. If you do not have the permission to a table or view and 
attempt to query them, Oracle will return the above error.  One 
might think you would get an error about "permission" however part
of Oracle security is that if you do not have access, then Oracle 
will not even confirm that the table or view exists for you.  
Instead it returns the above error.  And the Data Modeler is just 
returning the Oracle error it receives.
 
The Data Modeler doesn't actually touch your schema during Reverse 
Engineering.  Instead it is querying the system table and views to 
retrieve information about your schema.  So in most cases what we 
have here when this error message is returned, is a permission 
issue accessing the system tables\views.
 
To test whether indeed this is a permission issue, temporarily 
try reverse engineering as a user with DBA role.
 
3.3 - DEFAULT_HOME and MULTIPLE HOMEs
 
Are you working with more than one version of Oracle?
Are you using Oracle multiple HOME feature?

 Start:Program Files:Oracle For Windows NT:Oracle Home Selector
 (if you do not have multiple oracle homes, a dialog box will
 pop up telling you so).
 
If so check to make sure DEFAULT_HOME points to correct location.
Open Regedit and check the following
Depending upon version of Oracle being used:
 
   [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES]
   LAST_HOME points to your current (or last) home
   directory,
 
   LAST_HOME = "0"
 
Then check this key, HOME0
 
   [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]
 
   ORACLE_HOME = "C:\orant"
   ORACLE_HOME_NAME = "DEFAULT_HOME"
   SHARED_ORACLE_HOME = "C:\orant"
 
-or -
 
   [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES]
 
3.4 - GRANT or OWNER 
 
When the data modeler parses information from the system tables 
during reverse engineering it is looking for one of two things, in 
order to allow you to reverse engineer a particular schema.
 
1- you are listed as the OWNER
or
2- you have been given a GRANT on at least one table within the 
schema.
 
So you could be DBA and have all rights, other tools connect, and 
you still do not meet one of the above two conditions.
 
More Details:

In order for you to reverse engineer an Oracle Schema you must 
have been given the GRANT privilege to at least one table. 
For example:
 
GRANT SELECT ON T_0 TO USERNAME;
 
Note you need to be the owner of the schema or someone else that the 
owner has given privileges to ("WITH GRANT OPTION") to issue a 
GRANT.
 
Once a user has been granted access to at least one table, that
user should now see the schema in the Data Modeler wizard and 
be able to reverse engineer this table and other items in this 
schema.

To confirm if this is the problem open up SQL Plus logged in as 
user attempting to reverse engineer and issue the following query:
 
select * from all_tab_privs where grantee='TheLoginUserName' 
or table_schema <> 'SYS' and table_schema <> 'SYSTEM' and 
table_schema <> 'ORDSYS' and table_schema <> 'MDSYS'
  
Running this query will give you a list of schemas that this user 
can Reverse Engineer. This select works for schema's the user 
doesn't own, but may have been granted access to. 
 
If you do not see the schema name listed in the output, then user
will not be able to access this schema from the data modeler.

EXAMPLE:

username: user1 (password user1)

If we issue this command in SQL Plus:
 
SQL> connect user1/user1;
Connected.
SQL> 
SQL> select * from all_tab_privs where grantee='TheLoginUserName' or
  2   table_schema <> 'SYS' and table_schema <> 'SYSTEM' and   
  3   table_schema <> 'ORDSYS' and table_schema <> 'MDSYS';
 
We see DEMO and SCOTT schema show up in the results returned.
 
And in Rose Data Modeler Reverse engineering wizard, you have only 
these two schemas you can select from:
 
[ ] DEMO
[ ] SCOTT
 
If the owner (or someone with GRANT rights) of another schema,
while connected to this schema, issues a GRANT SELECT on a table 
to user1,  

 GRANT SELECT ON TABLE_1 TO USER1;

(NOTE: There are several ways to grant access to objects in 
schemas, including using roles, Schema Manager, etc.)

There will now be an additional row returned from above query, 
SCHEMA_NAME TABLE_1.

And in Rose Data Modeler Reverse engineering wizard, you now have 
three schemas you can select from:
 
[ ] DEMO
[ ] SCOTT
[ ] SCHEMA_NAME
 
As long as you have access to at least one table, you can reverse 
engineer everything in the schema, ALL Tables, Primary keys, etc.
 
###################################################################
4. OLE DB ISSUES
###################################################################
 
4.1 - USEORACLEOLEDB
 
Data Modeler doesn't show any schema's
-OR-
Unable to connect to the database with the specified information
 
There is a undocumented switch starting with Rose 2002 (and 
included in a hotfix for 2001A, which is no longer generally 
available), that may correct the problem.  It changes the OLE DB 
provider for Oracle from the default MS driver to Oracle's.
 
A. Exit Rose.
 
B. Using Regedit, please add a string value "UseOracleOLEDB" as 
"YES" under, 
 
HKEY_LOCAL_MACHINE\Software\Rational Software\Rose\AddIns\Data Modeler
 
UseOracleOLEDB=No means "don't use Oracle", which makes sure that 
Rose is using the default MS provider. 
Value of "Yes" uses the Oracle provider.
 
Another way to figure out which provider to use is to test with 
RowSetViewer (as described below).  Try the various Oracle 
connections i.e. MS Ole DB for Oracle and Oracle Ole DB and see 
which one works.  Then go to the registry and set the key 
"UseOracleOLEDB=" accordingly.
 
NOTE: It is not suggested to use this feature, unless normal 
connection attempt fails.
 
4.2 - CHECK MSDORAL.DLL
 
MSDAORA.DLL should be located on disk as well as in the registry. 
By default, disk location is:
C:\Program Files\Common Files\System\ole db\MSDAORA.DLL
 
Note that the directory that MSDAORA.dll resides in does NOT have 
to be in the path since MSDAORA.dll is a COM component and hence it 
has location transparency. 
 
If you do not have MSDAORA.DLL to access Oracle db, note that  
this file is installed with MDAC, so suggestion would be to 
installed MDAC. See "LASTEST VERSION OF MDAC" section below.
 
Also, make sure that your Oracle OLE DB Provider is properly 
registered on your client machine, by doing following steps:
 
 Run regedit.exe
 Select HKEY_LOCAL_MACHINE key
 Do a find on "MSDAORA.DLL"
 
The path to the file should match the path specified in the
registry. You will probably find at least two entries. On entry for 
the provider itself while the other for the ErrorLookUp.
 
In some circumstance you may also need to modify the registry to 
make sure msdaora.dll is accessed correctly, see
 
http://www.microsoft.com/data/MDAC25info/MDAC25rtmmanifest.htm
 
Known Issues for MDAC 2.5 RTM (2.50.4403.12) 
 
 Registering Oracle OCI Versions
 Different registry entries are necessary to ensure various OCI 
 versions operate correctly. To enable the OLE DB Provider for 
 Oracle to work with Oracle client software, the client's 
 registry must be modified as indicated in the following table... 
 
The same information above is not listed in later manifests,
so if using later versions of MDAC this may or may not be necessary.  
For more information see,
 
http://www.microsoft.com/data/mdac21info/manifest_intro.htm
 
MDAC Release Manifests 
 
4.3 - LASTEST VERSION OF MDAC
 
Data Modeler uses the MDAC provided MS OLE DB Provider for Oracle 
to connect to Oracle DBMS. MDAC is installed with numerous Microsoft 
products and also can be downloaded separately.  If the client work 
station can not run MDAC or has a improper installation of MS OLE DB 
Provider for Oracle, Rose DM will not work. 
  
There have been reports from some users, that they have needed to
upgrade the version of MDAC they had on their computers.
So suggestion would be to make sure you have,
MDAC 2.6 SP1 (2.61.7326.6), or later version.
  
You can obtain MDAC downloads at,
 
 http://www.microsoft.com/data/

There is no built in way to determine which version of MDAC you have 
running. To do so, you need to download a MS utility named Component 
Checker from,
 
 http://www.microsoft.com/data/download.htm
 
The Component Checker is a customizable tool that performs the 
following tasks: 
 
- Identifies the current MDAC installation on a computer. 
- Creates a series of reports about the files identified for the 
  current MDAC installation. 
- Removes the current MDAC installation after listing .dll 
 
4.4 - TESTING OLEDB WITH ROWSETVIEWER
 
If a configuration issue is suspect, and you have rule out other 
issues listed above, then we would like to try the following test 
to confirm there is not a problem with the OLE DB provider.
 
Data Modeler uses MDAC OLEDB driver for Oracle that sits on the 
top of Oracle client software.  To validate whether this is a Rose 
issue or a configuration issue outside of Rose we would like to 
test the OLEDB connection using an alternative method,
with a MicroSoft utility called RowsetViewer.
 
This utility can be obtain from MS and is included in
MDAC SDK - OLE DB Samples. Which can be downloaded from the 
"SDK Update Site" at,
 
 http://www.microsoft.com/msdownload/platformsdk/sdkupdate/
 
And select link for MDAC SDK  
 
There are quite a few files include with RowSetViewer, but the 
only one needed for the test is rowsetviewer.exe

TEST #1
 
  Start this program.
  Go to File:Data Links
  On the Provider tab select, Microsoft OLE DB Provider for
  Oracle Next button will then take you to Connection tab.
 
  Enter Service name, user name and Password
 
   example:
 
    SERVER NAME: tcp-loopback.world
    USERNAME: scott
    PASSWORD: tiger
 
  Then [Test Connection]
 
  What was the result of this test?
 
  If this test fails, so will Data Modeler.
 
TEST #2
 
 1) Start Rowsetviewer.exe
 
 2) Select "Full Connect" icon on toolbar (first icon on left)
 
 3) Under Connection, change Provider to "MSDAORA"
    Under Source change DataSource to point at your database
     i.e. either a Oracle Server name or a service name (created 
     by net8 easy config) 
    Under Login, enter your UserID: and Password
    Leave Prompt as what it defaults to.
    [OK]
 
 4) Enter in the following SQL code into the command window:
 
    select * from all_tab_privs where grantee='TheLoginUserName'  
    or table_schema <> 'SYS' and table_schema <> 'SYSTEM' and  
    table_schema <> 'ORDSYS' and table_schema <> 'MDSYS'
 
    Using your mouse, now HIGHLIGHT this line of code.
 
 5) Select Execute icon on toolbar (SQL with arrow under it,
    fifth button from left)
 
 6) Go with defaults, [OK]
 
 7) You should now see a results set from the query.
    Please do a screen shot, and forward this to us.
 
NOTE: When you run RowSetViewer it gives you a list of drivers to 
select from, some of the drivers are named very similarly.
There is a "Oracle Provider for OLE DB" and a "Microsoft 
OLE DB Provider for Oracle". The default driver the Data Modeler 
will use is the MicroSoft driver.  If you do not have this driver, 
update the version of MDAC you have on your machine.
 
If "Test Connection" fails and RowSetViewer test fails,
 
Possible solution would be to install the latest version of 
Microsoft Data Access Components. see LASTEST VERSION OF MDAC 
section above.
 
###################################################################
5. DEFECTS IN OLDER VERSIONS OF ROSE: 
   (NOT an issue if using Rose 2001A or later!)
###################################################################
 
5.1 - LOGIN MUST BE UPPER CASE
 
DEFECT IN: rose 2000e, 2001
FIXED IN: Rose 2001A
 
In some situations information may be lost or you can have
trouble connecting if you login in the Data Modeler in lower 
case.  Try login in, in all upper case,
 
 i.e. SCOTT\TIGER not scott\tiger.
 
5.2 - USER NAME MUST EQUAL SCHEMA NAME
 
DEFECT 28572 -
Must have the same password as the user that created the schema to 
get reverse engineering access to it.  
 
PROBLEM WITH: Rose 2000e, 2001
FIXED IN: Rose 2001a    
 
DETAILS:
You must have the same password as the user that created the
schema to get reverse engineering access to it.
 
 > I create user TEST with DBA role.
 > I can reverse engineer test.test.
 > If I log into DM as test, and select SCOTT schema instead
 > of TEST schema I get,
 >
 > 15:06:53|  [Data Modeler - Reverse Engineer - Database]
 > 15:06:53|    ORA-01017: invalid username/password; logon denied
 
###################################################################
6. CAN CONNECT AND START REVERSE ENGINEERING BUT THEN IT FAILS
###################################################################
 
6.1 - RELATIONAL VS. OBJECT RELATIONAL
 
Are you are using strictly relational constructs in this schema? 
And NOT the object oriented features (varrays, custom types, nested 
tables, etc.)? 
 
Note that the Data Modeler only works with relational constructs.  
If you wish to reverse engineer objects, use the Oracle8 addin.
If the schema contains objects, you may receive errors or in some
cases a access violation\unhandled exception may occur.
 
For more information see the following Solution:

 SOLUTION: 11964
 TITLE: Which addin should I use, Oracle 8 Addin or Data Modeler?
 
6.2 - GETTING TABLE UNKNOWN ERROR
 
Although the database connection is functioning (tested 
successfully), there is something happening that is not allowing 
the reverse engineering to complete without an error:
 
------------------------------------------------------------------ 
Reverse Engineering Wizard
  Generating Model
    Please wait while the reverse engineering process completes.
 
    Getting table Unknown error(0x800a0cca1) from database
 
    The reverse engineering wizard was unable to complete 
    successfully. Please review the Rose Log for an explanation 
    of the error.
------------------------------------------------------------------ 
 
However there are no error messages in the Rose Log.
 
Resolution:

If you select Oracle 8 as the target in the Data Modeler Reverse
Engineering Wizard while access Oracle 7 schema, you will get 
this error.  Select the correct target.
 
 
6.3 - JAVA.LANG.NULLPOINTEREXCEPTION 
 
The usual cause of java.lang.NullPointerException during RE of a
schema is some unhandled syntax is being encountered.
 
There are a couple of defects where this exception could occur if 
the Data Modeler was trying to parse something it did not 
understand. Defects; 12607, 11566, 11048.
Most of these are marked as fixed in later versions of Rose,
so make sure you are working with latest release.
 
One known case this is still marked as an open defect is,
 
Defect: 66314 : java NullPointerException using DM to RE Oracle 8 DB
 
Problem is related to a cross schema referencing. By that we
mean there are tables in one schema with foreign keys or 
other references to another schema. Data Modeler only knows 
how to handle items in the current schema. As soon as you are 
trying to get information from another schema (outside of the 
one you are analyzing), errors are encountered.
 
Cross schema referencing is currently not supported in Rose
Data Modeler (NOTE: XDE 1.1 Data Modeler does support cross schema 
referencing).
 
The only workaround is skip the items in question if doing a direct
connect to database, or to edit these out of DDL file.

To identify the problem references,

Reverse engineer the DDL file.  Problems with cross reference should
show as errors in the logs.

To do a selective reverse engineer,

- In the Component View create a new database and assign to a 
target database that matches the version of Oracle you are working
with.

- In the Logical View create a new schema, assign it to the 
database created in above step and name it exactly like the schema in 
the database that you want to reverse engineer.

- Instead of using the option 'reverse engineer', use the option 
'compare and sync' with the database. Compare and Sync will allow
you to selectively reverse engineer or import part of the schema.
       
If you have the latest release of the Rose and your schema does
not contain references to other schemas, next step in troubleshooting
this error would be to confirm if problem only happens on a 
particular schema. Export schema to DDL and attempt to RE the DDL.   
If this also fails, we should get a error in the Rose log 
identifying the line. If possible getting the DDL would allow us 
to troubleshoot further. We can then look at the DDL code to see 
if this is a known issue or file a defect if necessary.
 
In some rare cases the error has occurred when RE directly against 
the database, but not when RE from the DDL file.   In this case we 
need to see if we can get the DDL to reproduce. i.e. read it into 
a test database and RE from the Data Modeler.
 
###################################################################
7. QUESTIONS IF PROBLEM CONTINUES
###################################################################
 
-> What exact version of Rose is being used? 
   (help:about, Rose version: 7.0.????, 7.1.????, 7.5.????, etc.)
 
 
-> What operating system is Rose installed under?
 
 
-> What exact version of Oracle is being used? 
   (8.0.6, 8.1.7, etc.)
 
 
-> Where is the database, local or remote?  If remote what 
   operating system?
 
 
-> Do you have the same oracle client version installed on the 
   local machine as the version of Oracle on the server?
 
 
-> Do you have more than one version of Oracle installed?
   Are you using Oracle 8i multiple HOME feature?
 
 
-> Any and All EXACT error(s) you are receiving?
 
 
-> What operation are you doing when getting the above error?
   Starting reverse engineering, Test connect, after selecting
   Schema and pressing next button?
 
 
-> Can you connect to the database using Oracle tool SQL Plus?


 
NOTE: While you would use the same user name and password with
Data Modeler to connect to database, the Data Modeler and
SQL Plus do not both connect in the same way.  However if
SQL Plus will not connect, then the system would appear not
to be configure correctly, and Data Modeler will likely fail.
 
-> Can you connect using the Oracle8 addin instead of the Data Modeler 
addin?
 


NOTE: Again the Oracle8 addin and the Data Modeler connect to
the database using different methods.  Oracle8 addin uses
OCI (Oracle Call Interface) and the Data Modeler uses OLEDB.
However if the Oracle8 addin also fails, please forward us the
error message, as this error may help us locate the likely 
source of the problem.
 
-> Can you access other schemas?
 


Do you get the same problem trying to reverse engineer any
Oracle schema. i.e. sample table SCOTT/TIGER, or if that 
isn't available, create a simple test schema, add one 
simple table to it, attempt to reverse engineer this?
 
-> Can any other user or machine connect?
 


Can you Connect to this same database and schema from another
machine in your office as the SAME user? As a different user?
As the user who is the owner of the schema?
 
-> Test with DBA permissions.
 


Since the Data Modeler reads schema information from the system 
tables, to test whether it is a rights issue, can you test with DBA 
rights?

->  Can you forward DDL?



If problem happen on multiple schema ("production" and "test" for
example) then sending in the DDL to support is probably not going 
to help. On the other hand if problem is only reproducible with
some but not all schemas, then please forward DDL.


Rational Customer Service Policies and Information: http://www.rational.com/support/info.jsp