Purpose: Discuss the Tcl <-> Oracle DBMS binding What: Oracle extension to Tcl Where: http://oratcl.sf.net/ http://sf.net/projects/oratcl/ ftp://tcl.activestate.com/pub/tcl/nightly-cvs/ http://www.oracle.com/technology/software/products/database/oracle10g/index.html ftp://ftp.procplace.com/pub/tcl/sorted/packages-7.6/databases/oratcl-2.5/oratcl-2.5.tar.gz ftp://ftp.procplace.com/pub/tcl/sorted/packages-8.0/distrib/cvs-oratcl.tar.gz http://people.a2000.nl/hkooiman/Oratcl http://technet.oracle.com/ Description: Provide access to a Oracle (versions through 6-9) Database server from within Tcl. OraTcl 3 and up supports Tcl 8 (tclX recommended), and supports Tcl 8.x, includes Windows NT DLLs, cursor variables from PL/SQL, can bind Tcl variables to orafetch results, can bind Tcl variables to Oracle :bind variables, supports asynchronous SQL execution. It supports Tcl/Tk 8.x, Oracle 7/8/8i/9i, i18n data, and TEA/Stubs as well as Solaris and Windows NT. OraTcl 2.5 was the last version to support Tcl 7.6. The a2000 site has information relating to a Macintosh port of Oratcl. Oracle has released a version of its 8.0.5 database product for Linux at the oracle web site. Currently the version is 4.1 . Updated: 06/2005 Contact: mailto:tmh@jumpgate.cc.purdue.edu (Todd M. Helfter) mailto:tpoindex@nyx.net (Tom Poindexter) http://oratcl.sf.net/ is the home page for this package binding Oracle to Tcl. Insert your favorite Oratcl tips, tricks, and hints, etc. here. Also, point to various useful reading, such as [BOOK Tcl/Tk Tools], or [BOOK Oracle and Open Source]. "Cinderella languages" [http://sunsite.uakom.sk/sunworldonline/swol-08-1999/swol-08-regex.html] is a published article on Oratcl's origins. ''([escargo] - replaced link as of 8 Nov 2006)'' [Tom Poindexter] created Oratcl. [Todd M. Helfter] currently maintains it. Tom Poindexter's Oratcl page http://www.nyx.net/~tpoindex/tcl.html has a little bit about Oratcl, but the primary work on the extension is being done by Todd via the [SourceForge] project. [VAD], [nstcl] are tools that know about oratcl. ---- Also add here information about tips for managing [Unicode], images, etc. from within Oracle tables. One thing that was discovered is that one needs to set something called the NLS before starting oracle and attempting to deal with UTF-8 data. This can be done using the NLS_LANG environment variable, or using something like SQLPLUS's '''alter session set nls_territory''' and '''alter session set nls_language'' to appropriate values. Apparently, as long as NLS_LANG is set to some valid language, oracle then handles a localization sweep over the data properly before handing the data back to the requestor. Then, you do a call to encoding convertfrom utf-8 $string and off you go. ---- Todd points out that, "OraTcl relies on the Oracle install. This means different things on different platforms. On Unix, the ORACLE_HOME environment variable must point to a valid Oracle install. On windows, the ociw32.lib file must be found in the WINDOWS search path. In the past, I have had to place the oracle directory in the windows PATH variable in autoexec.bat. In later releases, the registry information was sufficient to find the file." ---- ''Laurent Riesterer, 2005/9/23'' The easiest way to install the required library (tested on Windows) is to use the "Oracle Instant Client" setup. You just need to unzip the content of one file in a folder (about 80 MB), add this folder to you path and then you can start to use [Oratcl]. You don't need any configuration file, just use a fully qualified name in your connection string: user/password@//my.oracle.server:port/my.database.service ---- Tcl has other connections to [COMPANY: Oracle]. ---- [tksql] is an application for editing [PostgreSQL] (''not'' Oracle) tables. ---- [LV] For which version of Oratcl is this template - I seem to recall that at version changes, the paradigm used by oratcl programs had to change... A recent article on comp.lang.tcl by [Kevin Rodgers] asked for comments about this sample boilerplate: # For error reporting: set program [file tail $argv0] # Package interface: package require Oratcl # Connect to the $env(TWO_TASK) database as USER with PASSWORD: if [catch {oralogon "USER/PASSWORD"} ora_logon] { puts stderr "$program: $ora_logon" exit 1 } if [catch {oraopen $ora_logon} ora_statement] { oralogoff $ora_logon puts stderr "$program: $ora_statement" exit 1 } #if [catch {oraconfig $ora_statement fetchrows 1024} ora_error] { # puts stderr "$program: $ora_error" #} # Execute SQL statement: set sql "SELECT column_1, ... column_N FROM ... WHERE ..." # Note that for Oratcl 4.x, the $oramsg references have to change to # [oramsg $ora_statement rc] if [catch {orasql $ora_statement $sql} ora_error] { puts stderr "$program: $ora_error" } elseif {$oramsg(rc) != 0} { puts stderr "$program: $oramsg(errortxt)" } else { # Process each row with column_I bound to var_I: while {$oramsg(rc) == 0} { if [catch {orafetch $ora_statement \ {... $var_1 ... $var_N ...} \ '@' var_1 1 ... var_N N} \ ora_error] { puts stderr "$program: $ora_error" break } elseif {$oramsg(rc) == 1403} { break } elseif {$oramsg(rc) != 0} { puts stderr "$program: $oramsg(errortxt)" break } } } # Disconnect from the $env(TWO_TASK) database: if [catch {oraclose $ora_statement ora_error] { puts stderr "$program: $ora_error" } if [catch {oralogoff $ora_logon ora_error] { puts stderr "$program: $ora_error" } ---- See also http://www.dbcorp.com/downloads/ORATCL.ppt for an intro to Oratcl (and Oracle's OEM) from 2000. Addition of pointers to other Oratcl tutorials - particular current ones - would be greatly appreciated. ---- [elfring] ''27 Aug 2003'' The package "[nstcl]-database" [http://nstcl.sourceforge.net/] uses a database driver for [Oracle]. ---- [RLH]: I would like to second the request for tutorials and example pointers. I am new to Tcl and while I can use Perl/DBI to do what I want, I would rather use Tcl. :-) ---- [Kroc] - 18 Oct. 2006 : OraTcl relies on the Oracle install but that's not so easy to install Oracle client on Mac OS X. Here are the steps I followed to get something that works: '''1) Download Oracle client 8.1.7:''' The file to get is '''Oracle 8i v8.1.7.1 OCI / Mac OS X''' from this page: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/macsoft.html (you must register to get it but it's free). Uncompress '''MacOSX_8171.cpio''' and you'll get 3 files: MacOSX_8.1.7.1_OCI_Demo.zip, Oracle_8.1.7.1_Client.zip et Release_Notes.zip. '''2) Client installation:''' Uncompress Oracle_8.1.7.1_Client.zip then move '''Oracle_8.1.7.1_Client''' directory in your home. Then you must edit '''~/Oracle_8.1.7.1_Client/network/admin/tnsnames.ora''' to fit your server parameters. At the end, it should be something like this: MYBASE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.119)(PORT = 1521)) ) (CONNECT_DATA = (SID = MYBASE) ) ) '''3) Environment variable:''' At least one environment variable, '''ORACLE_HOME''', must be set before loading [OraTcl]: set ::env(ORACLE_HOME) [file normalize ~/Oracle_8.1.7.1_Client] Tested on Mac OS X 10.4.8 with OraTcl 4.4 ---- [Programming Oracle stored-procedure cursors] ---- [[ [Category Package] | [Category Database] ]]