OMB+/OWB Revoke a privilege

OWB has the habbit of only revoking or granting priviliges ont creation of a role or object. If you change a role you have to manually change them. Here is a script to revoke privileges:
 

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

set RoleList [list EVERYONE]
set ProjectList [list ENECO_DWH]
set RevokeList [list COMPILE EDIT FULL_CONTROL]

foreach Project $ProjectList {
OMBCC '/$Project';

set OracleModuleList [OMBLIST ORACLE_MODULES]
foreach OracleModule $OracleModuleList {
put $OracleModule
foreach Role $RoleList {

foreach Revoke $RevokeList {

OMBREVOKE OBJECT_PRIVILEGE $Revoke ON ORACLE_MODULE '$OracleModule' CASCADE FROM ROLE '$Role' ;
    

}
}
OMBCOMMIT;
OMBSAVE;
}
}

Till Next Time

OWB/OMB Granting a privilege

OWB has the habbit of only revoking or granting priviliges ont creation of a role or object. If you change a role you have to manually change them. Here is a script to grant privileges:

###########################################################
#                                                            #
# (C) Ciber Nederland John Minkjan    OMB101.blogspot.com        #
#        V001    Init Version                                #
#        V002    Validated against OWB11gR2                    #
#                                                            #
############################################################
#OMBCONNECT rep_owner/rep_owner@localhost:1521:orcl;

set ProjectList [list ENECO_DWH]
set GrantList [list READ]
set RoleList [list EVERYONE READ_ONLY]

foreach Project $ProjectList {
OMBCC '/$Project';

set OracleModuleList [OMBLIST ORACLE_MODULES]
foreach OracleModule $OracleModuleList {
put $OracleModule
foreach Role $RoleList {

foreach Grant $GrantList {
    OMBGRANT OBJECT_PRIVILEGE $Grant \
    ON ORACLE_MODULE '$OracleModule' \
    CASCADE TO ROLE '$Role' ; 
}
}
OMBCOMMIT;
OMBSAVE;
}
}

Till Next Time

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

OMB Get Repository Documentation

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

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

# 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

Welcome to OMB101

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


Till Next Time