OMB+ 101

OMB single quote in tcl

2012-02-17T10:24:00.000-08:00

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.

2012-02-15T01:56:00.000-08:00

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

2012-02-08T05:25:00.001-08:00

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

OMB Set Tablespaces

2012-02-08T04:41:00.001-08:00

############################################################
#                                                          #
# Set Tablespace OWB TABLES                                #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                          #
############################################################
# use connection file
source C:\\OMB_CONNECT.tcl

# Switch ON Prompt
set OMBPROMPT ON;

set projectList [list ********];

foreach project $projectList {
    OMBCC '$project';
    set moduleList [OMBLIST ORACLE_MODULES];
    puts $moduleList;
    foreach module $moduleList {
        OMBCC '$module'
        set tableList [OMBLIST TABLES];
        foreach table $tableList {
        set alterTableCommand [OMBALTER TABLE '$table' SET PROPERTIES (TABLESPACE) VALUES ('USERS')];
        puts alterTableCommand;
        };
        puts $tableList;
        OMBCC '..';
    };

    OMBCC '..';
}

OMBCOMMIT;
OMBDISCONNECT;

File can be found here:

https://sites.google.com/site/filesof101/omb101/OMB_CHANGE_LOCATION.tcl?attredirects=0&d=1

Till Next Time

OMB Change Location

2012-02-08T04:24:00.001-08:00

############################################################
#                                                          #
# Change Location Properties                               #
#                                                          #
# (C) Ciber Nederland John Minkjan                         #
#                     http:\\OMB101.blogspot.com           #
#        V001    Init Version                              #
#        V002    Validated against OWB11gR2                #
#                                                          #
############################################################

# use connection file
source C:\\OMB_CONNECT.tcl

# Switch ON Prompt
set OMBPROMPT ON;

set LocationList [OMBLIST LOCATIONS];

foreach Location $LocationList {
    if [catch {
    set GetLocationType [OMBRETRIEVE LOCATION '$Location' GET PROPERTIES (CONNECTION_TYPE)];
    } errmsg] { set GetLocationType 'UNKNOWN'};
    if {$GetLocationType == "HOST:PORT:SERVICE"} {
        if {$Location != "OWB_REPOSITORY_LOCATION"} {
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (HOST) VALUES ('HOSTNAME')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (PORT) VALUES ('1521')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (SERVICE_NAME) VALUES ('DWH_POC')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (VERSION) VALUES ('11.2')];

            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (USER_NAME) VALUES ('$Location')];
            set alterLocationCommand [OMBALTER LOCATION '$Location' SET PROPERTIES (PASSWORD) VALUES ('********')];
            # PASSWORD isn't always accepted, has to be checked manualy

            OMBCOMMIT;
        }
    };
};
OMBCOMMIT;
OMBDISCONNECT;

Fill can be downloaded here: https://sites.google.com/site/filesof101/omb101/OMB_CHANGE_LOCATION.tcl?attredirects=0&d=1

Till Next Time

OMB Get Repository Documentation

2011-11-11T05:05:00.005-08:00

#########################################################################################################
#                                                                                                        #
# COMPILE OWB Repository                                                                                #
#                                                                                                        #
# Get the Repository documentation                                                                        #
# Output is written to file in RootDir                                                                    #
#                                                                                                        #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com                                                    #
#        V001    Init Version                                                                            #
#        V002    Validated against OWB11gR2                                                                #
#                                                                                                        #
#########################################################################################################
#OMBCONNECT rep_owner/rep_owner@localhost:1521:orcl USE WORKSPACE 'My_Workspace'
#set OMBPROMPT ON

# RootDir
set RootDir "C:\\temp\\"

