TECHNOTE: 7826 & 6451 SUMMARY: Rose Oracle8 Addin ------------------------------------------------------------------ 9. Rose Oracle8 Addin ------------------------------------------------------------------ 9. > ROSE ORACLE8 ADDIN 9.1 >> INTRO NOTES 9.2 >> ANALYZE SCHEMA - Error messages - Solutions - Quick getting started example 9.3 >> PROPERTIES 9.4 >> COMPONENT 9.5 >> FORWARD ORACLE8 GENERATION 9.6 >> DATA TYPE CREATION WIZARD 9.6.1 >>> OBJECT TYPE 9.6.1.1 >>>> ATTRIBUTES 9.6.1.2 >>>> METHODS 9.6.2 >>> RELATIONAL TABLE 9.6.3 >>> RELATIONAL VIEW 9.6.4 >>> OBJECT VIEW 9.6.5 >>> VARRAY 9.6.6 >>> NESTED TABLE 9.6.7 >>> OBJECT TABLE 9.7 >> ADDITIONAL PROPERTIES 9.7.1 >>> SCHEMA GENERATION PROPERTIES FOR AN ORACLE8 CLASS 9.7.2 >>> SCHEMA GENERATION PROPERTIES FOR ROSE ORACLE8 ROLE 9.7.3 >>> SCHEMA GENERATION PROPERTIES FOR ROSE ORACLE8 PROJECTS 9.8 >> EDIT FOREIGN KEYS 9.9 >> ORDERING WIZARD 9.10 >> SYNTAX CHECKER 9.11 >> SCHEMA GENERATION 9.12 >> REPORTS 9.13 >> IMPORT ORACLE8 DATA TYPES 9.14 >> MORE INFORMATION ================================================================== 9. > ROSE ORACLE8 ADDIN ================================================================== 9.1 >> INTRO NOTES 1) What are the end user product requirements? The end user product requirements are: Microsoft Windows NT 4.0 or Windows 95 Access to Oracle8 via the Net8 client software Memory: 24 MB minimum, 64+ recommended. Hard Disk Space: 80 MB (Rose 98 Enterprise) 95 MB (Rose 98i Enterprise) Pentium-class processor recommended. For Design and Code generation to script, no copy of Oracle needs to be installed. For Reverse Engineering Oracle8 tables, Oracle8 client software needs to be installed. 2) OR8ADDIN.DLL version shipping with Rose 98 version 8.0.2.9 version shipping with 98i version: 6.0.8324.0 98i update (beta) version: 6.0.9075.0 3) All existing Rose relational database schema generation support is included in this product. This will aid in the migration from Oracle 7 to Oracle 8, and is used to support the generation of relational tables in Oracle 8. 4) Not UML compilant. " Rose Oracle8 enables you to view and manipulate this model using the UML notation for object-oriented analysis and design. " Not Quite. Along with other missing UML features the major notation shortcomming is cardinality. UML uses cardinality\multiplicity to express a foreign key. _________ __________ | a |1 1..n| b | |---------|-------|----------| | name_a | | name_b | --------- ---------- If one-to-many, then it will generate a foreign key embedded in table B. Rose (per DDL generation) uses Navigability: _________ __________ | a | | b | |---------|<------|----------| | name_a | | name_b | --------- ---------- If navibability is set for the direction of the association, a relation is created via a Foreign key (regardless of multiplicity). 5) !!! CaSe SeNsItIvE CaSe SeNsItIvE CaSe SeNsItIvE !!! ex 1: DEFECT 146844 Generate Schema, Syntax checker fails because of case sensitivity Through user interface (not wizard) create a attribute 'name_a' in lower case in a type. Latter create a reference to 'name_a' in Data Wizard for example, view. Data Wizard outputs in all caps. Code gen fails because Syntax checker does not reconize the following names being equal: 'NAME_A' <> 'name_a' ex 2: Make a component called 'test' Assign o8 schema. Use Data Wiz, new component 'TEST' is created. You will run into these types of problems if you switch between writting your own and using the wizard. CURRENT WORKAROUND: USE ALL CAPS. Scheduled to be fixed in uncomming release. ------------------------------------------------------------------ 9.2 >> ANALYZE SCHEMA Reverse engineering is the process of analyzing an Oracle8 schema and creating a Rose model that captures the elements and structure of the schema. TECH NOTE: 6451 SUMMARY: Oracle8 Analyze Schema, errors and solutions Tools:Oracl8:Analyze Schema I. Error messages II. Solutions III. Quick getting started example ------------------------------------------------------------------ I. Error messages ------------------------------------------------------------------ ------------------------------------------------------------------ Oracle 8 AddIn The schema '' 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: 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. - Is Oracle in your path? \ORANT\BIN (NT default path) \ORAWIN95\BIN \ORAWIN98\BIN If Oracle files are not in the path error this can occur. 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 Defect ID 147021 Summary* Oracle8 addin fails with Oracle8i Workaround: Get updated or8addin.dll 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 !BETA copy, still undergoing testing! Tested with 8 and 8i but more testing is required on this version before offical release! 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: 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 ------------------------------------------------------------------ 9.3 >> PROPERTIES Rational Rose o8 suplies a default propert file (oracle8.pty) with o8 specific settings. The file is automatically attached and initalized when you start Rose o8. Schema Generation Properties: Schema generation properties control how model elements are created and generated for forward engineering. Various model elements have their own unique sets of schema generation properties. For example, class properties control aspects of schema generation for class elements that have stereotypes such as object type, relational table, object view, etc. Rose/ORACLE8 provides the following schema generation properties: - Project (Model) properties - Class properties - Operation properties - Attribute properties - Role properties - Module Specification Properties In addition, see ORACLE8 to Rose Mapping Quick Reference for how Rose elements are mapped to ORACLE8 elements. ------------------------------------------------------------------ 9.4 >> COMPONENT Component View Component Stereotype=Schema Each ObjectType, View or Table can be assigned to one or more database schema's where a schema is represented as a Component within Rose with the stereotype of Schema. Component Name: schema_name Language: Oracle8 Stereotype: Schema Realizes tab, list of assigned objects + SCHEMA GENERATION PROPERTIES FOR ORACLE8 COMPONENT SPECFICATIONS Property Type Description -------- ---- ----------- IsSchema Boolean Identifies if the component is a schema. ------------------------------------------------------------------ 9.5 >> FORWARD ORACLE8 GENERATION + EXAMPLE (starting from Rose i.e. not working with Reverse Engineered schema) (1) Load Oracle8 Framework. (Add-in Manager, has FrameWork Wizard checked) From Create New Model dialog, New Tab select oracel8-datatypes.mdl. -or- Tools:Oracle8:Import Oracle8 Data Types (2) Go to Component View, Main diagram Add New Component Give this Component the same name as your schema Go into Component Specification General Tab Set Language: Oracle8 hit APPLY button Set Stereotype: Schema (3) Add class in Logical View diagram (4) Now either drag class in browser to the Component you just made -or- Go back into Component specification, Realizes tab, and right click on class and choose assign. A red check mark should now appear on top of class icon in this list. (5) Go back to class open up Class Specification and set sterotype, Sterotype: RelationalTable Go to Attribute tab, right click and choose insert Go into Class Attribute Specification for attribute On General Tab Set Type to desired choice, i.e. CHAR, DATE, etc. Go to Oracle8 Tab Set properties for this attribute. Note most of these are optional but if using a attribute type that needs a length (such as char) you need to set this here. + PRIMARY KEY AND INDEXES Unlike DDL generation you cannot just set the PrimaryKey property to TRUE to generate a primary key. To create a primary key for O8 generation: Create an attribute that will be the primary key: Name: name_id Type: number Stereotype: Intial value: Create an attribute that will be the constraint name for the primary key, give it the name of the column for the primary key. Name: name_idx (primary key or index constraint name) Type: number Stereotype: Intial value: name_id (column designated as primary or index) Go to this attributes specification and set BOTH IsIndex and IsPrimaryKey to True. Tools:Oracle8:Schema Generation CREATE TABLE TEST.a( name_id NUMBER(2,6), CONSTRAINT name_idx PRIMARY KEY (name_id)): Go to this attributes specification and set only IsIndex to True. CREATE TABLE TEST.a( name_id NUMBER(2,6), a_ID NUMBER(5,0), PRIMARY KEY (a_ID)); CREATE INDEX TEST.name_idx ON TEST.a(name_id); ------------------------------------------------------------------ 9.6 >> DATA TYPE CREATION WIZARD Tools:Oracle8:Data Type Creation Wizard The wizard steps you through the process of creating Oracle8 constructs in a Rose model, including Object Types, Object Views, Relational Tables, Relational Views, VARRAYs, Nested Tables, and Object Tables. Always creates objects in Logical View Main. You need to move and relocate to other locations. Rational Rose uses classes, class stereotypes, and the relationships between classes to capture and model schema design. there are class stereotype for: Rose Oracle Description, Construct Construct Stereotype = ------------------------------------------- Class Object Type ObjectType Class RelationalTable RelationalTable Class ObjectTable ObjectTable Class ObjectView ObjectView Class VARRAY VARRAY Class NestedTable NestedTable Class RelationalView RelationalView Class Sequence Sequence ------------------------------------------------------------------ 9.6.1 >>> OBJECT TYPE Object Type Class Stereotype=ObjectType Rose models an Object Type as a class with an Object Type stereotype. The Object Type’s attributes are modeled as class attributes; its methods are modeled as operations. An object type is a user-defined datatype that is a single structured data unit that can be queried, updated, and stored in an Oracle8 database. The object types you create can be used the same way you use built-in relational datatypes. + EXAMPLE _________________ | <> | | OBJECT_NAME | |-----------------| | name1: number | |-----------------| | op1() | ------------------ CREATE OR REPLACE TYPE TEST.object_name AS OBJECT ( name1 NUMBER, MEMBER FUNCTION op1 (PARA_NAME IN NUMBER) RETURN NUMBER); CREATE OR REPLACE TYPE BODY TEST.object_name IS MEMBER FUNCTION op1 (PARA_NAME IN NUMBER) RETURN NUMBER IS NUMBER aNUMBER BEGIN return aNUMBER END END; 9.6.1 >>>> ATTRIBUTES Attributes are modeled in Rose as Attributes. + CREATE To create a new attribute, assign a name to the attribute and select the attribute’s datatype. If you select Scalar, you will need to select a built-in type from the list of types, as well as the appropriate Precision, Scale, and Length values. If the attribute has a user-defined datatype, select either Object Type, Varray, or Nested Table. For Type, select from the list of existing user-defined types or use New to create a new one. Use the Reference checkbox to create a REF to another datatype. (The attribute will refer to another datatype for its value.) + RULES FOR CREATING AND USING attributes can be one of the following types: - Scalar, using built in types such as CHAR, NUMBER, VARCHAR, DATE, etc. - Another object type. For example, an attribute for the CUSTOMER object type may be a PURCHASE_ORDER object type. This association can be made by creating a reference (REF) to PURCHASE_ORDER or it can be by value. - A collection type--either a VARRAY or a nested table. + NESTED OBJECT TYPES Rose models an attribute whose datatype is another object type (a nested object type) as an association between the two object types. In this case, the association is by value. ---------------- ---------------- | <> | | <> | | OBJECT_1 |+NAME_TYPE_O1 | OBJECT_2 | |----------------|<---------------|----------------| | NAME1: number | | NAME_TYPE_N | | NAME2: number | |----------------| |----------------| | | | | ---------------- ---------------- association CREATE OR REPLACE TYPE OBJECT_1 AS OBJECT ( NAME1 NUMBER, NAME2 NUMBER); CREATE OR REPLACE TYPE OBJECT_2 AS OBJECT ( NAME_TYPE_O1 OBJECT_1, NAME_TYPE_N NUMBER); If you create a REF (a pointer or reference similar to a foreign key in a relational table), Rose Oracle8 models it as an aggregate association. In this case, OBJECT_2 is the Client (Role B in the association’s Rose specification) and OBJECT_1 is the Supplier (Role A in the association’s Rose specification): ---------------- ---------------- | <> | | <> | | OBJECT_1 |+NAME_TYPE_O1_BYREF | OBJECT_2 | |----------------|<-----------------<>|----------------| | NAME1: number | | NAME_TYPE_N | | NAME2: number | |----------------| |----------------| | | | | ---------------- ---------------- aggregation CREATE OR REPLACE TYPE OBJECT_1 AS OBJECT ( NAME1 NUMBER, NAME2 NUMBER); CREATE OR REPLACE TYPE 0BJECT_3 AS OBJECT ( NAME_TYPE_O1_BYREF REF OBJECT_1, NAME_TYPE_N NUMBER(2,6)); In Data Wizard use the Reference checkbox to create a REF to another datatype. + SCHEMA GENERATION PROPERTIES FOR ORACLE8 CLASS ATTRIBUTES The following table describes the schema generation properties for Oracle8 attributes: Property Type -------- ---- Description ----------- OrderNumber String Column order for tables. IsUnique Boolean If set to False (the default), the attribute is not required to be unique. NullsAllowed Boolean If set to True (the default) attribute is required to have a value. A value is required for NOT NULL settings. Length string Used for scalar datatypes such as CHAR, VARCHAR, etc. Precision string Used by NUMBER scalar datatype. Scale string Used by NUMBER scalar datatype. IsIndex Boolean Identifies whether the attribute is part of an index. Default is FALSE. IsPrimaryKey Boolean Marks the attribute as the Primary Key or part of the Primary Key. The attribute must be a scalar type. If more than one Primary Key attribute is identified, a concatenated primary key is generated. Default is False. CompositeUnique Boolean Identifies if attribute is part of a composite. The default is False. CheckConstraint string Used to indicate a CHECK constraint. 9.6.1 >>>> METHODS + TYPES Functions and procedures Stored Functions: methods with a return type. Procedures: methods without a return type. Triggers - uses implementaton setting for Export Control to distingusigh them from other methods. Map or Order Comparison methods. Constructor. The type of method is captured by it's operation property, MethodKind. + STORED PROCEDURES Basically comes down to you should be able to, but due to a defect or two you can't. It was in the orginal spec for the product (hence found it's way into help) but was MIA on release. ------------- DEFECT 146314 oracle 8: create stored procedure - cannot resolve class I have a class stereotyped as ObjectType, created an operation with a MethodKind of Procedure. When I go to Schema Generation, it gave no syntax error, but when I click on Generate, it says cannot resolve class. It appears that the schema generator expects a return type which is left blank for procedures when you create the object method in the obj creation wizard. Functions (with return types) work fine. ------------- Fixed in upcomming service pack 1. ------------- DEFECT 146942 Oracle8: Support for stored procedures? Is there any support for stored procedures (not member procedures)? 1. Reverse Engineering a schema with a stored procedure brings nothing into Rose. 2. In old Rose 4.0 o8 spec it states: > Operation in a Class Utility = Stored Procedure All the global stored procedures for a schema will be represented as operations of class utility that is named the same as the schema name. < In part this doesn't work due to Defect ID 146314. However once you create a class with TYPE set to ClassUtility and name that class the same name as the schema, is a stored or member procedure generated? ------------- Still an open defect. + CREATE To define operations for the object type, enter an operation name and select a type. Normal creates a function or a procedure. (A procedure will not have a Return Type.) For comparison methods, choose between Map and Order. Choose the appropriate Return Type. Optionally, specify arguments by entering a Parameter Name, selecting a Direction and a Type, then click the Parameter Add. This clears the Parameter fields enabling you to define another set if needed. + RULES FOR CREATING AND USING - An object type can have one MAP method or one ORDER method, but not both - An object type cannot have an attribute with datatype of ROWID, LONG, LONG RAW, NCLOB, NCHAR, or NCHAR VARING. - You must use PRAGMAs to indicate the access level of member functions. - You cannot define an INDEX on an object type or an object types's attribute unless it has a scalar datatype. + SCHEMA GENERATION PROPERTIES FOR ORACLE8 OPERATIONS The following table describes the schema generation property for Oracle8 operations: Property Type Description -------- ---- ----------- MethodKind Enumerated Can be Function, Procedure*, Operator, Constructor, Destructor, Trigger, MapMethod, OrderMethod, Calculated Column Order Number if Calculated View Column. Initial value is Function. *MEMBER procedure not STORED procedure OverloadID String Overloading is Supported for Functions. OrderNumber String The column order, if a calculated View column. IsReadNoDataState Boolean Initial setting is False. IsReadNoProcessState Boolean Initial setting is False. IsWriteNoDataState Boolean Initial setting is False. IsWriteNoProcessState Boolean Initial setting is False. IsSelfish Boolean Initial setting is False. TriggerType Enumerated AFTER, BEFORE or INSTEAD OF TriggerEvent Enumerated Can be INSERT, UPDATE, DELETE, INSERT OR UPDATE, INSERT OR DELETE, UPDATE OR DELETE, INSERT OR UPDATE OR DELETE. TriggerText String Part of trigger definition. TriggerRefererencingNames String Used for the trigger Referencing option. TriggerForEach Enumerated Can be ROW, STATEMENT TriggerWhenClause String Parameter for a trigger. + VALID METHODKIND PROPERTIES FOR VARIOUS CLASSES Class OBJECT TYPE: MethodKind property must be 'Function', 'Procedure', 'MapMethod', or 'OrderMethod'. Class RELATIONAL TABLE: Only operations with MethodKind property set to 'Trigger' are valid for this type class. Class RELATIONAL VIEW: Only operations with MethodKind property set to 'Trigger' or 'Calculated' are valid for this type class. Class OBJECT VIEW: Only operations with MethodKind property set to 'Trigger' or 'Calculated' are valid for this type class. Class OBJECT TABLE: Only operations with MethodKind property set to 'Trigger' are valid for this type class. Class VARRAY: An operation is only meaningful for a class stereotyped as <>. Operation will be ignored. Class NESTED TABLE_NAME: An operation is only meaningful for a class stereotyped as <>. Operation will be ignored. + EXAMPLE >MAPMETHOD> CREATE OR REPLACE TYPE OBJECTTYPENAME AS OBJECT ( ATTRIBUTENAME NUMBER, MAP MEMBER FUNCTION OPERATIONNAME RETURN NUMBER); CREATE OR REPLACE TYPE BODY OBJECTTYPENAME IS MAP MEMBER FUNCTION OPERATIONNAME RETURN NUMBER IS NUMBER aNUMBER BEGIN return aNUMBER END END; >ORDERMETHOD> CREATE OR REPLACE TYPE OBJECTTYPENAME AS OBJECT ( ATTRIBUTENAME NUMBER, ORDER MEMBER FUNCTION OPERATIONNAME RETURN NUMBER); CREATE OR REPLACE TYPE BODY OBJECTTYPENAME IS ORDER MEMBER FUNCTION OPERATIONNAME RETURN NUMBER IS NUMBER aNUMBER BEGIN return aNUMBER END END; >FUNCTION> CREATE OR REPLACE TYPE OBJECTTYPENAME AS OBJECT ( ATTRIBUTENAME NUMBER, MEMBER FUNCTION OPERATIONNAME RETURN NUMBER); CREATE OR REPLACE TYPE BODY OBJECTTYPENAME IS MEMBER FUNCTION OPERATIONNAME RETURN NUMBER IS NUMBER aNUMBER BEGIN return aNUMBER END END; >PROCEDURE> CREATE OR REPLACE TYPE OBJECTTYPENAME AS OBJECT ( ATTRIBUTENAME NUMBER, MEMBER PROCEDURE OPERATIONNAME); CREATE OR REPLACE TYPE BODY OBJECTTYPENAME IS MEMBER PROCEDURE OPERATIONNAME IS BEGIN END END; >TRIGGER> CREATE TABLE NEWCLASS2 ( NEWCLASS2_ID NUMBER(5,0), PRIMARY KEY (NEWCLASS2_ID)); CREATE OR REPLACE TRIGGER opname AFTER INSERT ON NEWCLASS2 FOR EACH ROW; >CALCULATED> CREATE OR REPLACE VIEW RELATIONAL_VIEW (opname,ATTRIBUTE_NAME_RV) AS SELECT opname,RELATIONAL_TABLE_NAME.ATTRIBUTE_NAME_RT FROM RELATIONAL_TABLE_NAME WHERE (...WHERE CLAUSE...); + OPERATION PROPERTIES FOR PRAGMA IsReadNoDataState, IsReadNoProcessState, IsWriteNoDataState, IsWriteNoProcessState, IsSelfish Pragma enforces the purity level of functions. To execute a SQL statement that calls a member function, Oracle must know the purity level of the function, that is, the extent to which the function is free of side effects. The pragma tells the PL/SQL compiler to deny the member function read/write access to database tables, packaged variables, or both. In the object type specification, you code the pragma somewhere after the method to which it applies. The syntax follows: PRAGMA RESTRICT_REFERENCES ({DEFAULT | method_name}, {RNDS | WNDS | RNPS | WNPS}[, {RNDS | WNDS | RNPS | WNPS}]...); + EXAMPLE For example, the following pragma constrains map method convert to read no database state (RNDS), write no database state (WNDS), read no package state (RNPS), and write no package state (WNPS): CREATE TYPE Rational AS OBJECT ( num INTEGER, den INTEGER, MAP MEMBER FUNCTION convert RETURN REAL, ... PRAGMA RESTRICT_REFERENCES (convert, RNDS,WNDS,RNPS,WNPS) ); + DEFECT - METHODS DEFECT 146318 - oracle8 error: method fails to compile Type Body created with complilation errors The code generated from Rose does not compile. To recreate this, file:new Tools:Oracle8:Data Creation Wizard select object type Give it a name for object and schema Create a attribute (optional), Give operation a name, Operation type: Normal Return type: Number Add operation, Next Finished. SQL> CREATE OR REPLACE TYPE OBJ_NAME AS OBJECT ( 2 ATTRIBUTE_NAME NUMBER, 3 MEMBER FUNCTION OP_NAME RETURN NUMBER); 4 / Type created. SQL> CREATE OR REPLACE TYPE OBJ_NAME AS OBJECT ( 2 MEMBER FUNCTION OPNAME RETURN NUMBER); 3 4 CREATE OR REPLACE TYPE BODY OBJ_NAME IS 5 MEMBER FUNCTION OPNAME RETURN NUMBER IS 6 NUMBER aNUMBER 7 BEGIN 8 return aNUMBER 9 END 10 END; 11 / Warning: Type Body created with compilation errors. SQL> SHOW ERR; Errors for TYPE BODY OBJ1: LINE/COL ERROR -------- --------------------------------------------------------- 4/19 PLS-00103: Encountered the symbol "ANUMBER" when expecting one of the following: := . ( @ % ; You can run this code, but it will get made with COMPILATION errors. Oracle allows incomplete methods to be created. In order to get this to compile here, I had to flip the variable and type, NUMBER aNUMBER to aNUMBER NUMBER Add a semicolon to the end of this line, aNUMBER NUMBER; Add a semicolon to the end of this line, return aNUMBER; And put a extra semicolon after the END (second to bottom) END to END; SQL> CREATE OR REPLACE TYPE OBJ_NAME AS OBJECT ( 2 ATTRIBUTE_NAME NUMBER, 3 MEMBER FUNCTION OP_NAME RETURN NUMBER); 4 / Type created. SQL> CREATE OR REPLACE TYPE BODY OBJ_NAME IS 2 MEMBER FUNCTION OP_NAME RETURN NUMBER IS 3 aNUMBER NUMBER; 4 BEGIN 5 return aNUMBER; 6 END; 7 END; 8 / Type body created. ------------------------------------------------------------------ 9.6.2 >>> RELATIONAL TABLE Relational Table Class Stereotype=Relational Table Rose/Oracle models table columns as attributes of the RelationalTable class, stereotype. Oracle8 relational tables can also support using an object type, VARRAY, or nested table as the datatype for a column. Nested Datatypes for Columns Rose models a column whose datatype is a user-defined datatype (i.e., an object type, VARRAY, or nested table) as an association between the table and the datatype. + RULES FOR CREATING AND USING All Oracle8 rules for creating and using relational tables also apply in Rose/Oracle8. + EXAMPLE --------------------- | <> | | a | |---------------------| | name_a | --------------------- name_a: Type: Number PrimaryKey set to False Oracle: CREATE TABLE TEST.a( name_a NUMBER(2,6), A_ID NUMBER(5,0), PRIMARY KEY(a_ID)); ------------------------------------------------------------------ 9.6.3 >>> RELATIONAL VIEW Relational View Class Stereotype=Relational View A relational view is a standard Oracle construct for creating a virtual table based on one or more existing relational tables. View columns are modeled as class attributes. Rose uses dependencies to model relationships between the view and its underlying tables. The attribute name is equal to the view column name or the view column alias, if an alias is provided. Where an alias is provided, the initial value of the attribute is set to the fully qualified column name. In Data Creation Wizard you can add filtering criteria to the view to determine when to include data. To do this, use the Where button. The Where button opens a Where Clause dialog that lets you enter SQL statements that are carried forward into your Oracle8 schema when you perform schema generation. Outside of the Wizard, Go into, Class specification for view_name, oracle8 property set, and set WhereClause = ...WHERE CONDITIION... + RULES FOR CREATING AND USING All Oracle8 rules for creating and using relational tables also apply in Rose/Oracle8 + EXAMPLE --------------------- ----------------------------- | <> | | <> | | a | | view_name | |---------------------|<-----|-----------------------------| | NAME_A | | NAME_ID: NUMBER = a.NAME_ID | --------------------- ----------------------------- Dependency, instantiates relation between. CREATE OR REPLACE VIEW TEST.RELATIONAL_VIEW (NAME_ID) AS SELECT a.NAME_ID FROM TEST.a WHERE ...WHERE CONDITION... ; ------------------------------------------------------------------ 9.6.4 >>> OBJECT VIEW Object View Class Stereotype= Object View View columns are modeled as attributes; methods are modeled as operations. The relationships between the Object View and its underlying object type(s) and relational table(s) are modeled as dependencies. + CREATE To create an object view (whether you're using Rose/Oracle8 or generating the SQL yourself), you first need to create an object type that encapsulates the data from one or more relational tables. This object type serves as the bridge between the object view and relational data. You also need to designate ONE OR MORE OF THE OBJECT VIEW’S ATTRIBUTES AS AN OBJECT IDENTIFIER that the view will use to enable REF’s to point to objects (rows) in the view. (An object identifier can be a composite of more than one attribute.) The Object View’s attribute(s) that you selected as the Object Identifier is captured as a model property, OID, under Class Specification, Oracle8 property tab. Creating an Object View 1. Optional. From the Rose model, consider selecting (highlighting) any other existing object types or tables whose attributes or columns you will be using to create the view. 2. Call the Data Type Creation Wizard by selecting Tools - Oracle8 - Data Type Creation Wizard from the Rose menu. 3. Select Object View from the list of schema object icons. Note that if you selected object types or tables before you started the wizard, they appear in the Selected Items list. Click Next. 4. Assign a name to the new object view, identify the schema where the view will be created, and select the diagram where the view will appear. Click Next. 5. Select the Object Type for the object view from the list of available types. (If you selected an object type before you started the wizard, it appears as the type.) Or, create a new object type on the fly by selecting New Object Type. (The wizard will step you through the creation process.) Click Next to continue. 6. Use the Object View Map dialog to map attributes from an object type to attributes from one or more relational tables or other object view(s). Use the Map To button to select the object view(s) and/or relational tables whose attributes you will be mapping to. (This populates the selection box on the right side of the dialog.) The attributes of the object type should already be displayed in the selection box on the left. To perform the mapping, click once on an attribute from the left, then click once on the corresponding attribute on the right. Press the Map button. This places your selection in the View Map below the two selection boxes. Note that you must map every attribute displayed on the left. (Shortcut: Instead of using the Map button, select one attribute and double-click on the second attribute. It doesn’t matter which attribute is selected first.) To add filtering criteria that will test data for inclusion in the view, use the Where button. This displays an area for you to enter SQL statements that are included when you generate the DDL for the schema. Note that once in the View Map, you can edit the attribute by double-clicking on the attribute name or by clicking once with the right mouse button. This displays an edit dialog for creating expressions associated with the attribute/column. When you’ve finished mapping the view’s attributes, click Next. 7. Select one or more attributes that will serve as an object-identifier for the view. Click Finish. The new object view appears on the diagram you specified. + RULES FOR CREATING AND USING All Oracle8 rules for creating and using object views also apply in Rose Oracle8. In addition, note that you cannot specify an alias when creating an object view. + EXAMPLE ------------------ | <> | | OBJECT_NAME | |------------------| | NAME_OT: number |<--- |------------------| \ | | \ <> ------------------ \ \ ---------------------- ---------------------------------- | <> | | <> | | RT_NAME | | OBJECT_VIEW_NAME | |----------------------|<-----|----------------------------------| | NAME_RT | |NAME_OV: NUMBER = RT_NAME.NAME_RT | ---------------------- ---------------------------------- Dependency relations between classes. CREATE OR REPLACE TYPE OBJECT_NAME AS OBJECT ( NAME_OT NUMBER); CREATE TABLE RELATIONAL_NAME ( NAME_RT NUMBER, CONSTRAINT INDEX_NAME PRIMARY KEY (NAME_RT)); CREATE OR REPLACE VIEW OBJECT_VIEW_NAME OF OBJECT_NAME WITH OBJECT OID (NAME_OT) AS SELECT RELATIONAL_NAME.NAME_RT FROM RELATIONAL_TABLE_NAME WHERE (...WHERE CLAUSE...); ------------------------------------------------------------------ 9.6.5 >>> VARRAY VARRAY Class Stereotype= VARRAY Rose models a VARRAY as a class with a VARRAY stereotype. The relationship between the VARRAY and an underlying type is modeled as a dependency, with the "cardinality to" role corresponding to the size of the VARRAY. + RULES FOR CREATING AND USING A VARRAY is a datatype you create to define an ordered collection of data elements. All of the elements in a VARRAY must have the same datatype. The size of a VARRAY determines how many elements it can contain. Since a VARRAY defines an ordered collection, you can use it where the order of the elements is significant. (Each element in a VARRAY has an index that is based on the element’s position in the array.) This is in contrast to a nested table which is an unordered collection type. VARRAYs are stored in a single column; they allow you to retrieve a collection as a whole. You can use a VARRAY to define the datatype for: - An attribute in an object type - A column in a relational table Since VARRAYs can be used to define the datatype of a column in a relational table, you are able to introduce this object construct into your existing relational schema. All Oracle8 rules for creating VARRAYs also apply in Rose Oracle8. Please note these specific rules: - A VARRAY cannot have a datatype (directly or indirectly by nested object types) of BLOB, CLOB, NCLOB, NCHAR, or NCHAR VARYING. Note that this rule does not apply to object types that are included by reference. - A VARRAY cannot have a nested table as a datatype, either directly or indirectly. - A VARRAY does not support an index. + CREATE Creating a VARRAY with the Data Wizard a. Optional. From the Rose model, select (highlight) the object type for the VARRAY you are creating if the type already exists. b. Call the Data Type Creation Wizard by selecting Tools - Oracle8 - Data Type Creation Wizard from the Rose menu. Note that if you selected an object type before you started the wizard, it appears in the Selected Items list. c. Select VARRAY from the list of schema object icons. Click Next. d. Assign a name to the VARRAY, identify the schema where the VARRAY will be created, and select the diagram where the VARRAY will appear. Click Next. e. Select the datatype for the VARRAY. The datatype can be scalar (built-in, such as CHAR, VARCHAR, etc.) or it can be a user-defined object type. f. From the drop down list, select the appropriate datatype. If you selected an object type before you started the wizard, it appears as the type. You can also create a new object type on the fly by selecting New Object Type. If you select Scalar, you may need to provide values for Precision and Length. To specify the size of the VARRAY, enter a value in the Cardinality field. g. When you select Finish, the new VARRAY you created appears. + EXAMPLE --------------- | <> | | varray_name | --------------- CREATE TYPE TEST.varray_name AS VARRAY(2) OF CHAR(10); ------------------------------------------------------------------ 9.6.6 >>> NESTED TABLE Nested Table Class Stereotype=Nested Table A nested table is a table of unordered data elements that is embedded as a column in another table. You can perform the same operations on a nested table that you can perform on other tables. + CREATE Creating a Nested Table 1. (Optional). From the Rose model, select (highlight) the object type for the nested table you are creating if the type already exists. 2. Call the Data Type Creation Wizard by selecting Tools - Oracle8 - Data Type Creation Wizard from the Rose menu. Note that if you selected an object type before you started the wizard, it appears in the Selected Items list of the opening dialog. 3. Select Nested Table from the list of schema object icons. Click Next. 4. Assign a name to the nested table, identify the schema where the table will be created, and select the diagram where the table will appear. Click Next. 5. Select the nested table’s datatype, either an Object Type or Scalar. Use the drop down list to select the particular type you want to assign. (If you selected an object type before you started the wizard, it appears as the type.) Or, create a new object type on the fly by selecting New Object Type. + RULES FOR CREATING AND USING All Oracle8 rules for creating nested tables also apply in Rose Oracle8 including the rule that all of the elements in a nested table must have the same datatype. In addition, note that a nested table cannot be based on a scalar type of NCLOB, NCHAR, or NCHAR VARYING. + EXAMPLE When you create a nested table in Rose Oracle8, IT IS BASED ON AN OBJECT TYPE. Rose models the association between the nested table and its underlying object type as a dependency. For example: ----------------- ----------------- | <> | | <> | | OBJECT_NAME | | NESTEDTABLENAME | |-----------------| |-----------------| | NAME1: number |<---------|-----------------| |-----------------| | | | op1() | ----------------- ----------------- CREATE TYPE TEST.NESTEDTABLENAME AS TABLE OF TEST.OBJECT_NAME; When you use a nested table as the datatype for an object type attribute or a column in a relational table, Rose models the nested table attribute as an association between the nested table schema object and the object type or relational table. For example: ----------------- | <> | | NESTEDTABLENAME | +attributename ---|-----------------|<--------------------------- |-----------------| | | ----------------- -------------------- | <> | | Relationaltable2 | --------|--------------------| |--------------------| | | -------------------- CREATE TABLE TEST.RELATIONTABLE2 ( ATTRIBUTENAME TEST.NESTEDTABLENAME, RELATIONTABLE2_ID NUMBER(5,0), PRIMARY KEY (RELATIONTABLE2_ID)) NESTED TABLE ATTRIBUTENAME STORE AS ATTRIBUTENAME_NTS; ------------------------------------------------------------------ 9.6.7 >>> OBJECT TABLE Object Table Class Stereotype= Object Table Since object tables are built from underlying object types, Rose Oracle8 models this relationship as a Dependency. An object table enables you to place an object type in a relational construct. The columns in the object table correspond to the attributes in the underlying object type. Each row in the object table contains an object. By packaging an object type in an object table, you can access the objects using standard relational techniques. Note that another alternative for creating object packages that can be used in a relational world is to create Object Views. + CREATE Creating an Object Table 1. (Optional). From the Rose model, consider selecting (highlighting) the underlying object type for the object table you are creating if the type already exists. 2. Call the Data Type Creation Wizard by selecting Tools - Oracle8 - Data Type Creation Wizard from the Rose menu. 3. Select Object Table from the list of schema object icons. Note that if you selected an object type before you started the wizard, it appears in the Selected Items list in the opening dialog. Select Next. 4. Assign a name to the new object table, identify the schema where the table will be created, and select the diagram where the table will appear. Select Next. 5. Select the Object Type for the object table from the list of available types. (If you selected an object type before you started the wizard, it appears as the type.) Or, create a new object type on the fly by selecting New Object Type. The wizard will step you through the creation process. + RULES FOR CREATING AND USING All Oracle8 rules for creating and using object tables also apply in Rose Oracle8. + EXAMPLE ----------------- ----------------- | <> | | <> | | OBJECT_NAME | | OBJECTTABLENAME | |-----------------| |-----------------| | NAME1: number |<---------|-----------------| |-----------------| | | | op1() | ----------------- ----------------- CREATE TABLE TEST.OBJECTTABLENAME OF TEST.object_name; ------------------------------------------------------------------ 9.7 >> ADDITIONAL PROPERTIES 9.7.1 >>> SCHEMA GENERATION PROPERTIES FOR AN ORACLE8 CLASS The following table describes the schema generation property for an Oracle8 class: Property Type Description -------- ---- ----------- OID string The Object ID of the Object View. WhereClause string Used by object views and relational views. CheckConstraint string Used to indicate a CHECK constraint. CollectionTypeLength string Used by Varrays of Scalar type. CollectionTypePrecision string Used by Varrays of Scalar type CollectionTypeScale string Used by Varrays of Scalar type 9.7.2 >>> SCHEMA GENERATION PROPERTIES FOR ROSE ORACLE8 ROLE: The following is the schema generation property for an Oracle8 role: Property Type Description -------- ---- ----------- OrderNumber string Order of the table column. 9.7.3 >>> SCHEMA GENERATION PROPERTIES FOR ROSE ORACLE8 PROJECTS Model (or design-level) properties affect the entire Rose model. The following table describes the schema generation properties, as well as any defined default values, for Rose/ORACLE8 models. Property Type -------- ---- Description ----------- DDLScriptFilename string The name of the default DDL script file that is created when you generate a schema from a Rose model. DropClause Boolean If true, generates a DROP statement for each Oracle8 entity. Default is False PrimaryKeyColumnName string Suffix for generated primary keys. Initial value is _ID. PrimaryKeyColumnType string Default type and length for primary keys. The initial value is NUMBER(5,0) SchemaNamePrefix string An optional naming standard that is added to the beginning of a component name for each generated schema. The initial setting is blank. The recommended value is S_ SchemaNameSuffix string An optional naming standard that is appended to the end of the component name for each generated schema. The initial setting is blank. The recommended value is _S. TableNamePrefix string An optional naming standard that is added to the beginning of the class name for each generated table. The initial setting is blank. The recommended value is T_. TableNameSuffix string An optional naming standard that is appended to the end of the class name for each generated table. The initial setting is blank. The recommended value is _T. TypeNamePrefix string An optional naming standard that is added to the beginning of the class name for each generated object table The initial setting is blank. The recommended value is O_. TypeNameSuffix string An optional naming standard that is appended to the end of the class name for each generated object table. The initial setting is blank. The recommended value is _O. ViewNamePrefix string An optional naming standard that is added to the beginning of the class name for all generated object and relational views. The initial setting is blank. The recommended value is V_. ViewNameSuffix string An optional naming standard that is appended to the end of the class name for all generated object and relational views. The initial setting is blank. The recommended value is _V. VarrayNamePrefix string An optional naming standard that is added to the beginning of the class name for each generated Varray. The initial setting is blank. The recommended value is VA_. VarrayNameSuffix string An optional naming standard that is appended to the end of the class name for each generated Varray. The initial setting is blank. The recommended value is _VA. NestedTableNamePrefix string An optional naming standard that is added to the beginning of the class name for each generated nested table The initial setting is blank. The recommended value is NT_. NestedTableNameSuffixq string An optional naming standard that is appended to the end of the class name for each generated nested table. The initial setting is blank. The recommended value is _NT. ObjectTableNamePrefix string An optional naming standard that is added to the beginning of the class name for each generated object table. The initial setting is blank. The recommended value is OT_. ObjectTableNameSuffix string An optional naming standard that is appended to the end of the class name for each generated object table. The initial setting is blank. The recommended value is _OT. IndexNamePrefix string An optional naming standard that is added to the beginning of the class name for each generated index. The initial setting is blank. The recommended value is I_. AttributeNamePrefix string An optional naming standard that is added to the beginning of the attribute name for each generated attribute within an object type. The initial setting is blank. The recommended value is A_. AttributeNameSuffix string An optional naming standard that is appended to the end of the attribute name for each generated attribute within an object type. The initial setting is blank. The recommended value is _A. MemberNamePrefix string An optional naming standard that is added to the beginning of the operation name for each generated member within an object type. The initial setting is blank. The recommended value is M_. MemberNameSuffix string An optional naming standard that is appended to the end of the operation name for each generated member within an object type The initial setting is blank. The recommended value is _M. ------------------------------------------------------------------ 9.8 >> EDIT FOREIGN KEYS Tools:Oracle8:Edit Foreign Keys Rose Oracle8 models foreign keys as unidirectional associations. With the name of the primary key defined as Keys/Qualifier in Association specification, Role A (or B) tab. The wizard enables you to define/edit Foreign Keys for the columns in a relational table. When you identify a Foreign Key you link the column content of the relational table you have selected to the column content (Unique column, usually a Primary Key) of another referenced table. + CREATE 1. On your class diagram, select the relational table where you are adding/editing a foreign key. 2. Select Tools - Oracle8 - Foreign Key Wizard. 3. Complete the dialog as follows: FK Name This is the name of the Foreign Key you are creating/editing. Tables The Tables selection box displays all of the tables that currently exist in your schema. Select the table your Foreign Key will reference. Columns Once you have selected a table from the Tables selection box, a list of the columns with the Primary Key and Unique Key constraints is displayed in the Columns selection box. (Note that the column names are preceded with a U for Unique or P for Primary.) To select only the columns with a Primary Key, use the Select Primary Keys button. This button acts as a filter. Add Foreign Key Button When you have selected the columns from the Column selection box, use the Add Foreign Key button to complete creating the Foreign Key. When you do this: - The new Foreign Key is added to the Foreign Keys table at the bottom of the dialog, and - The FK Name field is cleared for you to create another Foreign Key. Foreign Keys Table This table lists the Foreign Keys you have created for the new relational table. You can edit the column names by double clicking on the name or by displaying the control menu via the right mouse button. + RULES FOR CREATING AND USING You can create composite keys that consist of multiple columns (up to 16) as long as the composite Foreign Key and composite Unique columns remain exactly the same (same datatypes). ! You MUST use the Foreign Key wizard to define your FKs. You can not do this directly ! + EXAMPLE __________ <> _________ | a | FKNAME | b | |----------|<-------[name_a]-|---------| | name_a | | name_b | | name_idx | --------- ---------- However if you do this directly (not using wizard) this will fail with the following error: Class B: ERROR: - {Class Name: B, Associatoin Name:Unnamed}: Keys on this association represent foreign key columns. You must specify at least one. The FK wizard puts in another parameter ('=name_a') that you cannot access from the properties tab: __________ <> _________ | a | FKNAME | b | |----------|<--------[name_a=name_a]|---------| | name_a | | name_b | | name_idx | --------- ---------- By using the FK wizard the following code is generated from the above example: CREATE TABLE TEST.A ( NAME_A NUMBER(2,6), CONSTRAINT NAME_IDX PRIMARY KEY (NAME_A)); CREATE TABLE TEST.B ( NAME_B NUMBER(2,6), NAME_A NUMBER(2,6), B_ID NUMBER(5,0), PRIMARY KEY (B_ID), CONSTRAINT FKNAME FOREIGN KEY(NAME_A) REFERENCES TEST.A(NAME_A)); + CHANGING NAME OF THE FOREIGN KEY In above example the name of the foreign key is the same name as the attribute: FOREIGN KEY(NAME_A) If you wish to use another name, create the FK using the wizard. Then go into the Association specification, then go to either Role A Detail, or Role B Detail (depending upon which direction the association is drawn, and modify Keys/Qualifiers name. For example, changing this from NAME_A to NAME_X, the diagram will now look like: __________ <> _________ | a | FKNAME | b | |----------|<--------[name_x=name_a]|---------| | name_a | | name_b | | name_idx | --------- ---------- And the FOREIGN KEY code will look like: CONSTRAINT FKNAME FOREIGN KEY(NAME_X) REFERENCES TEST.A(NAME_A)); + DEFECT NOTE: Defect in Rose 98 and initial release of 98i, All Primary Keys will not display in Data Wizard. This will be fixed in upcomming SP1 release. Meanwhile if using either 98 or 98i you can download a new or8addin.dll (beta) that will correct this problem. ftp://ftp.rational.com/public/rose/rose_cpp/patches or8addin.dll -> dll for 98i Oracle8 addin Make sure Rose is closed then replace .../Rose.../oracle8/or8addin.dll with this new one. ------------------------------------------------------------------ 9.9 >> ORDERING WIZARD Tools:Oracle8:Ordering Wizard Use the ordering wizard to: - Rearrange the attribute/column order of the Object Type, Relational Table or Relational View you have selected. - Display the current order column/attribute order. In some databases, the order of the attributes may be significant. Use the Ordering Wizard from the Tools - Oracle8 menu to display how attributes are ordered in an object type, relational table, or relational view. This is particularly useful if an attribute has a non-scalar attribute (such as another datatype or REF) since these are modeled as associations with other schema objects. 1. From your class diagram, select an object type, relational table, or relational view whose attributes/columns to want to view or rearrange. 2. Select Tools - Oracle8 - Ordering Wizard 3. On the dialog, use your mouse to drag attributes/columns to the positions you want. ! Change order in generated code only. Does not change order on diagram. To change on diagram go to Class Specification, Attribute tab, drag. ! ------------------------------------------------------------------ 9.10 >> SYNTAX CHECKER Tools:Oracle8:Syntax Checker The Rose Oracle8 Syntax Checker checks the Oracle8 model elements you select for possible DDL generation errors. You can use the Syntax Checker to make sure the schema objects in your model are defined appropriately and will generate the correct DDL for forward engineering. The portions of the model that re checked are: - All of the components that have the <> stereotype and all of the classes that are assigned to those components. - All components in the model that have their IsSchema property set to TRUE and all classes that are assigned to those components. - Any classes in the model that have a valid Oracle8 stereotype but that are not assigned to any component. To check the syntax of a model component: 1. Select (highlight) a model element. 2. Select Tools - Oracle8 - Syntax Checker. 3. View the results by displaying the contents of the Rose Log. Select Window -Log. ! Syntax checker is always run automatically whey you do tools:oracle8:Schema Generation ! ------------------------------------------------------------------ 9.11 >> SCHEMA GENERATION Tools:Oracle8:Schema Generation This menu selection enables you to generate a DDL script based on selected Oracle8 elements you selected in a Rose model. If no items were selected, then you will need to select them in the Selected Components: listbox before the Generate Key will become enabled. You will either recieve an error (check CaSe) or a Window titled Oracle8 - DDL Execution, with your code hightlighted. DONE - Exit with Save. Generated DDL is saved to the file you selected in the initial Schema Generation dialog. EXECUTE - Select (highlight) the statements you want to execute against your Oracle8 schema, use this button to initiate execution. It will display the dialogs needed to establish a connection to an Oracle8 database server. SAVE - Only becomes enabled if you edit the generated code. Will AUTOMATICALLY save script (overwrite) to DDL1.SQL or file name you specified in initial screen. If no component exists, Schema Generator will assign elements to a component named "UNASSIGNED". Code will be generated without a schema name, i.e CREATE TABLE a( instead of CREATE TABLE schema_name.a( NOTE: You can also create this component "UNASSIGNED" and generate schema nameless code. Otherwise Rose will look for a component with a schema name, ------------------------------------------------------------------ 9.12 >> REPORTS Tools:Oracle8:Reports Wizard Assisted Report Generation. Oracle 8 Database Model Report Sorted by class name or Sorted by stereotype Checkboxes: Documentation Classes Documentation Columns Methods Dependencies Associations Documenation Columns Methods Dependencies Associations Souce code (.ebs) for report available at, ftp://ftp.rational.com/public/rose/rose_cpp/patches AIO8Reports.ebs ->Updated Oracle8 report generation script(98\98i) ------------------------------------------------------------------ 9.13 >> IMPORT ORACLE8 DATA TYPES Tools:Oracle8:Import Oracle8 Data Types Use to imort O8 scalar (built-in) data types, such as CHAR, VARCHAR, etc. You will want these in your model when creating attributes. Reverse Engineering automatically loads. ------------------------------------------------------------------- 9.14 >> MORE INFORMATION - Manual that ships with Rose 98 Enterprise Edition, "Using Rational Rose / Oracle8" - Rose 98 online help, contents tab, Rose Oracle8 - Rose/Oracle8 Mapping Document This document describes the mapping of UML to Oracle8 as implemented in Rose/Oracle8. (also see on line help: Oracle8 to Rose Mapping Quick Reference) ftp://ftp.rational.com/public/rose/rose_cpp/patches/ o8mapping.doc -> Rose to Oracle8 mapping - For a walk thru showing the use of the Oracle8 wizard see, Rose/Oracle tutorial. ftp://ftp.rational.com/public/rose/rose_cpp/patches/ o8_help.zip -> Tutorial files for Rose\Oracle8 4.0.8 NOTE: Even thou this tutorial was written for Rose 4.0, very little has changed with Oracle8 wizard between 4.0 and 98. The zip file contains (5) movie files (avi) that will give you a few lessons on Rose/Oracle 8. Lesson1.avi demonstrates how a user can use Rose to analyze (reverse engineer) an existing Oracle8 schema and create a Rose model that captures the schema's elements and structure. Lesson2.avi demonstrates how a user can take the complex diagram created in the first lesson and separate it into smaller, more manageable diagrams. Lesson3.avi shows how to use the Data Type Creation Wizard to easily (and accurately) add a new element to a model, in this case, a new object type. Lesson3a.avi builds on the previous movie by using the wizard to create a new object view based on the object type the user created. It also demonstrates how to generate a report of model elements. Lesson4.avi brings the tutorial full-circle by showing how to generate and execute a DDL script that updates the original Oracle8 schema with the new object type and object view created in lesson 3. - Information on modeling: Data Modeling with Rational Rose (brief overview) http://www.rosearchitect.com/mag/archives/9901/rose101.shtml Object-Oriented Design of Database Applications http://www.rosearchitect.com/mag/archives/9901/f1.shtml Using Structured Tools in Object-Oriented Development (Designing databases) written for ROSE Architect Magazine http://argussoft.com/article.html Unified Modeling Language resource center http://www.rational.com/uml/index.jtmpl Overview of the Modeling Environment - INTRODUCTION TO OBJECT MODELING IN ORACLE8 (good overview of difference between ER diagrams and UML) http://www.dulcian.com/papers/ObjMod.htm http://www.ambysoft.com/mappingObjects.html Mapping Objects To Relational Databases An AmbySoft Inc. White Paper Also has a list of links. http://www.ksccary.com/Articles/ObjectRDBMSPattern/ObjectRDBMSPattern.htm Pattern Language for Object-RDBMS Integration "The Static Patterns" By Kyle Brown and Bruce G. Whitenack (relational schema and the object model) http://www.cetus-links.org/oo_db_systems_3.html Databases: Mapping Objects to Relations Also LOTs of related links. ------------------------------------------------------------------ ------------------------------------------------------------------