WebObjects 5: Reverse-Engineering Oracle Databases With EOModeler

EOModeler's New Database Wizard lets you reverse-engineer a database, but gives no control over which tables are available. By default, only the user's tables can be selected. This document describes how to control which tables of an Oracle database are selectable, including views, aliases, and synonyms in schemas owned by other users.

Applicable products

• WebObjects 5.1
• WebObjects 5.2
• WebObjects 5.3

Important: This technique works only for WebObjects 5.1, 5.2 and 5.3. It does not work for WebObjects 5.0. Developers using WebObjects 5.0 must write a JDBC plugin. Details on writing a JDBC plugin are in Technical Note 2027 (http://developer.apple.com/technotes/tn/tn2027.html).

How to modify Java Properties

The version of EOModeler included with WebObjects 5.1 and later uses Java's properties mechanism to control the New Database Wizard's behavior.

Note: For more information about how WebObjects uses Java's properties, see this article). Look at the section titled "Accessing The Properties". WebObjects 5.3 and later only: The EOModeler application resides under /Developer/Applications/WebObjects/ This article does not apply to the EOModeler plug-in used with Xdesign/Xcode.

Follow the steps below to modify the Java properties.

1. Quit EOModeler if it is open.
2. Copy the file /System/Library/Frameworks/JDBCEOAdaptor.framework/Versions/Current/Resources/Properties to your home directory.
   
   With Microsoft Windows, replace /System with the value of $NEXT_ROOT, usually C:/Apple.
   
   
3. Rename the new file WebObjects.properties.
4. Open WebObjects.properties in a text editor.
5. To show all tables owned by all users, change these lines:
   

   #  The default is to use the USER's schema
   #jdbcadaptor.oracle.wildcardPatternForSchema=null

   ... like this:
   
   

   #  Show everyone's tables (including SYSTEM)
   jdbcadaptor.oracle.wildcardPatternForSchema=%

6. To show all tables owned by the user STEVE, change these lines:
   

   #  The default is to use the USER's schema
   #jdbcadaptor.oracle.wildcardPatternForSchema=null

   ... like this:
   
   

   #  Show STEVE's schema instead of mine
   jdbcadaptor.oracle.wildcardPatternForSchema=STEVE

7. To list the tables using a custom SQL query, use the sqlStatementForGettingTableNames property. Here's an example that shows all non-system views and private synonyms. Change these lines:
   

   #  The default is to use JDBC API instead of executing a SQL statement.
   #jdbcadaptor.oracle.sqlStatementForGettingTableNames=null

   ... to this:
   
   

   # Use this SQL statement to determine table names
   jdbcadaptor.oracle.sqlStatementForGettingTableNames=SELECT DISTINCT OWNER || '.' || 
   TABLE_NAME FROM SYS.ALL_CATALOG WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC') AND TABLE_TYPE IN ('VIEW', 'SYNONYM')

8. Save the WebObjects.properties file, and test the changes by opening EOModeler and creating a new model. Choose the JDBC adapter and proceed with the New Model Wizard until the list of tables appears.

Troubleshooting Tips

1. Make sure that your table names (entity names) are unique. Otherwise, EOModeler can generate errors, such as:
   

   -[NSCFDictionary setObject:forKey:]: attempt to insert nil value

2. Check that your SQL statements are correct to avoid the following type of error:
   

   EvaluateExpression failed: <OraclePlugIn$OracleExpression: <<.... >>
   Next exception:SQL State:42000 -- error code: 936 -- msg: ORA-00936: missing expression

3. WebObjects 5.3: The Oracle Database can take a long time to fetch all tables when setting the following property:
   

   jdbcadaptor.oracle.wildcardPatternForSchema=%

   During that time, the EOModeler application seems to be unresponsive (The "Force Quit" panel will show EOModeler as non-responding). Wait until the fetch completes, and EOModeler will return to the active state.