Oracle Warehouse Builder (OWB) Weblog

Well, not sure if anyone missed my posts on this blog, but recently I moved myself onto a new blog. We have started a more generic data warehousing blog, which focusses on the actual database platform, rather than on tools. You will see items on new functionality, on best practices and other hopefully interesting things. It is also the home of the DW team's ideas and thoughts on the HP Oracle Database Machine.

You can find us at: http://blogs.oracle.com/datawarehousing/

While you are reading this, if you are interested in HP Oracle Database Machine, google Kevin Closson, he writes a nice blog on storage and architecture. Very interesting and very detailed stuff.

Sometimes this blog is very into nitty-gritty, tactical technical suggestions, but sometimes (like when you’re coming up on the end of the week and tired of your to-do list) it’s useful to raise your head and look a few years out at changes in the industry that will probably eventually impact your day-to-day concerns. I tend to look at cloud computing at times like this and try to see how it will affect data integration and data quality.

Recently I ran across this blog post on cloud computing, BI and DI from the Open Group Cloud Computing summit:

A few observations around BI and cloud computing from the show in attending other talks, and just in the hallways over coffee.

First, cloud computing can't not progress forward without a clear data integration and business intelligence strategy, if you ask those in charge.   The core questions are:  How will my information get back into my enterprise when I need it, and better yet, how can I consider data stuck in the cloud in the context of my BI requirements?

Second, security.   Enough said.   

Finally, cultural issues around leveraging platforms we don't own.

Cloud computing is not evil, indeed it's an opportunity to leverage databases-as-a-service, and even information-as-service, at a price point unheard of.   However, you need to place all of this in the context of a cloud computing strategy that specially addresses data security, BI, MDM, and data integration.   

I would like to know how the OWB user community is thinking about such issues as cloud and SAAS become inescapable parts of our environment. I’d also like to shamelessly promote the LinkedIn group the OWB team started on data integration and data quality offerings from Oracle, and foster discussion there.

So:

• if you use OWB or just follow Oracle data integration offerings, come join the OWB LinkedIn group. It will put you in contact with hundreds of Oracle and outside people who work with or work on Oracle Warehouse Builder and our other data integration offerings. (There are also job listings and recruiters in the group, which may or may not interest you.)
• If you’re interested in these topics, come join our discussion over at LinkedIn.

Hope to see you there…

With OWB so widely adopted, it’s not too surprising that there’s an ecosystem of consulting companies offering OWB-related services and solutions to Oracle customers. Today, we want to give a tip of the hat to Oracle Partner iSeerix, a consulting and software development organization located in Rochester, New York.

Much of their business is centered on BI consulting, and they have developed considerable consulting expertise with Oracle solutions including OWB. They have now distilled much of their experience into a finished software product, iSeerix Release Manager, that you can use with OWB to automate software configuration management, release management, and promotion of releases among development, test and production environments. This eliminates the need for OWB customers with complex requirements to develop manual or scripted processes for these tasks.

You can find out more about iSeerix at www.iseerix.com and read up on OWB release management best practices with iSeerix Release Manager in their whitepaper, downloadable from OTN here.

If you have solutions that leverage or extend OWB, let us know and we may feature you on the blog too.

Here is some information on the process flow variable literal quote or not quote query. Its not exactly intuitive, so hopefully this helps! The flow designer has to know what is a PLSQL activity and what is not. Process flow variables are added in the same manner as process flow parameters (see the post here for details)

Literal = FALSE

When Literal = FALSE is set then the value entered must be a valid PL/SQL expression which is evaluated at the Control Center e.g.
'Hello World!'
22 / 7

Literal = TRUE

When Literal = TRUE then the value is dependent on the the type of Activity.  If the activity is a PL/SQL object i.e. Mapping or Transformation, then the value is PL/SQL snippet.  The critical difference here is that the value is macro substituted into the call for the object.  The format of the value is identical to that entered as default value is the Mapping editor. e.g.
'Hello World!'
sysdate()

If the activity type is not a PL/SQL object then the value is language independent. e.g.
Hello World
3.1427571

Some illustrations

So what happens when you get it wrong? The following variable definitions and default value

will give the following errors when the flow is executed:

Error
RPE-01003: An infrastructure condition prevented the request from completing.
PFLOWCON
Error
RPE-01038: Failed to evaluate expression declare "$LOOP_DETECT$" NUMBER := 0;function "PFLOWCON" return VARCHAR2 is "WARNING" NUMBER := 2;"RETURN_RESULT_NUMBER" NUMBER := NULL;"NUMBER_OF_ERRORS" NUMBER := 0;"SUCCESS" NUMBER := 1;"ITEM_TYPE" VARCHAR2(4000) := (owbsys.wb_rt_task_variable.get_value(12137));"ERROR" NUMBER := 3;"EVAL_LOCATION" VARCHAR2(4000) := (owbsys.wb_rt_task_variable.get_value(12140));"OK" NUMBER := 1;"FAILURE" NUMBER := 3;"AUDIT_ID" NUMBER := 12136;"RETURN_CODE" NUMBER := NULL;"NUMBER_OF_WARNINGS" NUMBER := 0;"ITEM_KEY" VARCHAR2(4000) := (owbsys.wb_rt_task_variable.get_value(12139));"RETURN_RESULT" VARCHAR(64) := NULL;"PARENT_AUDIT_ID" NUMBER := NULL;"OK_WITH_WARNINGS" NUMBER := 2;begin "$LOOP_DETECT$" := "$LOOP_DETECT$" + 1;if "$LOOP_DETECT$" > 2 then raise_application_error(-20001, 'Loop detected calling "PFLOWCON"');end if;return to_char(defaultvalue);end;begin :result := "PFLOWCON";end;.  Please modify the expression, redeploy and retry again.
PFLOWCON
Error
ORA-06550: line 1, column 831:
PLS-00201: identifier 'DEFAULTVALUE' must be declared
ORA-06550: line 1, column 816:
PL/SQL: Statement ignored

This error happens when you have a variable defined as a STRING, with literal defined as false, and the value of the variable is not quoted.

So the following two variable definitions are equivalent and valid:

So watch out for the RPE-01038 exception and check the values and literal definitions.

For some years we have had our own OWB Utility Exchange on OTN where we post useful scripts and experts for the OWB user base. We are planning to reorganize that content to make it easier to access.

We also receive from time to time contributions of scripts from our user community that other customers may find valuable. Until recently, we have encouraged posting those on the official Oracle Wiki page for OWB (http://wiki.oracle.com/page/OWB+Utilities+and+Experts). However, to maintain a certain amount of control over that content and to concentrate it where our traffic actually goes, we have decided to start posting them on the blog instead.

Check the User-Contributed Content category to find such material. 

Contributing a Utility or Expert for Oracle Warehouse Builder

• To submit material, send an email message to antonio.romero@oracle.com and tell me what you have.
• Describe the expert or other script, the version of OWB it was developed for (including patch level), how to install and use it, and who to contact with any questions.
• If we think it’s of general interest and well-crafted etc., we’ll run it and link back to your site.