OMB+/OWB create, register, delete en unregister an OWB Location:

How to create, register, delete en unregister an OWB Location:

#####
# CREATE LOCATION
#####

OMBCREATE LOCATION 'SH_LOC' \
SET PROPERTIES \
(BUSINESS_NAME, DESCRIPTION, TYPE, VERSION, CONNECTION_TYPE, USER_NAME,HOST,PORT,SERVICE_NAME)  \
VALUES \
('SH_LOC','Sales History on localhost','ORACLE_DATABASE', '11.2' , 'HOST_PORT_SERVICE','SH','localhost','1521','orcl.world');

OMBCOMMIT;

OMBCONNECT CONTROL_CENTER;

OMBCOMMIT;

OMBALTER LOCATION 'SH_LOC' SET PROPERTIES (PASSWORD)  VALUES ('password');

OMBCOMMIT;

OMBREGISTER LOCATION 'SH_LOC';

OMBCOMMIT;

#####
# DELETE LOCATION
#####

OMBUNREGISTER LOCATION 'SH_LOC';

OMBCOMMIT;

OMBDROP LOCATION 'SH_LOC';

OMBCOMMIT;

Till Next Time

OWB/OMB+ connect to oracle database

Some handy function if you want to query an oracle database directly from OMB+

###########################################################
#                                                         #
# Oracle DataBase Connection                              #
#    Functions to execute select statements on a           #
#   Oracle DB using OMB+ / tcl                            #
#                                                         #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com      #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                         #
###########################################################
# based on orginal code from Michael Broughton aka ZEPPO
# file: OMB_ORCL_MASTER.tcl

package require java

OMB OWB TCL Sorting table partitions

When migrating from OWB10.1 to 11gR2 the  partition order (sometimes) gets scrambeld and the table will not validate.  This scripts sets the partitions back in (alphabetical) order .     

[script]
###########################################################
#                                                         #
# Sort Table Partitions                                   #
#                                                          #
# Note: When migrating from OWB10.1 to 11gR2 the          #
#       partition order gets scrambeld and the table      #
#       will not validate                                 #
#       This scripts sets the partitions back in          #
#       alphabetical order                                #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com      #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                         #
###########################################################

set ProjectList [list ********]
foreach Project $ProjectList {
    OMBCC '/$Project'
    set OracleModuleList [OMBLIST ORACLE_MODULES]
        foreach OracleModule $OracleModuleList {
        puts $OracleModule
        OMBCC '/$Project/$OracleModule';
        set TableList [OMBLIST TABLES];
        foreach Table $TableList {
            set PartitionList [OMBRETRIEVE TABLE '$Table' GET PARTITIONS];
            if { $PartitionList != ""} {
                puts "-- $Table "
                set PartitionListS [lsort $PartitionList]
                if { $PartitionList != $PartitionListS} {
                    set partno 1
                    foreach Partition $PartitionListS {
                        OMBALTER TABLE '$Table' MODIFY PARTITION '$Partition' MOVE TO POSITION $partno
                        puts "$OracleModule - $Table - $Partition  - $partno"
                        set partno [expr $partno + 1 ]
                    }
                    OMBCOMMIT;
                }
            }
        }
    }
}
[\script]

OMB OWB TCL Mapping set maximum number of errors

By default the maximum number of errors in a mapping is 50..... Here is a script to change it to 0:
############################################################
#                                                          #
# Change Maximum Number Of Errors                          #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           # 
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
# orignal idea: Borkur Steingrimsson                       #
#                                                          #
############################################################
#Disconnect if connected
catch {
    OMBDISCONNECT;
    };
   
