Can Oracle and OWB Do Email, LDAP and SOA? Yes, We Can.
By antonio romero on Jun 09, 2008
Sometimes OWB PM gets questions about ways to use Warehouse Builder in consulting situations, sales-related proof of concepts implementations, and so on. Some that came up recently include:
- How OWB can be used to create or consume Web Services (a subject of a recent whitepaper);
- How OWB can send email with attachments (the process flow task for sending email makes no provisions for attachments);
- How OWB can make updates to an Active Directory or other LDAP server
In the second case, we recommended the PL/SQL package UTL_MAIL. Incorporating a short bit of PL/SQL code that called the needed functions (make the attachment, create the mail message with the attachment, send it out) into a process flow would cover the customer's/developer's need.
The third case was more interesting, because of the thinking behind it, and the questions it raised.
The original question that reached PM was, "Can OWB write to ODBC targets? (Or do I need a different ETL product?)" The line of thinking was something like:
- I'm using an ETL tool
- An ETL tool can update data in database targets
- Therefore, if I want to update something that isn't a database, I should make it look like a database target
- Therefore, the customer needs to license a third-party ODBC driver for LDAP, and choose an ETL tool that can write to ODBC
- Can OWB write to ODBC? ("Yes, we can", by the way-- the core product can use the gateway for ODBC with any target, no cost.)
This chain of reasoning is quite understandable, but it misses an important point: OWB is much more than a tool for talking to data sources and targets. OWB is now and has always been a great tool to maximize productivity for developers of PL/SQL-based data integration solutions on top of the Oracle database. (Future mechanisms for heterogeneous data integration in our roadmap don't obsolete any of the strengths of OWB when used with Oracle databases.)
The PL/SQL part is crucial here. From OWB you have access to all the functionality Oracle builds into the PL/SQL packages that come with the Oracle database. (This, for example, is how we have incorporated Web Services/SOA functionality into OWB since our 9.2 release. PL/SQL is generated for OWB mappings, and all PL/SQL packages can be accessed as web services, therefore OWB mappings can be exposed as web services. Likewise, PL/SQL can consume Web services, OWB generated code is PL/SQL code, therefore OWB can consume web services.)
In this instance, OWB access to PL/SQL enables a much more direct solution, with no third-party component and no need to abandon OWB. We proposed a little PL/SQL code to call the DBMS_LDAP and DBMS_LDAP_UTL packages from PL/SQL to update the LDAP server directly, and incorporate that code into a process flow.
Can we do more over time to package up capabilities like the ones above? Yes, we can-- whenever the market demands it. (Tell us what you want-- you, after all, are the market.)
But the next time you look in your palette and you can't find the right icon to drag out to do exactly what you want, don't give up, reframe the question: "Can the Oracle Database platform do this? Are there PL/SQL packages that can do this?" You won't want to or need to do this often-- but when you choose to, yes, you can.