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


# Open the connection
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
}

# Close the connection
proc oracleDisconnect { oraConnect } {
  $oraConnect close
}

# Execute the query
proc oracleQuery { oraConnect oraQuery } {

   set oraStatement [ $oraConnect createStatement ]
   set oraResults [ $oraStatement executeQuery $oraQuery ]

   return $oraResults
}

# Example:
#
#set oraConn [oracleConnect $CHK_HOST $CHK_SERVICE $CHK_PORT $CHK_SCHEMA $CHK_PASSWORD ]
#set sqlStr "select object_name from all_objects where owner = 'STG_OWN' 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