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;
Next you will be prompted for the directory to generate the SQL scripts in;
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 :
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;
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
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.
