Version 60 of Oratcl

Updated 2009-10-07 21:52:48 by RLH

INTRO

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: 
        The Oratcl package is a dynamic extension of the Tcl language that integrates Oracle OCI 
        calls into a set of Tcl commands that allow Oracle access via Tcl. 

        Starting in May 2000, Todd Helfter became the new maintainer of the Oratcl package. Currently 
        two versions of Oratcl are supported. Oratcl 4 for use with Oracle 8i, 9i and 10g, and the older 
        Oratcl 3 for use with Oracle 7 and 8.0.x. 

        Please visit the project page for feature requests, bug reports and general help requests.

        Currently the version is: 4.5

 Updated: 10/07/2009
 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.

There is now a short manual [L1 ] on using Oratcl.Thanks Todd!


Oratcl Tips

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


Oratcl binary data tips

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.


Oratcl dependance on Oracle

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."


Installing Oratcl on Windows

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.


Oratcl programming style question

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"
 }

Intro to Oratcl reference

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.

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. :-)


elfring 27 Aug 2003 The package "nstcl-database" [L3 ] uses a database driver for Oracle.


Installing Oratcl on MacOS X

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


Oracle 10 and OraTcl

Note that Oracle 10 brings, at least on some platforms, another wrinkle. For me, on a SPARC Solaris 9 system, Oracle 10 defaults to 64 bit libraries. If you are using ActiveTcl or something similar, you will find that oratcl was compiled as a 32 bit interface. This means that you need to use the environment varirable:

# For 32 bit tcl on 64 bit solaris with 64 bit oracle.
export ORACLE_LIBRARY=$ORACLE_HOME/lib32/libclntsh.so

so that oratcl can be loaded. This is not a bug in oratcl. It is the mechanism that Oracle provides for someone to use the 32 bit libraries. You just need to know whether you are using a 32 bit or a 64 bit tcl so that you get the correct shared libraries.


Oratcl tool sought

Has anyone written a Tcl/Tk tool that provides one with the ability to browse (and perhaps update) oracle table data?

osalcescu - 2008-06-24 - please check Tom's wisqlite application, it may be what you're looking for. http://www.nyx.net/~tpoindex/wisqlite.html

The graphic is interesting. I didn't see the code there, so I don't know how it works in general. Looking on this wiki, I only see references to it being related to sybtcl. Looking in the sybtcl and oratcl code distributions, I see the code in the sybtcl distribution, but I don't see a comparable version for oratcl. The code distributed with sybtcl is very sybase specific - it isn't a matter of just changing the package require statement. I could probably go through and rewrite it to work with oratcl, but right now I don't have the available time to do that. Thanks for the pointer though.

escargo - The link for wisqlite does not show how to get the application, plus the page claims to have last been revised in 1995. Tom's home page there [L4 ] says it was last revised in 1999, and has no link to wisqlite.

aec - 2008-06-25 - I found an old distribution of oratcl which contains oddis and wosql in the examples subdirectory which may be what you are seeking. Goto ftp://tcl.activestate.com/pub/tcl/mirror/ftp.procplace.com/sorted/databases/oratcl-2.5/index.html and get the oratcl-2.5.tar.gz archive file.

LV Thanks. With that name, I was able to track down a copy. I actually found a development branch of oratcl 2.7 which still had a version of wosql in it. I wonder whether anyone has done anything with it in the past few years.

osalcescu - 2008-06-26 - Well, I did develop, some time ago, a more basic utility. Nothing that fancy. If you're interested I can pass you the code, could be your start-up coding boost. Or not... :) Leave an email address here if you're interested.

escargo - Aren't there two programs mentioned here, which are distinct: wisqlite (for which no source has been identified), and wosql, which has been found again? Based on the name, I would have thought that wisqlite was a client SQLite, nor Oracle.

LV wisqlite is unrelated to Oratcl. It was, in fact, created before SQLite was created! The name is, I suspect, a play on the idea of a light weight "w"indowing "sq" "lite" weight program, probably designed more for conveying the idea of what someone could do rather than intended to be a full fledged production quality application. Tom provided, as a part of the sybtcl distribution, the original application. When I went looking for wosql, on some web site I ran across a note that implied that code was basically a port of wisqlite to oratcl, and thus the name "w"indowing "o"racle "sql" wosql. TP LV is pretty much correct concerning the naming wisqlite and wosql. Sybase's command line utility was/is called isql, and Sybase had a Windows version called wisql. I thought my tool for Sybtcl was lighter and simpler, thus the name wisqlite.

LV Note that wosql's history comments seem to indicate that the last release was back in the mid to late 1990s. If you don't have older oratcl's installed on your system, you may find problems. If you do have older ones, you will have to experiment with versions to find out which one is the one you need.


Note about orafetch

LV 2008 Nov 17 In moving from oratcl 4.2 to 4.4, I noticed a difference in behavior in one of my programs. Turns out that in my program I used:

orafetch ... -command { name }

In my program, the name proc returned without specifying an argument. In oratcl 4.2, the program worked fine. In oratcl 4.4, the return code from orafetch appears to have become the return value from name. I changed name to return 0 instead of nothing, and my program worked again.

In my case, this was a better coding style anyways, so it worked out well. Just wanted to be certain that anyone else encountering peculiar behavior thinks of this.


LV 2009-Jan-21 I have a situation for which I'm looking for a solution.

I have two data sources - one, a flat text file, containing deliminated fields of information. The first field is an item identifier.

The second data source is an oracle table, where each column corresponds to the fields from the flat file and the rows should correspond to each line.

What I am wanting to end up with is a program that reports items from the flat file that are not found in the table, and items in the table which are not found in the flat file.

I supposed I could dynamically generate a select statement with a where col_name not in (val1, val2, ...., val2000) type statement, but I was wondering if anyone had other ideas of ways to approach the situation.

Harm Olthof - 2009-Jan-22 There are quite a few solutions from the Oracle side. (1) you can import the flat file into your database, using sqlloader; (2) From Oracle version 9.x and higher you can also link it, using the concept of "External Tables"; (3) Oracle also has a [webdav] solution, but this not generally made available by the dba-er. I think [ftp] is also possible; (4) You could convert your flat file to xml and then there are more sophisticated possibilities; All of these possibilities expose the flat file as a table and then you can do a "..where not exists.." query. Oracle has a kind of combination of our wiki and a newsgroup, run by Tom Kyte on: [L5 ]. If you go there and search for things like sqlloader, external tables, csv, flat file etc. you'll find a lot of explanations and examples. No [Tcl] solution. It would be great if someone wrote a tcl Oracle Cartridge so we could use tcl instead of pl/sql.


See also