OMBIMPORT situation?

655771 · Sep 4, 2008 4:14PM

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

zeppo · Sep 5, 2008 12:45PM

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

Carsten Herbe · Sep 5, 2008 7:32AM

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.

zeppo · Sep 5, 2008 11:37AM

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 · Sep 5, 2008 12:45PM

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 · Sep 8, 2008 9:58AM

Wow. This not only answered my question, but gave me some ideas on how to automate some other things. Beautiful script!

-Kenneth

Forum Stats

Quick Links

Categories

OMBIMPORT situation?

Best Answer

Answers

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

Forum Stats

Categories

OMBIMPORT situation?

Best Answer

Answers