Forum Stats

  • 3,826,280 Users
  • 2,260,619 Discussions
  • 7,896,865 Comments

Discussions

OMBIMPORT situation?

655771
655771 Member Posts: 3
edited Sep 8, 2008 9:58AM in Warehouse Builder
When you are in the OWB Design Center, you can explore down through your project, into a module, and right click on Views. If you click Import..., you are taken to the Import Metadata Wizard. If I select only View, and then press Next, I am shown a list of Views that I can import. I select one and click Next again, then Finish. The View is now available for use within Design Center (to build mappings, etc.)

I need to automate this process, and have been working with OMB Plus as a solution to automating other things within OWB. My first inclination was to try the OMBIMPORT command, but frankly the documentation is a little cryptic to me on this particular thing. (And I have successfully automated Mapping Creation).

Has anyone done this before? Am I barking up the wrong tree with OMB Plus?

Thanks,
-Kenneth
Tagged:

Best Answer

  • zeppo
    zeppo Member Posts: 374
    Answer ✓
    Another try.....
    #FILE: loadviews.tcl
    
    package require java
    
    ##################################################################################
    #
    # Basic Connection Details
    #
    ##################################################################################
    
    # OWB Repository Connection
    set OWB_DEG_USER    username
    set OWB_DEG_PASS    password
    set OWB_DEG_HOST    myserver
    set OWB_DEG_PORT    1555
    set OWB_DEG_SRVC    orcl
    set OWB_DEG_REPOS   owb_mgr
    
    set SPOOLFILE "c:/omb/logfile.txt"
    
    
    ##################################################################################
    #
    # Procedures from our standard OWB library
    #
    ##################################################################################
    
    ##################################################################################
    # Default logging function.
    #  Accepts inputs: LOGMSG - a text string to output
    #                  FORCELOG - if "1" then output regardless of VERBOSE_LOG setting
    ##################################################################################
    proc log_msg { LOGTYPE LOGMSG } {
       global SPOOLFILE
    
       set fout [open "$SPOOLFILE" a+]      
       puts $fout "$LOGTYPE:-> $LOGMSG"
       puts "$LOGTYPE:-> $LOGMSG"
       close $fout
    }
    
    
    ##################################################################################
    # Default rollbabk and exit with error code function.
    ##################################################################################
    proc exit_failure { msg } {
    
       log_msg ERROR "$msg"
       log_msg ERROR "Rolling Back....."
    
       exec_omb OMBROLLBACK
    
       log_msg ERROR "Exiting....."
    
       # return and also bail from calling function
       return -code 2
    }
    
    ##################################################################################
    # Generic wrapper for OMB+ calls
    ##################################################################################
    proc exec_omb { args } {
    
       # log_msg OMBCMD "$args"
    
       # the point of this is simply to return errorMsg or return string, whichever is applicable,
       # to simplify error checking using omb_error{}
    
       if [catch { set retstr [eval $args] } errmsg] {
          log_msg OMB_ERROR "$errmsg"
          log_msg "" ""
          return $errmsg
       } else {
       #   log_msg OMB_SUCCESS "$retstr"
       #   log_msg "" ""
          return $retstr
       }
    
    }
    
    
    ##################################################################################
    # Generic test for errors returned from OMB+ calls
    ##################################################################################
    proc omb_error { retstr } {
       # OMB, Oracle, or java errors may have caused a failure.
       if [string match OMB0* $retstr] {
          return 1
       } elseif [string match ORA-* $retstr] {
          return 1
       } elseif [string match java.* $retstr] {
          return 1
       } else {
          return 0
       }
    }
    
    
    
    ##################################################################################
    #
    # Procedures from our standard OWB/SQL library
    #
    ##################################################################################
    
    proc oracleConnect { serverName databaseName portNumber username password } {
    
       # import required classes 
       java::import java.sql.Connection
       java::import java.sql.DriverManager
       java::import java.sql.ResultSet
       java::import java.sql.SQLWarning
       java::import java.sql.Statement
       java::import java.sql.CallableStatement
       java::import java.sql.ResultSetMetaData 
       java::import java.sql.DatabaseMetaData 
       java::import java.sql.Types
       java::import oracle.jdbc.OracleDatabaseMetaData
    
       # load database driver .
       java::call Class forName oracle.jdbc.OracleDriver 
    
       # set the connection url.
       append url jdbc:oracle:thin
       append url :
       append url $username
       append url /
       append url $password
       append url "@"
       append url $serverName
       append url :
       append url $portNumber
       append url :
       append url $databaseName
    
       set oraConnection [ java::call DriverManager getConnection $url ] 
       set oraDatabaseMetaData [ $oraConnection getMetaData ]
       set oraDatabaseVersion [ $oraDatabaseMetaData getDatabaseProductVersion ]
    
       puts "Connected to: $url"
       puts "$oraDatabaseVersion"
       
       return $oraConnection 
    }
    
    
    proc oracleDisconnect { oraConnect } {
      $oraConnect close
    }
    
    proc oracleQuery { oraConnect oraQuery } {
    
       set oraStatement [ $oraConnect createStatement ]
       set oraResults [ $oraStatement executeQuery $oraQuery ]
    
       return $oraResults
    }
    
    
    ##################################################################################
    #
    #                MAIN SCRIPT BODY
    #
    ##################################################################################
    
    proc load_view { viewname modulename} {
    
        exec_omb  OMBCREATE TRANSIENT IMPORT_ACTION_PLAN 'IMPORT_VIEW' ADD ACTION 'IMPORT_ACTION' SET REF SOURCE VIEW '$viewname' SET REF TARGET ORACLE_MODULE '$modulename'
        exec_omb  OMBIMPORT FROM METADATA_LOCATION FOR IMPORT_ACTION_PLAN 'IMPORT_VIEW' 
        exec_omb  OMBDROP IMPORT_ACTION_PLAN 'IMPORT_VIEW'
    }    
    
    
    
    ##################################################################################
    #  Connect to repos
    ##################################################################################
    log_msg LOG "Connecting to Repository"    
    
    set print [exec_omb OMBCONNECT $OWB_DEG_USER/[email protected]$OWB_DEG_HOST:$OWB_DEG_PORT:$OWB_DEG_SRVC USE REPOSITORY '$OWB_DEG_REPOS']
    
    if [omb_error $print] {
        if [string match  OMB01041* $print] {
            log_msg LOG "Already connected ....."
        } else {
            log_msg ERROR "Unable to connect to repository."
            log_msg ERROR "Exiting Script.............."
            return 
        }
    } else {
        log_msg LOG "Connected to Repository"    
    }
    
    ##################################################################################
    # Connect to project
    ##################################################################################
    
    puts -nonewline "Which project do you want to import to? "
    set CHK_PROJECT_NAME [gets stdin]
    
    set print [exec_omb OMBCC '$CHK_PROJECT_NAME']
    
    if [omb_error $print] {
    
       log_msg LOG "Project $CHK_PROJECT_NAME does not exist. No Import Required...."
       exec_omb OMBDISCONNECT
       return
      
    } else {
       log_msg LOG "Verified project $CHK_PROJECT_NAME exists"
    }    
    
    
    puts -nonewline "Which module do you want to import to? "
    set ORA_MODULE_NAME [gets stdin]
    
    set print [exec_omb OMBCC '$ORA_MODULE_NAME']
    
    if [omb_error $print] {
    
       log_msg LOG "Module $ORA_MODULE_NAME does not exist. No Import Required...."
       exec_omb OMBDISCONNECT
       return
      
    } else {
       log_msg LOG "Verified module $ORA_MODULE_NAME exists"
    }    
    
    exec_omb OMBCC '..'
    
    set CURRENT_DEPLOYED_LOCATION [exec_omb OMBRETRIEVE ORACLE_MODULE '$ORA_MODULE_NAME' GET REFERENCE LOCATION]
    if [omb_error $print] {
       log_msg LOG "Unable to retrieve location to match top . Exiting...."
       exec_omb OMBDISCONNECT
       return
      
    } else {
       log_msg LOG "Retrieved location $CURRENT_DEPLOYED_LOCATION"
    }    
    
     set CHK_SCHEMA   [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (SCHEMA)]
     set CHK_HOST     [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (HOST)]
     set CHK_PORT     [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (PORT)]
     set CHK_SERVICE  [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (SERVICE)]
    
     set CHK_PASSWORD   [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (PASSWORD)]
     if [string match "{}" $CHK_PASSWORD] {
        #Password not stored in repository
        log_msg LOG " "
        puts -nonewline "Require password for schema $CHK_SCHEMA? "
        set CHK_PASSWORD [gets stdin]
     }   
    
    
    ##################################################################################
    # Validate to Control Center 
    ##################################################################################
    log_msg LOG "Connecting to Control Center " 
    set print [exec_omb OMBCONNECT CONTROL_CENTER USE '$CHK_PASSWORD' ]
    if [omb_error $print] {
        exec_omb OMBROLLBACK
        log_msg ERROR "Unable to connect to Control Center "
        log_msg ERROR "$print" 
        exit_failure "Exiting Script.............."
    }
    exec_omb OMBCOMMIT
    
    
    log_msg LOG "Checking existing MetaData." 
    set print [exec_omb OMBALTER LOCATION '$CURRENT_DEPLOYED_LOCATION' SET PROPERTIES (PASSWORD) VALUES ('$CHK_PASSWORD')]
    exec_omb OMBCOMMIT
    
    exec_omb OMBCC '$ORA_MODULE_NAME'
    
    log_msg LOG "Making SQL*Plus Connection...." 
    
    
    set oracle_view_lst {}
    
    log_msg LOG "Getting views defined in database...." 
    log_msg LOG " " 
    
    set oraConn [oracleConnect $CHK_HOST $CHK_SERVICE $CHK_PORT $CHK_SCHEMA $CHK_PASSWORD ]
    set sqlStr "select object_name from all_objects where owner = '$CHK_SCHEMA' and  object_type = 'VIEW'"
    set oraRs [oracleQuery $oraConn $sqlStr]
    while {[$oraRs next]} {
         set vwName [$oraRs getString object_name]
         lappend oracle_view_lst $vwName
    }  
    $oraRs close
    $oraConn close
    
    
    foreach viewname $oracle_view_lst {
         log_msg LOG "Checking view $viewname ....  "
    
         #see if view already in OWB. An OMBRETRIEVE will error out if it doesn't exist.
         set owb_column_lst [ exec_omb OMBRETRIEVE VIEW '$viewname' GET COLUMNS ]
    
         if [omb_error $owb_column_lst] {
              log_msg LOG "Need to import view $viewname "
              exec_omb OMBCC '..'
              load_view $viewname $ORA_MODULE_NAME     
              exec_omb OMBCC '$ORA_MODULE_NAME'
         } else {
              log_msg LOG "View $viewname  already in OWB"
         } 
         
    }
    
    
    log_msg LOG " " 
    log_msg LOG "Done Views. Exiting..." 
    log_msg LOG " " 
    
    exec_omb OMBDISCONNECT