set ProjectList [OMBLIST PROJECTS];
foreach ProjectName $ProjectList {
if {$ProjectName != "PUBLIC_PROJECT"} {
    set chan [open [string map {\\ /} "$RootDir\\ProjectSummery_$ProjectName.txt"] w];
    set timestamp [clock format [clock seconds]];

        puts $chan "Project Summery: $ProjectName Run: $timestamp";
        puts $chan "";
        set ProjectDescription [OMBRETRIEVE PROJECT '$ProjectName' GET PROPERTIES (DESCRIPTION)];
        puts $chan "Project Description: $ProjectDescription";

        OMBCC '$ProjectName';
        puts $chan "";
        puts $chan "$ProjectName: ORACLE_MODULES";
        puts $chan "";
        set ModuleList [OMBLIST ORACLE_MODULES];
        foreach ModuleName $ModuleList {
            OMBCC '$ModuleName';
            set plural "S"
            set ObjectList [list MAPPING DATA_AUDITOR DIMENSION CUBE TABLE EXTERNAL_TABLE VIEW MATERIALIZED_VIEW SEQUENCE FUNCTION PROCEDURE TABLE_FUNCTION PACKAGE]
            foreach ObjectName $ObjectList {
                set workListName [concat $ObjectName$plural];
                set workList [OMBLIST $workListName];
                foreach workName $workList {
                        set ProjectDescription [OMBRETRIEVE $ObjectName '$workName' GET PROPERTIES (DESCRIPTION)]
                        puts "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                        puts $chan "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                        }
                    ;
                };
            OMBCC '..';
        };

        puts $chan "";
        puts $chan "$ProjectName: FLAT_FILE_MODULES";
        puts $chan "";
        set ModuleList [OMBLIST FLAT_FILE_MODULES];
        foreach ModuleName $ModuleList {
            OMBCC '$ModuleName';
            set plural "S"
            set ObjectList [list FLAT_FILE]
            foreach ObjectName $ObjectList {
                set workListName [concat $ObjectName$plural];
                set workList [OMBLIST $workListName];
                foreach workName $workList {
                    set ProjectDescription [OMBRETRIEVE $ObjectName '$workName' GET PROPERTIES (DESCRIPTION)]
                    puts "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                    puts $chan "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                };
            };
            OMBCC '..';
        };

        puts $chan "";
        puts $chan "$ProjectName: PROCESS_FLOW_MODULES";
        puts $chan "";

        set ModuleList [OMBLIST PROCESS_FLOW_MODULES]
        puts $ModuleList
        foreach ModuleName $ModuleList {
        puts $ModuleName
        OMBCC '$ModuleName';
        set plural "S"
            set ObjectList [list PROCESS_FLOW_PACKAGE]
            foreach ObjectName $ObjectList {
                set workListName [concat $ObjectName$plural];
                set workList [OMBLIST $workListName];
                foreach workName $workList {
                    set ProjectDescription [OMBRETRIEVE $ObjectName '$workName' GET PROPERTIES (DESCRIPTION)]
                    puts "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                    puts $chan "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                };
            };
            OMBCC '..';
        };

        puts $chan "";
        puts $chan "$ProjectName: CALENDAR_MODULES";
        puts $chan "";
        set ModuleList [OMBLIST CALENDAR_MODULES]
        foreach ModuleName $ModuleList {
        OMBCC '$ModuleName';
        set plural "S"
            set ObjectList [list CALENDAR]
            foreach ObjectName $ObjectList {
                set workListName [concat $ObjectName$plural];
                set workList [OMBLIST $workListName];
                foreach workName $workList {
                    set ProjectDescription [OMBRETRIEVE $ObjectName '$workName' GET PROPERTIES (DESCRIPTION)]
                    puts "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                    puts $chan "$ProjectName - $ModuleName - $ObjectName - '$workName' - $ProjectDescription"
                };
            };
        OMBCC '..';
        };

        puts $chan "";
        puts $chan "$ProjectName: CONFIGURATIONS";
        puts $chan "";

        set ModuleList [OMBLIST CONFIGURATIONS];
        foreach ModuleName $ModuleList {
            set ProjectDescription [OMBRETRIEVE CONFIGURATION '$ModuleName' GET PROPERTIES (DESCRIPTION)]
                    puts "$ProjectName - $ModuleName - $ProjectDescription"
                    puts $chan "$ProjectName - $ModuleName - $ProjectDescription"
        };

        OMBCC '..';
    close $chan;
    }
}

OMB VALIDATE Without Deploy

2011-11-09T13:18:00.001-08:00

#########################################################################################################
#                                                                                                        #
# Validate OWB Repository                                                                                #
#                                                                                                        #
# Validate Objects in OWB repository without deploymeny                                                    #
# Output is written in folder set of RootDir                                                            #
#                                                                                                        #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com                                                    #
#        V001    Init Version                                                                            #
#        V002    Validated against OWB11gR2                                                                #
#                                                                                                        #
#########################################################################################################

# RootDir
set RootDir "C:\\temp\\"

