'''[http://oratcl.sourceforge.net%|%Oratcl]''' 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.
** Resources **
[http://sourceforge.net/projects/oratcl/%|%Sourceforge project page]:
author: [Todd M. Helfter]
contact: [Tom Poindexter]
** Attributes **
current version: 4.5
** Download **
[http://sourceforge.net/projects/oratcl/files/oratcl/%|%sourceforge.net]:
** Documentation **
[http://oratcl.sourceforge.net/OraTcl_Users_Guide_and_Reference.pdf%|%User's Guide and Reference]:
[http://oratcl.sourceforge.net/manpage.html%|%man page]:
[http://docs.oracle.com/cd/B10501_01/em.920/a96676/chap3.htm#46317%|%OraTcl Description] ,[http://docs.oracle.com/cd/B10501_01/em.920/a96676/toc.htm%|%Oracle Intelligent Agent User's Guide, release 9.2.0.2]:
[http://download-west.oracle.com/otndoc/oracle9i/901_doc/em.901/a88771/chap4.htm#48632%|%OraTclkkOracle Intelligent Agent User's Guide, release 9.2.0.2]:
[http://www.cab.u-szeged.hu/local/doc/oratcl/oratcl.html%|%Introduction to OraTcl] ,[Tom Poindexter]:
[http://www.nyx.net/~tpoindex/tcl.html%|%Tom Poindexter's Oratcl page]: last updated in 1999
** Press **
[Cinderella Languages Cameron Laird Kathryn Soraiz 1999]:
** See Also **
[VAD]: uses OraTcl
[nstcl]: uses OraTcl internally
[Programming Oracle stored-procedure cursors]:
[oratcl examples]:
[Oratcl Logon Dialog]:
[Plotting data]:
[http://www.idevelopment.info/data/Oracle/DBA_tips/Programming/PROGRAMMING_1.shtml%|%Oracle Programming with Tcl & Oratcl] ,Jeff Hunter:
** Description **
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/10g/11g,
i18n data, and TEA/Stubs as well as Solaris and Windows NT. Oracle has
released a version of its 8.0.5 database product for Linux at the oracle web
site.
[Oratcl] was one of the first, and remains one of the most widely-used,
examples of a third-party language [interacting with databases]. Oracle has
only acknowledged Oratcl's existence though
([http://docs.oracle.com/cd/B10501_01/em.920/a96676/chap3.htm#46317]), always
documenting use of its core [RDBMS] product in terms of [C], its proprietary
PL/SQL, and, in recent years, [Java].
** History **
OraTcl 2.5 was the last version to support Tcl 7.6.
** 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].
** 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-09-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:
======none
user/password@//my.oracle.server:port/my.database.service
======
** 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. :-)
** Installing Oratcl on MacOS X **
[Kroc] 2006-10-18:
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.
** wosql **
older versions of OraTcl included wosql, but it didn't work with newer
versions, so was removed from the distribution. Susan Emma
[https://groups.google.com/forum/#!topic/comp.lang.tcl/sZ9usF61t9A%|%announced]
that she had modified wosql to work with OraTcl 4, but that code is currently
lost.
** Note about orafetch **
[LV] 2008-1117:
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-01-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-01-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: [http://asktom.oracle.com].
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.
----
'''[thelfter] - 2017-09-22 17:01:24'''
I am adding new features to Oratcl.
If you have any feature requests -- please pass them along.
[MHo] 2022-04-13: It´s not so easy to connect to a ''pluggable Database'' (PDB) ''as sysdba''. One can set ORACLE_SID beforehand, or the standard system settings are used, but then you get logged in to the ''Container Databae'' (CDB). And there you have to execute the command ''alter session set container=PDBNAME'' if you want to operate against the PDB. Setting ORACLE_PDB_SID beforehand does not work (saw it somewhere), not in conjunction with oratcl i think).
<<categories>> Package | Database