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

OMB Set Tablespaces

############################################################
#                                                          #
# 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

 

############################################################
#                                                          #
# 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