4 Sample Scripts

Updating a Design Repository

One possible use case is to perform mass update on repository metadata. Users can write the following script to add a primary key with local column ID for each table with name beginning in EDW inside the module MY_MODULE:

OMBCC MY_MODULE;
foreach tableName [OMBLIST TABLE EDW*] { \
OMBCREATE TABLE '$tableName' \
ADD PRIMARY_KEY '$tableName_PK' SET REFERENCE COLUMNS ('ID');} 

We can build even more powerful and useful script using if-then-else:

foreach tableName [OMBLIST TABLE EDW*] { \
set columnList [OMBRETRIEVE TABLE '$tableName' GET COLUMNS]; # Use lsearch to search for a name in a list 
if {[lsearch $columnList 'ID'] == -1} {
      OMBCREATE TABLE '$tableName' \
         ADD COLUMN 'ID' \
            SET PROPERTIES (DATATYPE, LENGTH, NOT_NULL) VALUES \
               ('NUMBER', 10, 'true');
   }
}

The preceding script checks the list of tables which name begins with EDW whether each of them contains an ID column. If not, it will create an ID column for that table. Hence, executing the preceding script will guarantee that all tables with names beginning in EDW will have the ID column defined.

Reporting on Repository Objects

Another common use is for reporting purpose. The following script displays the properties of the table T1 and its column definitions on standard output:

#Displaying metadata of a table
puts -nonewline "Please enter the table name: " gets stdin tableName
puts ""
puts "Report on $tableName"
puts "======================================="
puts "Physical name = $tableName"
puts "Logical name = [lindex [OMBRETRIEVE TABLE '$tableName' GET \ PROPERTIES(BUSINESS_NAME)] 0]"
puts "Description = [lindex [OMBRETRIEVE TABLE '$tableName' GET \ PROPERTIES(DESCRIPTION)] 0]"
puts "---------------------------------------"
set columnList [OMBRETRIEVE TABLE '$tableName' GET COLUMNS]
set i 1
foreach colName $columnList {
set dt [lindex [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET \ PROPERTIES(DATATYPE)] 0]
   if { $dt == "VARCHAR2" } {
      set prop [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET PROPERTIES(LENGTH, NOT_NULL)]
      puts "Column $i: $colName datatype=VARCHAR2 length=[lindex $prop 0] \
           not_null=[lindex $prop 1]"
   } elseif { $dt == "NUMBER" } {
      set prop [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' \
                GET PROPERTIES(PRECISION, SCALE, NOT_NULL)]
      puts "Column $i: $colName datatype=NUMBER precision=[lindex $prop 0] \
          scale=[lindex $prop 1] not_null=[lindex $prop 2]"
   } elseif { $dt == "DATE" } {
      set prop [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET \ PROPERTIES(NOT_NULL)]
      puts "Column $i: $colName datatype=DATE not_null=[lindex $prop 0]"
   } # end else
   incr i
}

A sample output is like the following:

Physical name = T1
Logical name = Table 1
Description = This is my first table.
=====================================
Column: ID datatype=NUMBER precision=0 scale=0 not_null=1
Column: NAME datatype=VARCHAR2 length=30 not_null=1
Column: VALUE datatype=VARCHAR2 length=100 not_null=0 

Finding Invalid Objects

Users can also take advantage of the validation service provided by scripting, like this:

set tableList [OMBLIST TABLES];
foreach tableName $tableList {
   if { [OMBCOMPILE TABLE '$tableName]' == "Invalid." } {
      set context [OMBDCC];
      puts "Table $context/$tableName is invalid.";
   }
}

The preceding script will tell users which table is invalid under the current module.

Using OMB*Plus to Navigate Repositories

Another scenario we present is for a disabled user that relies on OMB*Plus interactive shell (and also some screen reading software for the disabled) to navigate through a Warehouse Builder repository:

OMB+> OMBCONNECT owb/owb@localhost:1521:dev901
Connected. 
OMB+> OMBLIST PROJECTS
DIM_TEST_PROJ MY_PROJECT PROJ_ENDTOEND PROJ_RELATIONAL TEST_DIM_PROJ
OMB+> OMBLIST PROJECTS .*RELATION.* 
PROJ_RELATIONAL
OMB+> OMBCC 'PROJ_RELATIONAL'
Context changed.
OMB+> OMBDCC 
PROJECT /PROJ_RELATIONAL
OMB+> set OMBPROMPT ON
ON
OMB+> OMBDCC
PROJECT /PROJ_RELATIONAL
/PROJ_RELATIONAL>
/PROJ_RELATIONAL> OMBLIST ORACLE_MODULES
WH
/PROJ_RELATIONAL> OMBCC 'WH'
Context changed.
/PROJ_RELATIONAL/WH> OMBLIST TABLES
PRODUCT PO
/PROJ_RELATIONAL/WH> OMBRETRIEVE TABLE 'PO' GET COLUMNS
OID PROD_ID ORDER_DATE CUSTNAME
/PROJ_RELATIONAL/WH> OMBCC '..'
Context changed.
/PROJ_RELATIONAL> OMBCC '..'
Context changed.
/>
/> OMBDISCONNECT
Disconnected.