Version 31 of Oratcl

Updated 2007-01-11 18:05:03 by LV

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: See the sf.net web site
        mailto:[email protected] (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" [L1 ] 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. (escargo 10 Jan 2007 - The tcl.html file referenced dates from the previous century....)

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" [L2 ] 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 ]