ODI Snippets: Reverse engineer surrogate key of XML/XSD as VARCHAR

October 7, 2009

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
The other day I had to load data from an Oracle table into an XML file. The primary key of the table was of type VARCHAR.
When you reverse engineer the XSD model for the XML ODI adds a surrogate key of type NUMERIC to each datastore in the XML. When I tried to load this from my Oracle table with primary key of type VARCHAR I got an exception.
After much digging around I finally had a look in the documentation et voilá…
As part of the JDBC URL to your XSD you can submit a parameter ni (numeric_id). By default it is set to true and will create NUMERIC surrogate keys. If set to false this will generate FKs and PKs as VARCHARs.