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

Uli Bethke Oracle Data Integrator (ODI)

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.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.