Installing Oracle's ODBC drivers

One of my projects requires an ODBC connection from a Windows environment to Oracle 10x. As most of you know, it's difficult to find step-by-step instructions on how to install it. So this is what I've done so far to at least get the driver installed:

1. Download Oracle InstantClient (Basic) and the InstantClient ODBC suppliment (http://www.oracle.com/technology/tech/oci/instantclient/index.html). It’s a much smaller footprint than installing the whole Oracle Client.
2. Unzip both files to folder. Copy root folders to a folder like C:\Oracle\instantclient (basic and supplement will be in same folder).
3. Run odbc_install.exe from command prompt – you can see results better than double-clicking the executable.
4. Add Oracle’s System Variables (Windows control panel\System\Advanced tab, click Environment Variables, click Add for System Variables):
   LD_LIBRARY_PATH
   PATH (append)
   SQLPATH
   TNS_ADMIN
5. Add (create) TNSNAMES.ORA file to folder described above in TNS_ADMIN.
6. Create ODBC data source connection the typical way in Windows.
7. And there you have it! To uninstall, first delete ODBC connection that you created in step 6, then remove envronment variables from step 4, then run odbc_uninstall.exe.

Using BizTalk Value Mapping (flattening) and Looping functoids together

I have a map that I've been working on and needed to flatten out a source (looped) to target (flattened out). Basically a looped XML source to an X12 EDI destination. Currently I'm developing in BizTalk 2006 (not R2).

My source XML in question looks like this:

<BI_XHD_CNT_TAO class="R">

<SETID>ID</SETID>

<CUST_ID>123456</CUST_ID>

<BUSINESS_UNIT>SomeBusiness</BUSINESS_UNIT>

<INVOICE>12345</INVOICE>

<NOTES_SEQ_NUM>2</NOTES_SEQ_NUM>

<NOTE_TYPE>SHIPPING</NOTE_TYPE>

<TEXT254>Tracking: 987654321</TEXT254>

</BI_XHD_CNT_TAO>

<BI_XHD_CNT_TAO class="R">

<SETID>ID</SETID>

<CUST_ID>123456</CUST_ID>

<BUSINESS_UNIT>SomeBusiness</BUSINESS_UNIT>

<INVOICE>12345</INVOICE>

<NOTES_SEQ_NUM>2</NOTES_SEQ_NUM>

<NOTE_TYPE>SHIPPING</NOTE_TYPE>

<TEXT254>Carrier: UPS GRND</TEXT254>

</BI_XHD_CNT_TAO>

.

. (more iterations - other stuff in the TEXT254, but just the above is what I want)

.

I get multiple iterations of this information - the TEXT254 info is what I'm looking for. I need to extract and flatten the information to different parts of my destination - an X12 EDI 810 (invoice). The SAC segment has two elements - SAC_04 (shipping carrier name (i.e. FedEx) for the trading partner), and the SAC_13 is the tracking number.

Source and Destination:

I mapped the source and destination using the Value Mapping (flattening) functoid, and had to do some minor string manipulation (i.e if the text starts with “Carrier” or “Tracking” then map it out) but I wasn't getting the results I was looking for. I was still getting multiple iterations of the SAC segment – and I only can have one. Initially my output XML looked like this:

<SACLoop2>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC04="FED EX"/>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC13="123456"/>

</SACLoop2>

and what I wanted to get was this:

<SACLoop2>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC04="FED EX" SAC13="123456" />

</SACLoop2>

After understanding what was going on, I realized I needed to add a Looping functoid, as shown below (and selected):

This flattened out everything out further and gave me the results I was looking for:

<SACLoop2>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC04="FED EX" SAC13="123456" />

</SACLoop2>

Hope this helps out. Other suggestions are always welcome.

Welcome!

Welcome to my blog!

This blog will be semi-technical in nature mostly focused within the Microsoft world.

I work as a vb.net/tfs developer in the Midwest. I used to consult, but grew tired of poorly matched engagements.

I've done BizTalk in the past, and have touched InfoPath, SharePoint, heavy EDI and other stuff.

Hope I can make contributions to the developers out there that have gone bald by constantly pulling their hair out...