# Open Log
set chan [open [string map {\\ /} "$RootDir\\log.txt"] w]
set timestamp [clock format [clock seconds]]
puts $chan $timestamp

# Get the projects lists
set ProjectList [OMBLIST PROJECTS];

foreach ProjectName $ProjectList {
    OMBCC '$ProjectName';
    file mkdir $RootDir$ProjectName
    set ModuleList [OMBLIST ORACLE_MODULES];
        foreach ModuleName $ModuleList {
            OMBCC '$ModuleName';
            file mkdir [concat "$RootDir$ProjectName\\$ModuleName"];
            set plural "S"
            set ObjectList [list TABLE MAPPING FUNCTION PROCEDURE TABLE_FUNCTION PACKAGE DATA_AUDITOR DIMENSION CUBE EXTERNAL_TABLE VIEW MATERIALIZED_VIEW SEQUENCE]

                foreach ObjectName $ObjectList {
                        set workListName [concat $ObjectName$plural];
                        set workList [OMBLIST $workListName];
                        foreach workName $workList {
                        set outputFileName [string map {\\ /} [concat "$RootDir$ProjectName\\$ModuleName\\Validation$ObjectName$plural.txt"]]

                        set isValid [OMBVALIDATE $ObjectName '$workName' OUTPUT VALIDATION_RESULT TO FILE '$outputFileName' WRITE ALL]

                        puts $chan "$ProjectName - $ModuleName - $ObjectName - '$workName' - $isValid"
                        };
                };
            OMBCC '..';
        };

    set ModuleList [OMBLIST FLAT_FILE_MODULES];
        foreach ModuleName $ModuleList {
            OMBCC '$ModuleName';
            file mkdir [concat "$RootDir$ProjectName\\$ModuleName"]
            set plural "S"
            set ObjectList [list FLAT_FILE]
            foreach ObjectName $ObjectList {
                set workListName [concat $ObjectName$plural];
                set workList [OMBLIST $workListName];
                foreach workName $workList {
                    set outputFileName [string map {\\ /} [concat "$RootDir$ProjectName\\$ModuleName\\Validation$ObjectName$plural.txt"]]
                    set isValid [OMBVALIDATE $ObjectName '$workName' OUTPUT VALIDATION_RESULT TO FILE '$outputFileName' WRITE ALL]
                    puts $chan "$ProjectName - $ModuleName - $ObjectName - '$workName' - $isValid"
                };
            };
            OMBCC '..';
        };

    set ModuleList [OMBLIST CONFIGURATIONS];
        foreach ModuleName $ModuleList {
            file mkdir [concat "$RootDir$ProjectName\\$ModuleName"]
            set outputFileName [string map {\\ /} [concat "$RootDir$ProjectName\\$ModuleName\\ValidationConfiguration.txt"]]
                set isValid [OMBVALIDATE CONFIGURATION '$ModuleName' OUTPUT VALIDATION_RESULT TO FILE '$outputFileName' WRITE ALL]
                puts $chan "$ProjectName - Configurations - $ModuleName - $isValid"
        }

    OMBREVERT;
    OMBCC '..';
};
close $chan;

EXPORT Project MDL

2011-11-09T08:13:00.001-08:00

# Connection to Workspace
# OMBCONNECT rep_owner/rep_owner@localhost:1521:orcl USE WORKSPACE 'My_Workspace'
# show the prompt
# set OMBPROMPT ON
# List Projects
#
# (c) Ciber Nederland John Minkjan
#         V001    Validated against OWB11gR2
#
set RootDir "C:\\temp\\"
set projectList [OMBLIST PROJECTS];
foreach projectName $projectList {
    OMBCC '$projectName';
    file mkdir $RootDir$projectName
    puts "exporting $projectName to MDL file"
    set exportFileName [string map {\\ /} [concat "$RootDir$projectName\\$projectName.mdl"]]
    set exportFileLogName [string map {\\ /} [concat "$RootDir$projectName\\$projectName.log"]]
    OMBEXPORT MDL_FILE '$exportFileName' FROM PROJECT '$projectName' OUTPUT LOG TO '$exportFileLogName'
OMBCC '..';
};

Welcome to OMB101

2011-11-09T07:24:00.000-08:00

Welcome to OMB101

I will devote this blog to my travels on the OWB / OMB+ scripting tool path.

Till Next Time