Oracle Warehouse Builder – OMB Script quirks

If you’re like me, you like to use the patterns of the Data Vault or Data Warehouse to auto-generate code, more specifically auto-generate ETL or ELT code.  Well, I’m generating code for OWB which really is OMB scripting language defined by Oracle.  In this entry I’ll discuss just a few minor gotcha’s that maybe you did or didn’t know about.  This entry is realted to OWB for 11gR2. 

  1. Line continuation characters – there CANNOT be anything after a line continuation character, not even a space! this will lead you to have an invalid command on the next line.  BUT…  the next line CAN start with spaces (this is a quirk).
  2. Adding “anything” to OWB requires a CONTEXT first, make sure to execute OMBCC command for a context.
  3. When ADDING columns to tables, NUMBER, NUMERIC, and DECIMAL have the following rules:  a) IF a precision is greater than zero, THEN both precision and scale MUST be identified in the ADD command b) IF precision is NOT specified, then both precision and scale must be LEFT OUT of the add command.
  4. When ADDING columns to tables, non-numeric columns that HAVE a “length” must specify the keyword LENGTH instead of PRECISION and SCALE.
  5. Line Continuation characters “”  do NOT need to go on the last command before the OMBCOMMIT

EXAMPLE CODE: 

OMBCREATE TABLE ‘JOB_PROCESSING_LOG’
  SET PROPERTIES (DESCRIPTION) VALUES (‘Contains dbms job processing data.’)
  ADD COLUMN ‘PROCESSING_LOG_ID’
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES (‘NUMBER’,0,’Job processing log record PK sequence number’)
  ADD COLUMN ‘JOB_ID’
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES (‘NUMBER’,0,’Oracle dbms job ID.’)
  ADD COLUMN ‘PROCESSING_STATUS’
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES (‘VARCHAR2′,30,0,’Processing status’)
  ADD COLUMN ‘PROCESSING_DATE’
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES (‘DATE’,0,’Processing date and time’)
  ADD COLUMN ‘PROCESSING_NOTE’
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES (‘VARCHAR2′,2000,0,’Processing note’)
  ADD COLUMN ‘CREATE_DATE’
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES (‘DATE’,0,’Record create system date and time’)
  ADD COLUMN ‘CREATE_USER’
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES (‘VARCHAR2′,30,0,’Oracle user name’)
  ADD COLUMN ‘SYS_CREATE_DATE’
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES (‘DATE’,0,’Database maintenance create system date and time’)
OMBCOMMIT

Hope this helps, there will be more coming soon.

Tags: , , , , ,

2 Responses to “Oracle Warehouse Builder – OMB Script quirks”

  1. Roelant Vos 2010/05/03 at 10:58 pm #

    Hi, with OMB (Oracle Meta Base) you can basically do everything you can configure with the OWB client tool. This includes creation of metadata definitions (target tables, mapping metadata, technical settings like schemas and db links and the whole deployment, scheduling and running).

    I’ve added a complete set of OWB / OMB scripts including source data so set up an entire demo environment based on Data Vault which anyone can use. These scripts can be downloaded here (including the source data: http://www.ravos.com.au/wordpress). It’s not perfect, but the main concepts are there.

    The thing to be seen is whether Oracle Data Integrator will continue to support this kind of scripting. OWB and ODI are to be combined according to the Oracle product roadmap. Anyone has experience with that?

    Regards,
    Roelant Vos

  2. dlinstedt 2010/05/07 at 10:04 pm #

    I’ve heard that Oracle will continue to support it, as well as make it stronger – they apparently have a huge base of Oracle technologists who are working with OMB…

    I am continuing to work on generating OMB scripts that will produce mappings, there will be more information forthcoming soon.

Leave a Reply

*