OMBCONNECT OWB_OWN/******@***********:1521:DWH_A2
set OMBPROMPT ON

set projectList [OMBLIST PROJECTS]
foreach projectName $projectList {
    OMBCC '$projectName'
    puts "Project: $projectName"
    set moduleList [OMBLIST ORACLE_MODULES]
    foreach moduleName $moduleList {
        puts "Process: $moduleName"
        OMBCC '$moduleName'
        set mappingList [OMBLIST MAPPINGS]
        foreach mappingName $mappingList {
            puts "Altering: $moduleName/$mappingName"
            OMBALTER MAPPING '$mappingName' SET PROPERTIES (MAXIMUM_NUMBER_OF_ERRORS) VALUES ('0')
        };
    OMBCC '..'
    };
    OMBCC '..'
};
(Kudos to Borkur: http://www.rittmanmead.com/2007/07/a-little-bit-of-omb-goes-a-long-way/)

Till Next Time

OMB single quote in tcl

The other day I was fighting to get a single quote, a double quote and a single qoute within a string. After trying a number of escape sequences David Allen handed me the solution on OTN:
https://forums.oracle.com/forums/thread.jspa?threadID=2347623&tstart=0
Just use two single quotes for one ....

Till Next Time

OMB OWB TCL Repair NULL.

During the migration constants with the value NULL where migration as 'NULL' (with quotes). I create a script to replace them trough the whole repository with TO_CHAR(NULL).
############################################################
#                                                          #
# Set Expression OWB Repair NULL                           #
#                                                          #
# During Migration from OWB10.1 to 11gR2 an Expression     #
# NULL is migrated as 'NULL' making it a VARCHAR(4)        #
# This script repairs it for all Operators called:         #
# - C_STANDAARD                                               #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           # 
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                          #
############################################################

#Disconnect if connected
catch {
    OMBDISCONNECT;
    };
   
# Connect
OMBCONNECT ****/*******@*******:1521:OWB_POC \
USE  WORKSPACE '*******';

# Set OMBPROMPT
set OMBPROMPT ON;

# Set ProjectList
set ProjectList [list ********];
foreach Project $ProjectList {
    OMBCC '$Project'
    set ModuleList [OMBLIST ORACLE_MODULES]
    #set ModuleList [list DM_DNI]
    foreach Module $ModuleList {
        OMBCC '$Module';
        set MappingList [OMBLIST MAPPINGS]
        foreach Mapping $MappingList {
            set OperatorList [OMBRETRIEVE MAPPING '$Mapping' GET OPERATORS];
            foreach Operator $OperatorList {
                    if {$Operator == "C_STANDAARD"} {
                        puts  "$Module - $Mapping - $Operator - ";
                        set GroupsList [OMBRETRIEVE MAPPING '$Mapping' \
                                            OPERATOR '$Operator' \
                                            GET GROUPS];
                        foreach Group $GroupsList {
                            puts  "$Module - $Mapping - $Operator - $Group ";
                            set AttributeList [OMBRETRIEVE MAPPING '$Mapping' \
                                                OPERATOR '$Operator' \
                                                GROUP '$Group' \
                                                GET ATTRIBUTES]
                            foreach Attribute $AttributeList {
                                puts  "$Module - $Mapping - $Operator - $Group - $Attribute ";
                                set Expression [OMBRETRIEVE MAPPING '$Mapping' \
                                                OPERATOR '$Operator' \
                                                GROUP '$Group' \
                                                ATTRIBUTE '$Attribute' \
                                                GET PROPERTIES (EXPRESSION)]
                                puts "Current Expression: $Expression"
                                if {$Expression  == "\'NULL\'"} {
                                    puts "BINGO"
                                    puts [OMBALTER MAPPING '$Mapping' \
                                            MODIFY ATTRIBUTE '$Attribute' \
                                            OF GROUP '$Group' \
                                            OF OPERATOR '$Operator'  \
                                            SET PROPERTIES (EXPRESSION) VALUES('TO_CHAR(NULL)')]
                                    set Expression [OMBRETRIEVE MAPPING '$Mapping' \
                                                    OPERATOR '$Operator' \
                                                    GROUP '$Group' \
                                                    ATTRIBUTE '$Attribute' \
                                                    GET PROPERTIES (EXPRESSION)]
                                    puts "Current Expression: $Expression"
                                    OMBCOMMIT;
                                }
                                };
                            };
                        };
                    };
            };
        OMBCC '..';
        };
    OMBCOMMIT;
    OMBCC '..';
    };   
OMBSAVE;


Download found here: Repair NULL

Till Next Time

OMB OWB “CURRENT” Constants

Some nice constants to use in your scripts:

• $OMB_CURRENT_PROJECT: Stores the name of the current project.

• $OMB_CURRENT_SESSION: Stores the identifier of the current session.

• $OMB_CURRENT_USER: Stores the user ID of the current user.

Till Next Time