Workaround for Oracle Views in ITSM 5.04-5.06

In ITSM (versions from 5.04 and 5.06) the linking of external data to Oracle views is problematic (eg. you are able to see the view but no columns are presented to add, even though they exist). Below is a work around if you need to connect an Oracle view in as an External Data source.

1. Create a new definition set.
2. Create an Object (just a Standard or Validation Business Object depending on your requirements), and define all the fields that WILL appear in your database view (i.e. Mimic the structure of the view you wish to connect in an ITSM table)
3. Make sure the view does not exist in the database – drop it if necessary,
4. Commit your definition set. The system will create a table in the database.
5. Directly in the database (i.e. connect to the database with a tool such as Toad etc. not through the ITSM administrator ), drop the table.
6. Directly in the database (i.e. connect to the database with a tool such as Toad etc. not through the ITSM administrator ), create the view with the same name and in the same table space as the table that was created through ITSM administrator in Step 2 (remembering that capitalization is important in Oracle systems).
7. The system will now use the view as if it were a table, and will operate normally.

Important points:

1. If there are any changes to the format of the object in Administrator, the commit will fail, because it will try to drop the table (which doesn't exist because it is a view)
2. You will need to 2 SQL scripts – one to run before a commit, one to run after.
1. The first will drop the View, and create a table with the same structure as the view.
2. The second will drop the table and recreate the view.
3. Adjust the security for the new object that you've created so that no user can update or add to this table to prevent errors in the tool
   
4. All fields in the table must be replicated in the view, this includes RecId, LastModDateTime, LastModBy, CreatedDateTime and CreatedBy