IT-eye

It is very common, when creating a Staging Area in Oracle Warehouse Builder, to create tables in this Oracle Module with the same structure and similar names as the tables in the Oracle Source Modules.

When the above is the case and a lot of tables are needed then it is far more productive and effective to create those tables using an OMBPlus script.

See below script for creating tables.

# Set variables.
puts -nonewline "SET VARIABLES.... "
set project 'DATAWAREHOUSE_PROJECT'

set sourcemodule 'SOURCE_AMSTERDAM'

set targetmodule 'STAGING_AREA'
puts "DONE"

# Get tables from $project/$sourcemodule
  OMBCC $project
  OMBCC $sourcemodule

  set tableList [ OMBLIST TABLES ]
  foreach tableName $tableList {
    puts $fname "$project / $sourcemodule / $tableNamer"
    puts $fname "***********************************************************************************r"
#   Delete last 4 characters (_CUM) of the name of the selected table.
    puts -nonewline "RENAMING TABLE NAME $tableName.... "
#   Get lenght of selected tablename.
    set v_t_lengte [string length '$tableName']
#   Set lenght of string 7 chars smaller;  startposition + (_CUM' )
    incr v_t_lengte -7
#   Create new tablename for SA.
    set v_sourcetable [string range $tableName 0 $v_t_lengte]
    puts "$v_sourcetable"
    puts $fname "MODIFIED TABLE NAME $tableName TO $v_sourcetabler"

    OMBCC '..'
    OMBCC $targetmodule

#   Create new table in SA.
    puts -nonewline "CREATE TABLE $v_sourcetable IN $project/$targetmodule.... "
    OMBCREATE TABLE '$v_sourcetable'
    puts "CREATED"
    puts $fname "TABEL $v_sourcetable CREATED IN $project / $targetmoduler"
    puts $fname "-----------------------------------------------------------------------------------r"
    puts $fname "             TABEL			DATATYPE	LENGTH		NOT_NULLr"
    puts $fname "             ===============		=============	============	========r"

    OMBCC '..'
    OMBCC $sourcemodule
#   Get all colomnnames, datatypes and lenght per table.
    set columnList [ OMBRETRIEVE TABLE '$tableName' GET COLUMNS ]
    foreach columnName $columnList {
      puts "  ADD COLUMN $columnName TO $v_sourcetable"
      puts -nonewline "    GET COLUMN PROPERTIES.... "
      set columnDataType [ OMBRETRIEVE TABLE '$tableName' COLUMN '$columnName' GET PROPERTIES (DATATYPE) ]
      set columnDataNull [ OMBRETRIEVE TABLE '$tableName' COLUMN '$columnName' GET PROPERTIES (NOT_NULL) ]
      set columnDataLength "N/A"
      puts "DONE"
      if {"$columnDataType" == "VARCHAR2"} {
        puts -nonewline "    RESIZE DATATYPE.... "
        set columnDataLength [ OMBRETRIEVE TABLE '$tableName' COLUMN '$columnName' GET PROPERTIES (LENGTH) ]
#       The colomn values need to be changed into the project standards.
#        varchar2 >=   15 wordt varchar2(15)
#        varchar2 >=   50 wordt varchar2(50)
#        varchar2 >=  100 wordt varchar2(100)
#        varchar2 >=  250 wordt varchar2(250)
#        varchar2 >= 1000 wordt varchar2(1000)
        if {$columnDataLength < 15} {
          set columnDataLength 15
        } elseif {$columnDataLength < 50} { set columnDataLength 50
          } elseif {$columnDataLength < 100} { set columnDataLength 100
            } elseif {$columnDataLength < 250} { set columnDataLength 250
              } else { set columnDataLength 1000 }
        puts "RESIZED"
        OMBCC '..'
        OMBCC $targetmodule
        OMBALTER TABLE '$v_sourcetable' ADD COLUMN '$columnName' SET PROPERTIES (DATATYPE, LENGTH, NOT_NULL)
                                                                 VALUES ('$columnDataType', $columnDataLength, $columnDataNull)
      } else {
#         number           becomes (no (precision, scale))
#         date             becomes date
          OMBCC '..'
          OMBCC $targetmodule
          OMBALTER TABLE '$v_sourcetable' ADD COLUMN '$columnName' SET PROPERTIES (DATATYPE, NOT_NULL)
                                                                   VALUES ('$columnDataType', $columnDataNull)
        }
      puts "  ADDED COLUMN; DATATYPE: $columnDataType, LENGTH: $columnDataLength, NOT_NULL: $columnDataNull"
      puts $fname "  ADD COLUMN $columnName			$columnDataType		$columnDataLength	$columnDataNullr"
      OMBCC '..'
      OMBCC $sourcemodule
    }
#     Add new attributes toselected table.
#     Set pointer from $sourcemodule to $targetmodule.
      OMBCC '..'
      OMBCC $targetmodule
      puts -nonewline "ADD NEW ATTRIBUTS.... "
      OMBALTER TABLE '$v_sourcetable' ADD COLUMN 'MUTATIE_CDE' SET PROPERTIES (DATATYPE, LENGTH)
                                                               VALUES ('VARCHAR2', 15)
                                      ADD COLUMN 'PROCES_CDE' SET PROPERTIES (DATATYPE, LENGTH)
                                                              VALUES ('VARCHAR2', 30)
                                      ADD COLUMN 'PROCES_DAT' SET PROPERTIES (DATATYPE)
                                                              VALUES ('DATE')
      puts "DONE"
      puts $fname "  ADD COLUMN MUTATIE_CDE	VARCHAR2		15	0r"
      puts $fname "  ADD COLUMN PROCES_CDE	VARCHAR2		30	0r"
      puts $fname "  ADD COLUMN PROCES_DAT	DATE		0	0r"
      puts $fname "r"
      puts -nonewline "COMMITTING.... "
      OMBCOMMIT
      puts "COMMITTED"
  }

Script was accomplished with the help of Ludo de Heus.
Email: l.deheus@dba.nl