Welcome, guest | Sign In | My Account | Store | Cart

Quick connection to Oracle Database.

Tcl, 137 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
###########################################################
# Connect to Oracle using Type 4 Java driver.
###########################################################

#########################################################  
## Source packages. 
#########################################################  
#
package require java

#################################################################
# putsLog with timestamp.
####################################################################
proc putsLog { a } {

    set host [ info host ]

    set compTime [clock format [clock seconds] -format "%Y-%m-%d-%H.%M.%S"]

    puts "\[$host:$compTime\] $a"

}
#######################################
## Proc - oracleConnect. 
#######################################
proc oracleConnect { serverName databaseName portNumber driverType username password sqlQuery } {

   putsLog "proc - [info level 0 ]"

   # 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.ResultSetMetaData 
   java::import java.sql.DatabaseMetaData 
   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

   putsLog "connection URL is:  $url\n"   
   
   set ConnectionI [ java::call DriverManager getConnection $url ] 

   putsLog "transaction isolation level is [ $ConnectionI getTransactionIsolation ]"

   putsLog "#########################################"
   putsLog "### Database connection details"
   putsLog "#########################################"

   # get the database metadata information.
   #Retrieves a DatabaseMetaData object that contains metadata about the database
   #to which this Connection object represents a connection.

   set DatabaseMetaDataI [ $ConnectionI getMetaData ]

   putsLog [ $DatabaseMetaDataI getDatabaseProductName ]
   putsLog [ $DatabaseMetaDataI getDatabaseProductVersion ]
   putsLog "database version [ $DatabaseMetaDataI getDatabaseMajorVersion ]\.[ $DatabaseMetaDataI getDatabaseMinorVersion ]"
   putsLog "driver version   [ $DatabaseMetaDataI getDriverName ] [ $DatabaseMetaDataI getDriverMajorVersion ]\.[ $DatabaseMetaDataI getDriverMinorVersion ]"
   putsLog "jdbc version     [ $DatabaseMetaDataI getJDBCMajorVersion  ]\.[ $DatabaseMetaDataI getJDBCMinorVersion  ]"
   putsLog "connect username [ $DatabaseMetaDataI getUserName ]"
   putsLog "transaction isolation level is [ $ConnectionI getTransactionIsolation ] \n"

   # get a list of table names in database.
   # if there are no tables the results set is empty.  

   set opt1 [java::field ResultSet TYPE_SCROLL_INSENSITIVE]

   set ResultSetI [ $DatabaseMetaDataI getCatalogs ]

   set ResultSetMetaDataI [ $ResultSetI getMetaData ] 
  
   set columnCount        [ $ResultSetMetaDataI getColumnCount ]

   putsLog "Column Count is $columnCount"

   set i 1

   while { $i <= $columnCount } {

       set columnName [ $ResultSetMetaDataI getColumnName $i ]
      
       lappend columnList $columnName

       incr i 
   }    

   unset i

   putsLog $columnList 

   $ResultSetI  close 
   $ConnectionI close

}
######################################
# Main Control.
######################################

putsLog "executing [info script]"

# make script drive independent.

set drive [lindex [file split [info nameofexecutable]] 0 ] 

set reportFile   C:\\reports\\oracleConnect.txt

set reportFileId [ open $reportFile w ] 

set serverName   xxxx
set databaseName xxxx
set portNumber   1234
set driverType   4
set username     xxxx
set password     xxxx

set sqlQuery "select * from aaaa.bbbb where char(customer) like '287554%'"  

oracleConnect $serverName $databaseName $portNumber $driverType $username $password $sqlQuery 

2 comments

Gaius Hammond 13 years, 7 months ago  # | flag

But why would you do this, when there's Oratcl?

Robert Hicks 12 years, 10 months ago  # | flag

One reason to use this over Oratcl is that you wouldn't need the Oracle client installed. Everything happens with JDBC.

Created by Patrick Finnegan on Tue, 30 Jan 2007 (MIT)
Tcl recipes (162)
Patrick Finnegan's recipes (10)

Required Modules

  • (none specified)

Other Information and Tasks