First make sure you want to use the Oracle8 addin as oppossed to the
Data Modeler addin. What's the difference?
The main difference is Object-Relation vs. Relational features.
If you are using Oracle 8 and taking full advantage of the
specific object-relational features in Oracle 8 (such as; user
defined types, arrays and nested tables) that were not available
in Oracle 7, then you want to be using the Oracle 8 add-in.
If you are just using Oracle as a relational database and just
using the features that are not object specific, you can
use the Data Modeler add-in.
Data Modeler has more features, so if not using object extensions,
I would suggest using that. (see online help, contents tab,
Rose Data Modeler, How to). If you need to use the o8 addin, here's
a technote on troubleshooting connection problems.
TAN ID: 6451
PRODUCT: Rose
OS: Win95/NT
DEFECT #:
PATCH #:
REFERENCES:
CREATED: 2/23/99
REVISED:
SUMMARY: Oracle8 Analyze Schema, errors and solutions
BODY:
Tools:Oracl8:Analyze Schema
I. Error messages
II. Solutions
III. Quick getting started example
------------------------------------------------------------------
I. Error messages
------------------------------------------------------------------
------------------------------------------------------------------
Oracle 8 AddIn
The schema '<schema name>' was not found on '< Datbase Server
name>'. Please check with your database administrator.
------------------------------------------------------------------
CAUSED BY:
incorrect: schema name (2)
user name (2)
password (2)
------------------------------------------------------------------
Oracle 8 AddIn
This option requires the Oracle 8 Client to be installed.
------------------------------------------------------------------
CAUSED BY:
Oracle-Client not installed (4)
DEFECT 147021 - Oracle8 addin fails with oracle8i (9)
------------------------------------------------------------------
or8addin
Error: Failed to Connect to Database
------------------------------------------------------------------
CAUSED BY:
incorrect: Database Server name (2)
------------------------------------------------------------------
or8addin
Error - ORA-00942: table or view does not exist Code=942
------------------------------------------------------------------
CAUSED BY:
Insufficient privilages (on this or underlying tables) (3)
Using synonyms
Misspelled
Underlying table does not exist (i.e view and table was deleted)
Oracle7 table in Oracle8 (5)
International language option (6)
------------------------------------------------------------------
or8addin
ERROR - ORA-01406: Fetched column value was truncated
------------------------------------------------------------------
CAUSED BY:
DEFECT 133838, fixed in 98i (7)
------------------------------------------------------------------
Rose crashes:
------------------------------------------------------------------
CAUSED BY:
Insufficient permissions (3)
Oracle7 table in Oracle8 (5)
DEFECT, fixed in 98i (8)
------------------------------------------------------------------
II. Solutions
------------------------------------------------------------------
(1) REQUIREMENTS
(2) ENTRIES IN ANALYZE SCHEMA DIALOG BOX
(3) PERMISSIONS
(4) ORACLE CLIENT INSTALLED? ORACLE IN PATH?
(5) ORACLE7 TABLES
(6) WORKING IN A LANGUAGE OTHER THAN ENGLISH?
(7) DEFECT 133838 - FETCHED COLUMN VALUE WAS TRUNCATED
(8) CRASHES
(9) DEFECT 147021 - ORACLE8 ADDIN FAILS WITH ORACLE8I
------------------------------------------------------------------
(1) REQUIREMENTS
The Professional Editions do not ship with Oracle8 addin.
To reverse engineer Oracle8 tables you need to have the Rose
98 Enterprise edition (only version that ships with Oracle8
addin). This addin will forward and reverse engineer existing
Oracle8 tables.
The following are the requirements for the Rose Oracle8
product:
Target database: Oracle8 version 8.03 GA version or later.
Rose will not work correctly with beta
versions.
Connectivity software: Native Oracle8 client software released
with Oracle8 version 8.03 or later.
Microsoft Windows 95 or NT 4.0
An 80486-based or Pentium-based PC-compatible computer system
32 Mb of RAM (64+ Mb recommended)
A minimum of 80 Mb of disk space (Rose 98 install)
95 MB (Rose 98i Enterprise)
+ 220mb (oracle8 install)
+ 80mb (oracle8 doc)
A SVGA-compatible display (256 or more colors recommended)
Any pointing device with at least two buttons
(2) ENTRIES IN ANALYZE SCHEMA DIALOG BOX
LOGING IN
(Not case sentsitive)
EXAMPLE:
SCHEMA: demo
DATABASE SERVER: <blank>
USERNAME: scott
PASSWORD: tiger
- SCHEMA:
An Oracle database can contain one or more schemas. A schema
is a collection of database objects that can include: tables,
views, indexes and sequences. By default, each user has their
own the schema which has the same name as the Oracle username.
If user name = test, then by default the schema name = test.
( The object names are also prefixed by the schema name as in:
schema.object_name )
- DATABASE SERVER:
If the Server is local, leave this entry blank.
If the Server is not local, for DATABASE SERVER, do NOT
enter anything except the Server name (i.e. no '\' drive
letters or other information). Use the same string you would
use for HOSTNAME when connecting to SQL*PLUS.
This connect string can also be found in TNSNAMES.ORA,
The syntax for the TNSNAMES.ORA:
descriptor_alias=
(DESCRIPTION=
(ADDRESS=
[(COMMUNITY=community_name)]
(PROTOCOL=protocol)
(protocol specific information)
)
(CONNECT_DATA=
(SID=sid_alias)
)
)
It is the descriptor_alias that you reference in the connect
string when you connect to a server.
NOTE: If database is not local.
In order for Rose Oracle8 addin to connect to the database, it
is require that the OracleTNSListner80 service be running on
the host servers in order for the oracle 8 addin to connect to
the host.
- USER NAME:
Your Oracle username.
- PASSWORD:
Your Oracle password.
(3) PERMISSIONS
"table or view does not exist" error is often the database
server's way of saying, "You don't have permission to access
this table" you NEED to have the necessary Oracle permissions to
read the system tables, since Rose will need to have access
to these in order to reverse engineer.
Minimum Privileges for the user accessing the database from
the oracle 8 addin:
------------------
Connect
Resource
System Privileges
Select any table
( Using the security manager in Oracle 8, open the user you
want to use in Rose/Oracle 8, right-click on 'System
Privileges Granted', Select 'Add Privileges to users..'.
Select the user again and select Privilege Type:
'System Privileges'. Scroll to the bottom of the list and
select 'SELECT ANY TABLE'. Apply the changes. )
These are the tables\views that the oracle 8 addin accesses:
ALL_TAB_COLUMNS
ALL_TABLES
ALL_METHOD_RESULTS
ALL_TYPE_METHODS
ALL_TYPE_ATTRS
ALL_COLL_TYPES
ALL_TYPES
ALL_METHOD_PARAMS
ALL_CONS_COLUMNS
ALL_CONSTRAINTS
ALL_COL_COMMENTS
ALL_TAB_COMMENTS
ALL_VIEWS
DBA_TRIGGERS
ALL_IND_COLUMNS
ALL_INDEXES
ALL_OBJECT_TABLES
DBA_SOURCE
ALL_USERS
Data dictionary views are accessible to all users of an Oracle
Server. Most views can be accessed by any user with the
CREATE_SESSION privilege. The data dictionary views that begin
with DBA_ are restricted. These views can be accessed only by
users with the SELECT_ANY_TABLE privilege.
Example to test for needed priviledges:
// User with sufficient rights can DESC DBA_ file:
SQL> connect
Enter user-name: test1
Enter password: ********
Connected.
SQL> desc dba_source;
Name Null? Type
------------------------------- -------- ----
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NOT NULL NUMBER
TEXT VARCHAR2(4000)
// User with insufficient rights cannot DESC DBA_ file:
SQL> connect
Enter user-name: test2
Enter password: ********
Connected.
SQL> desc dba_source;
ERROR:
ORA-04043: object SYS.DBA_SOURCE does not exist
If you still have problems, test by granting DBA priviledge to
the user. If it still fails with DBA, then permissions is not
the problem.
(4) ORACLE CLIENT INSTALLED? ORACLE IN PATH?
- Is there a Oracle-Client installed?
You need to have the Client Side Oracle API installed, which can
connect to Oracle Server. Rose uses some of the DLLS provided
by Oracle. Then using Rose/Oracle you should be able to see the
database.
- Can you connect to your database on _this_ machine using
SQL*Plus? If this fails, then more than likely Rose will also
fail.
- Can you connect using Net8 Assistant? If not, then the
client software is not installed correctly.
Just testing with SQLPlus is not a complete test. Depending
upon how you configure SQLPlus it may not be connecting to
the database the same way Rose is attempting to. Rose uses
Oracles OCI to connect to Oracle.
Start Net8 Assistant.
Highlight a Service Name, for example TCP-LOOPBACK.WORLD
Tools:Test Service Name Conectivity...
Enter in username and password, for example scott, tiger
If the connection test was successful.
Again try Rose, Tools:Analyze schema using this information:
SCHEMA: tiger
DATABASE SERVER SERVER: tcp-loopback.world
USERNAME: scott
PASSWORD: tiger
- Is Oracle in your path?
Verify that ora803.dll and oci.dll are in your oracle \bin directory
and this directory is in your path.
\ORANT\BIN (NT default path)
\ORAWIN95\BIN
\ORAWIN98\BIN
If Oracle files are not in the path error this can occur.
If it is in your path, try adding it to the front of the path
for test purposes. In previous case and incorrect item in path
before this entry has caused problems.
"My local DBA found the problem. The problem was caused by a faulty
PATH statement. Some tool's installation program apparently
inserted a blank into my PATH statement. It caused the PATH
statement to fail, and Oracle's ODBC support was not accessible to
Rose. But everything is well now."
To check on NT 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, add to path
(or run c:\windows\system\sysedit.exe)
Save. Reboot machine.
(5) ORACLE7 TABLES
Rose will crash working with Oracle7 tables. Accessing them
through Oracle8 does not change this behavior. The database
must be migrated to Oracle8 format. A database migration
alters the data dictionary and other control structures to
make an existing database conform to the definitions of a
different Server release. Migration can be accomplished with
the Migration utility, which converts the structures in an
existing database, or by performing an export/import, which
copies the data from one database into the structures of
another database. See Oracle help on "Database Migration".
To make sure this is all we are dealing with, test the demo
database through Oracle8 to see if the crash persists. With
the demo created through Oracle8 it should RE correctly
through Rose. If it does not then we are probably looking at
a permissin problem.
>I have two versions of my host database on UNIX: one is in
>Oracle7 and one is Oracle8. When I access the Oracle7
>database, I get a "Table or view not found".
It is possible to use a schema created in Oracle7 through an
Oracle8 server without performing a schema conversion to
Oracle8 format. However without the schema conversion Rose
will fail, even though everything else is being done through
Oracle8.
Connecting to the Oracle7 database engine will fail bacuase
the engine version is unsupported. Connecting to the Oracle8
database engine should work if the schema is also in Oracle8
format (permissions permitting).
The client software is not what is making the difference. I
suspect the schema is still in Oracle7 format regardless of the
client software or the database engine being used. Since
Oracle8 will use Oracle7 tables without conversion it is an
easy step to skip.
Ask your DBA to verify that the database being used through
the Oracle8 host has in-fact been converted to Oracle8 format.
>As you suggested, it appears that when the Oracle 7 database
>was migrated to Oracle 8, it was not "converted". Our DBA
>created an entirely new database and I was able to connect and
>reverse engineer it.
(6) WORKING IN A LANGUAGE OTHER THAN ENGLISH?
>Error message:
>"942: Table or View does not exist"
>
>I think it is an Oracle 8 error message.
>There is no error message in the log window.
>
>After analysis two tables are created.
>The first with all columns.
>The second with name, no columns. (does have so in Database)
International language option is set to French.
Reversing with International language option = English works.
The problem was the national language parameter (france) of my
database. It is OK with another database in my office.
defect ID: 142647
(7) DEFECT 133838 - FETCHED COLUMN VALUE WAS TRUNCATED
PROBLEM:
When Reverse engineering an ORACLE8 schema, the following error
message appears:
"ERROR - ORA-01406: Fetched column value was truncated."
ANSWER:
DEFECT 133838 - error during reverse engineering: Fetched
column value was truncated
PRODUCT: Rose 98
VERSION (in help about): 4.5.8163.3
OS: Win 95
The error indicates that that some column data was truncated
while retreiving fetching data. The truncated column problem
has been found to be related to a trigger or package.
WORKAROUND:
Fixed in 98i (still get the oracle error, but now it handles
it more efficiently by bypassing just the problem triggers and
writing out a report to the Rose Error log. )
If still using Rose 98 you can add the updated 98i dll to
Rose 98 to correct these problems. Download the following file
and replace the current copy of
.../Rational Rose 98 Enterprise Edition/Oracle8/or8addin.dll
With this newer version:
ftp://ftp.rational.com/public/rose/rose_cpp/patches
or8addin.dll -> dll for 98i Oracle8 addin
The only other known workaround to this problem is to
drop all packages and triggers from the schema, and rerun the
analyze schema, it should now work.
(8) CRASHES
Insufficient permission can lead to crashes. See
"(2) PERMISSIONS" above.
Also some crash problems were addressed in the 98i
release. If still using Rose 98 you can add the
updated 98i dll to Rose 98 to correct these problems.
Download the following file and replace the current
copy of
.../Rational Rose 98 Enterprise Edition/Oracle8/or8addin.dll
With this newer version:
ftp://ftp.rational.com/public/rose/rose_cpp/patches
or8addin.dll -> dll for 98i Oracle8 addin
If working on Win95 accessing Oracle8 client:
See the following technical note for additional steps.
TITLE: Trouble Shooting Win95 Crash Problems
Note this technical note applys to Win95 NOT NT.
(9) DEFECT 147021 - ORACLE8 ADDIN FAILS WITH ORACLE8I
If you have a version of Rose prior to Rose 2000 you
need to get an updated dll.
Defect ID 147021
Summary* Oracle8 addin fails with Oracle8i
Workaround: Get updated or8addin.dll
The dll that ships with Rose 2000 now works with o8i.
For previous versions of Rose:
or8addin_dll_readme.txt:
or8addin.dll (6.5.9238.0)
DLL that has the fix for oracle 8i version.
Client dll has changed from ora803.dll to client8.dll.
Recompile or8addin.dll so that it links with oci.lib instead of
using LoadLibrary to load 'ora803.DLL'. This is the recommended
way of linking an OCI application.
This update can be found at the following location:
ftp.rational.com/exchange/outgoing/o8_ddl
or8addin.dll.6.5.9238.0
or8addinres.dll
download these files as type binary,
ftp> bin
200 Type set to I.
ftp> get or8addin.dll.6.5.9238.0
ftp> get or8addinres.dll
Save or copy files to
...98i\oracle directory
Shut down Rose.
Rename or8addin.dll in ...98i\oracle to or8addin.dll.old
Rename or8addin.dll.6.5.9238.0 to or8addin.dll
Register this dll:
Open up a command prompt window.
CD to the oracle8 directory. Default location:
c:\program files\rational\rose 98i\oracle8
Run regsrv32:
c:\winnt\system32\regsvr32 or8addin.dll
or
c:\window\system\regsvr32 or8addin.dll
Restart Rose.
------------------------------------------------------------------
III. Quick getting started example
------------------------------------------------------------------
a) Start Rose
b) From Create New Model Wizard choose the followin framework
model: oracle8-datatypes
c) analyze demo that comes with Oracle8:
Tools:Oracl8:Analyze Schema
SCHEMA: demo
DATABASE SERVER: <blank>
USERNAME: scott
PASSWORD: tiger
!NOTE! If the Server is not local, for DATABASE SERVER, do
NOT enter anything except the Server name (i.e. no '\' drive
letters or other information).
If DEMO doesn't exist in Oracle, you may have to create it by
running the create demo script,
Start SQL*Plus
USERNAME: xxxx
PASSWORD: xxxx
(your username and password)
File:Open \orant\Dbs\Demobld.sql
per comments in this file:
-- This script creates the SQL*Plus demonstration tables.
-- It should be STARTed by each user wishing to access the
tables.
Press Return button to get SQL> prompt
File:Run
------------------------------------------------------------------
------------------------------------------------------------------
Patrick Kennedy
Rational Support
On Jun 27, 14:33, Visser, Cathi (EARN) wrote:
> Subject: (ROSE) Connecting to Oracle through Rose Enterprise 2001A
>
> I am attempting to reverse engineer an Oracle schema through Rose Enterprise
> 2001A.
>
> I go to Tools, Oracle8, Analyze Schema. When prompted I enter the connect
> information that I use when connecting to the database through sql*plus or
> SQL Navigator.
>
> I get the following error or8addin Error: Failed to Connect to Database.
>
> I cannot find any information on this error or how Rose is attempting to
> connect to the database. None of the other products (OEM, SQL Navigator,
> sql*plus) need anything changed in them to work (out of the box as long as
> you know the connect string, id and password).
>
> Any hints on how to proceed? Places to look for things to read?
>
> Thanks
>
> Cathi
> [EMAIL PROTECTED]
>
> ************************************************************************
> * Rose Forum is a public venue for ideas and discussions.
> * For technical support, visit http://www.rational.com/support
> *
> * Admin.Subscription Requests: [EMAIL PROTECTED]
> * Archive of messages:
http://www.rational.com/products/rose/usergroups/rose_forum.jtmpl
> * Other Requests: [EMAIL PROTECTED]
> *
> * To unsubscribe from the list, please send email
> *
> * To: [EMAIL PROTECTED]
> * Subject:<BLANK>
> * Body: unsubscribe rose_forum
> *
> *************************************************************************
>-- End of excerpt from Visser, Cathi (EARN)
************************************************************************
* Rose Forum is a public venue for ideas and discussions.
* For technical support, visit http://www.rational.com/support
*
* Admin.Subscription Requests: [EMAIL PROTECTED]
* Archive of messages:
http://www.rational.com/products/rose/usergroups/rose_forum.jtmpl
* Other Requests: [EMAIL PROTECTED]
*
* To unsubscribe from the list, please send email
*
* To: [EMAIL PROTECTED]
* Subject:<BLANK>
* Body: unsubscribe rose_forum
*
*************************************************************************
