Headless Operation – OWB code generation, going it alone

There are a few different implementation scenarios for moving designs from OWB into an execution environment, out of the box OWB has a runtime service component which supports deployment and execution of all of OWB's design objects. The standard deployment through the control center is the approach the majority take, it is the one we document and include in training etc. This post will illustrate how you can roll your sleeves up and go it alone in a headless mode, using the deploy to file utility (on exchange here). In the OWB designer you can generate code for any object, take the code by saving it to file or copy-pasting and run where ever you want. This utility speeds up this process and saves scripts for a bunch of objects into a directory and provides a convenient top-level wrapper script.

The deploy to file utility uses some OMB scripting commands to generate a directory of SQL scripts and control files for objects in OWB's designer. Not all objects can be deployed in this way, but common ones can;

  • tables/views/materialized views
  • dimensions/sequences
  • procedures/functions/packages
  • mappings (PLSQL/SQLLoader)

The utility will generate the code as a bunch of SQL scripts (and SQLLoader control files) and be installed independently of the OWB product (there is still the need for the repository tables for the runtime etc.). The utility provides the ability to deploy from a collection or an Oracle module. It uses the OMBDEPLOY ....AS SCRIPT TO ... grammar to generate a script that can be executed (deployed) in SQLPlus like any other Oracle SQL script. For SQLLoader mappings a control file is generated and this control file can be used by invoke SQLLoader command line and passing the data input file name on the command line. For execution of the PLSQL mappings use the MAIN function method (not the procedure entry point). Process flows cannot be deployed since the code is XPDL (XML) that is interpreted by the runtime adapter and transformed into Oracle Workflow for example. Executing in this manner will also not give the full audit capabilities of OWB, so there are a bunch of caveats.

Users can use collections to group sets of objects together and use this as a deployment unit. For example to deploy all objects in a collection simply right click on the collection node and select 'Deploy To File', the example below will specifically only generate code for the table and mappings in the collection DEP_TEST;

deploy2file3

Next you will be prompted for the directory to generate the SQL scripts in;

deploy2file4

That's it. The utility will generate all of the SQL scripts and save them to file also generating a master create.sql script (dependent order is not guaranteed). The utility is an expert, so can be altered, enhanced to provide such capabilities.

The utility is also available from the Oracle module node :

deploy2file5

When executed from the Oracle module node you will be prompted to select the types you wish to deploy, so you can decide to only generate tables or mappings or any combination can be selected;

deploy2file6

A handy little utility.

I mentioned that not all of the OWB metadata is included since manual deployment was chosen, so you cannot see the deployment information in the OWB runtime tables, or the executions from the control center. The audit details such as errors and row counts are still performed. For example taking the following query;

  • select task_type,exec_location_uoid,object_name,object_type, number_task_errors,number_task_warnings from all_rt_audit_executions

The task_type, exec_location_uoid are normally populated when executing an object deployed through OWB, this ties all the metadata together that the control center manager and browser use, When you manually deploy the script this is unavailable, audit rows are still recorded so the number of task errors and warnings are still reported. The auditing is still done for PLSQL maps, for SQLLoader maps, scraping of the audit details from the log into the audit tables is done by the control center service, so manual execution will not have such audit info.

There is a SQLDeveloper report for viewing the execution information included in the zip. Import the file owb_sqldev_headless.xml from the 'User Defined Reports' node in the Reports panel of SQLDeveloper. This will create a report 'Mappings (headless)' under 'User Defined Reports/OWB/Execution' folders. The report is a master detail report showing;

  • throughput
  • mapping audit
  • mapping errors
  • throughput chart
  • timing analysis

deploy2file9

This approach may be useful for you when you are happy manually building the system and leveraging the code generation capabilities of OWB. This is definitely for the more savvy OWB user since you will be responsible for the database links/directories for example that are used by the mappings and for orchestration of the mappings etc.

Comments:

Hi David,

We've been using a similar approach with version 10.2 for a while now and it works great. However, we're in the process of upgrading to OWB 11.2, and can't seem to get headless operations to work...basically we generate the code and compile it into the target environment. We then make a call to the Main Function to execute the mapping, however we are now getting the following error:

ORA-20213: Unable to create standalone job record for map named "RJ_TEST_TI_1" having UOID A99865D78FEC11A1E04400144F80C6CA

This method used to work fine in v10.2, any ideas on what’s going wrong in v11.2?

Cheers

Raj

Posted by guest on August 22, 2011 at 04:01 AM PDT #

Hi Raj

In SQLPlus are you connected as the user where the mapping SQL script was manually deployed using the generated SQL script?

If this schema created via the OWB repository assistant or OWB security UI? ie is it a repository workspace user.

Cheers
David

Posted by guest on August 22, 2011 at 04:56 AM PDT #

Hi David,

Thanks for your quick reply!

I am logged into the schema where the generated sql script was manually compiled, and can confirm tha the schema is registered as a workspace user.

Cheers

Raj

Posted by guest on August 22, 2011 at 08:34 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today