Introducing Darwin Oracle Type Accelerator

Years ago, I created a set of XSL templates and queries to create object types out of queries on the datadictionary of the Oracle Database. It only did basic types on tables, and selects on those. I wrote an article on it that still can be downloaded here. Later, I extended the framework to also do inserts and updates and follow foreign keys. The thing with foreignkeys is that you can handle them a as a detail-tables like Order Lines with an Order. Or lookup, like a place of birth, or country of origin, etc.

I must say I was quite pleased with what it could do.

Lately I stumbled on a question on community.oracle.com that ran about updating multiple tables using the database adapter. Normally you would need to do multiple invokes of a Database Adapter definitions, at least one per table, to do inserts and or updates.  I created thus because Oracle Types are a very powerful means to get data from a datamodel with multiple tables in just one invoke. Or insert data into it. You just create a pl/sql procedure with a parameter based on the root object. The database adapter wizard creates the accompanying XSD's and you only need to do a proper mapping in to the input variable and do the invoke. In the Pl/Sql procedure you call the particular method (sel, ins, upd) of the root object, that propagates into all the child and lookups. Most of the times that is enough. In more complex models, you might enhance the calling pl/sql.

I'm happy to announce that a moment ago I put my source on Github. I renamed it to Darwin Oracle Type accelerator. I did not have the change to create elaborate documentation. But in short:

• In $GitHub/Dotacc/Source/Dotacc/ddl/owner\ you'll find setup scripts for the framework.
• With setupTables.sql and setupPlsql.sql you create a bunch of tables and pl/sql with XXX_ as a prefix to support the generation of types.
• insertXslNL2.0.sql or insertXslEN2.0.sql creates the xsl in the xxx_xmldocuments table.
• $GitHub/Dotacc/Source/Dotacc/Config contains a set of insert scripts to define what tables, foreignkeys etc. to handle: 
• XXX_TABLES: defines the tables for which you want to generate the types. The column generation_order defines in which order the types are created or dropped at recreation. 
• XXX_FK_DEFINITIONS: defines the foreignkeys to consider. The FK_TYPE column defines if it should be considered as a child table ('DETAIL') or Lookup ('LOOKUP'). 
• XXX_DERIVED_COLUMNS: defines virtual columns that can be looked up from another table. You can define a method that is added to do the actual lookup based on a lookup value from a key column. 
• XXX_CUSTOM_METHODS: can be used to add custom methods to the object type.
• I added two datamodels (Doe_owner and hbc_owner under ddl) as a sample model. It would be nice to come up with a sample filling for named tables.

To do a recreate of the types, perform:

execute  xxx_gen_objects.recreate_objects;

 If you have remarks, post a comment on the blog and/or do a request to contribute on github or send me a PM on community.oracle.com.