Answers

  • Carsten Herbe
    Carsten Herbe Member Posts: 638
    Hi Kenneth,

    first you have to build an import plan
    OMBCREATE TRANSIENT IMPORT_ACTION_PLAN 'MY_PLAN' ADD ACTION 'MY_ACTION' SET REF SOURCE VIEW 'MY_VIEW' SET REF TARGET ORACLE_MODULE '/CHE/SOURCE_DB'
    then you may actually import the objects
    OMBIMPORT FROM METADATA_LOCATION FOR IMPORT_ACTION_PLAN 'MY_PLAN' 
    Good luck,
    Carsten.
    Carsten Herbe
  • zeppo
    zeppo Member Posts: 374
    edited Sep 5, 2008 11:50AM
    barking up the wrong tree? Heck no! OMB is a huge timesaver once you get a bunch of scripts

    Try this one (untested - use at own risk - you need to set connection info and repoint log file to a valid directory)

    I think you will find lots of usefull OMB+ stuff in there ;)

    Cheers,
    Mike


    Sorry, I had to delete the script. ORacle's new markup tags for the text editor kpt assuming that valid OMB+ syntax was markup, and the rich text editor keeps crapping out.


    Thpppppppppppppppp!

    Edited by: zeppo on Sep 5, 2008 8:49 AM
  • zeppo
    zeppo Member Posts: 374
    Answer ✓
    Another try.....
    #FILE: loadviews.tcl
    
    package require java
    
    ##################################################################################
    #
    # Basic Connection Details
    #
    ##################################################################################
    
    # OWB Repository Connection
    set OWB_DEG_USER    username
    set OWB_DEG_PASS    password
    set OWB_DEG_HOST    myserver
    set OWB_DEG_PORT    1555
    set OWB_DEG_SRVC    orcl
    set OWB_DEG_REPOS   owb_mgr
    
    set SPOOLFILE "c:/omb/logfile.txt"
    
    
    ##################################################################################
    #
    # Procedures from our standard OWB library
    #
    ##################################################################################
    
    ##################################################################################
    # Default logging function.
    #  Accepts inputs: LOGMSG - a text string to output
    #                  FORCELOG - if "1" then output regardless of VERBOSE_LOG setting
    ##################################################################################
    proc log_msg { LOGTYPE LOGMSG } {
       global SPOOLFILE
    
       set fout [open "$SPOOLFILE" a+]      
       puts $fout "$LOGTYPE:-> $LOGMSG"
       puts "$LOGTYPE:-> $LOGMSG"
       close $fout
    }
    
    
    ##################################################################################
    # Default rollbabk and exit with error code function.
    ##################################################################################
    proc exit_failure { msg } {
    
       log_msg ERROR "$msg"
       log_msg ERROR "Rolling Back....."
    
       exec_omb OMBROLLBACK
    
       log_msg ERROR "Exiting....."
    
       # return and also bail from calling function
       return -code 2
    }
    
    ##################################################################################
    # Generic wrapper for OMB+ calls
    ##################################################################################
    proc exec_omb { args } {
    
       # log_msg OMBCMD "$args"
    
       # the point of this is simply to return errorMsg or return string, whichever is applicable,
       # to simplify error checking using omb_error{}
    
       if [catch { set retstr [eval $args] } errmsg] {
          log_msg OMB_ERROR "$errmsg"
          log_msg "" ""
          return $errmsg
       } else {
       #   log_msg OMB_SUCCESS "$retstr"
       #   log_msg "" ""
          return $retstr
       }
    
    }
    
    
    ##################################################################################
    # Generic test for errors returned from OMB+ calls
    ##################################################################################
    proc omb_error { retstr } {
       # OMB, Oracle, or java errors may have caused a failure.
       if [string match OMB0* $retstr] {
          return 1
       } elseif [string match ORA-* $retstr] {
          return 1
       } elseif [string match java.* $retstr] {
          return 1
       } else {
          return 0
       }
    }
    
    
    
    ##################################################################################
    #
    # Procedures from our standard OWB/SQL library
    #
    ##################################################################################
    
    proc oracleConnect { serverName databaseName portNumber username password } {
    
       # import required classes 
       java::import java.sql.Connection
       java::import java.sql.DriverManager
       java::import java.sql.ResultSet
       java::import java.sql.SQLWarning
       java::import java.sql.Statement
       java::import java.sql.CallableStatement
       java::import java.sql.ResultSetMetaData 
       java::import java.sql.DatabaseMetaData 
       java::import java.sql.Types
       java::import oracle.jdbc.OracleDatabaseMetaData
    
       # load database driver .
       java::call Class forName oracle.jdbc.OracleDriver 
    
       # set the connection url.
       append url jdbc:oracle:thin
       append url :
       append url $username
       append url /
       append url $password
       append url "@"
       append url $serverName
       append url :
       append url $portNumber
       append url :
       append url $databaseName
    
       set oraConnection [ java::call DriverManager getConnection $url ] 
       set oraDatabaseMetaData [ $oraConnection getMetaData ]
       set oraDatabaseVersion [ $oraDatabaseMetaData getDatabaseProductVersion ]
    
       puts "Connected to: $url"
       puts "$oraDatabaseVersion"
       
       return $oraConnection 
    }
    
    
    proc oracleDisconnect { oraConnect } {
      $oraConnect close
    }
    
    proc oracleQuery { oraConnect oraQuery } {
    
       set oraStatement [ $oraConnect createStatement ]
       set oraResults [ $oraStatement executeQuery $oraQuery ]
    
       return $oraResults
    }
    
    
    ##################################################################################
    #
    #                MAIN SCRIPT BODY
    #
    ##################################################################################
    
    proc load_view { viewname modulename} {
    
        exec_omb  OMBCREATE TRANSIENT IMPORT_ACTION_PLAN 'IMPORT_VIEW' ADD ACTION 'IMPORT_ACTION' SET REF SOURCE VIEW '$viewname' SET REF TARGET ORACLE_MODULE '$modulename'
        exec_omb  OMBIMPORT FROM METADATA_LOCATION FOR IMPORT_ACTION_PLAN 'IMPORT_VIEW' 
        exec_omb  OMBDROP IMPORT_ACTION_PLAN 'IMPORT_VIEW'
    }    
    
    
    
    ##################################################################################
    #  Connect to repos
    ##################################################################################
    log_msg LOG "Connecting to Repository"    
    
    set print [exec_omb OMBCONNECT $OWB_DEG_USER/[email protected]$OWB_DEG_HOST:$OWB_DEG_PORT:$OWB_DEG_SRVC USE REPOSITORY '$OWB_DEG_REPOS']
    
    if [omb_error $print] {
        if [string match  OMB01041* $print] {
            log_msg LOG "Already connected ....."
        } else {
            log_msg ERROR "Unable to connect to repository."
            log_msg ERROR "Exiting Script.............."
            return 
        }
    } else {
        log_msg LOG "Connected to Repository"    
    }
    
    ##################################################################################
    # Connect to project
    ##################################################################################
    
    puts -nonewline "Which project do you want to import to? "
    set CHK_PROJECT_NAME [gets stdin]
    
    set print [exec_omb OMBCC '$CHK_PROJECT_NAME']
    
    if [omb_error $print] {
    
       log_msg LOG "Project $CHK_PROJECT_NAME does not exist. No Import Required...."
       exec_omb OMBDISCONNECT
       return
      
    } else {
       log_msg LOG "Verified project $CHK_PROJECT_NAME exists"
    }    
    
    
    puts -nonewline "Which module do you want to import to? "
    set ORA_MODULE_NAME [gets stdin]
    
    set print [exec_omb OMBCC '$ORA_MODULE_NAME']
    
    if [omb_error $print] {
    
       log_msg LOG "Module $ORA_MODULE_NAME does not exist. No Import Required...."
       exec_omb OMBDISCONNECT
       return
      
    } else {
       log_msg LOG "Verified module $ORA_MODULE_NAME exists"
    }    
    
    exec_omb OMBCC '..'
    
    set CURRENT_DEPLOYED_LOCATION [exec_omb OMBRETRIEVE ORACLE_MODULE '$ORA_MODULE_NAME' GET REFERENCE LOCATION]
    if [omb_error $print] {
       log_msg LOG "Unable to retrieve location to match top . Exiting...."
       exec_omb OMBDISCONNECT
       return
      
    } else {
       log_msg LOG "Retrieved location $CURRENT_DEPLOYED_LOCATION"
    }    
    
     set CHK_SCHEMA   [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (SCHEMA)]
     set CHK_HOST     [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (HOST)]
     set CHK_PORT     [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (PORT)]
     set CHK_SERVICE  [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (SERVICE)]
    
     set CHK_PASSWORD   [OMBRETRIEVE LOCATION '$CURRENT_DEPLOYED_LOCATION' GET PROPERTIES (PASSWORD)]
     if [string match "{}" $CHK_PASSWORD] {
        #Password not stored in repository
        log_msg LOG " "
        puts -nonewline "Require password for schema $CHK_SCHEMA? "
        set CHK_PASSWORD [gets stdin]
     }   
    
    
    ##################################################################################
    # Validate to Control Center 
    ##################################################################################
    log_msg LOG "Connecting to Control Center " 
    set print [exec_omb OMBCONNECT CONTROL_CENTER USE '$CHK_PASSWORD' ]
    if [omb_error $print] {
        exec_omb OMBROLLBACK
        log_msg ERROR "Unable to connect to Control Center "
        log_msg ERROR "$print" 
        exit_failure "Exiting Script.............."
    }
    exec_omb OMBCOMMIT
    
    
    log_msg LOG "Checking existing MetaData." 
    set print [exec_omb OMBALTER LOCATION '$CURRENT_DEPLOYED_LOCATION' SET PROPERTIES (PASSWORD) VALUES ('$CHK_PASSWORD')]
    exec_omb OMBCOMMIT
    
    exec_omb OMBCC '$ORA_MODULE_NAME'
    
    log_msg LOG "Making SQL*Plus Connection...." 
    
    
    set oracle_view_lst {}
    
    log_msg LOG "Getting views defined in database...." 
    log_msg LOG " " 
    
    set oraConn [oracleConnect $CHK_HOST $CHK_SERVICE $CHK_PORT $CHK_SCHEMA $CHK_PASSWORD ]
    set sqlStr "select object_name from all_objects where owner = '$CHK_SCHEMA' and  object_type = 'VIEW'"
    set oraRs [oracleQuery $oraConn $sqlStr]
    while {[$oraRs next]} {
         set vwName [$oraRs getString object_name]
         lappend oracle_view_lst $vwName
    }  
    $oraRs close
    $oraConn close
    
    
    foreach viewname $oracle_view_lst {
         log_msg LOG "Checking view $viewname ....  "
    
         #see if view already in OWB. An OMBRETRIEVE will error out if it doesn't exist.
         set owb_column_lst [ exec_omb OMBRETRIEVE VIEW '$viewname' GET COLUMNS ]
    
         if [omb_error $owb_column_lst] {
              log_msg LOG "Need to import view $viewname "
              exec_omb OMBCC '..'
              load_view $viewname $ORA_MODULE_NAME     
              exec_omb OMBCC '$ORA_MODULE_NAME'
         } else {
              log_msg LOG "View $viewname  already in OWB"
         } 
         
    }
    
    
    log_msg LOG " " 
    log_msg LOG "Done Views. Exiting..." 
    log_msg LOG " " 
    
    exec_omb OMBDISCONNECT
  • 655771
    655771 Member Posts: 3
    Wow. This not only answered my question, but gave me some ideas on how to automate some other things. Beautiful script!

    -Kenneth
This discussion has been closed.