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