Tcl

TCL and its Oracle equivalent, OraTCL are scripting languages.

TCL

Tcl, or Tool Command Language (pronounced "tickle") is a simple, open-source-licensed programming language similar to Perl and PHP. Tcl provides basic language features such as variables, procedures, and control, and it runs on almost any modern OS, including Unix, Linux Windows, MacOS and PDA systems.

TCL scripts are executed with the "tclsh" command interpreter. Look at this example TCL Script:

puts stdout "Please enter your name:"
gets stdin  name
puts stdout "Hello $name"

OraTCL

OraTCL is an Oracle OCI-like extension to the TCL Language. OraTCL allows one to embed SQL and PL/SQL statements in TCL programs and get the results back in strings.

OraTCL was adopted by Oracle Corporation and is used for several of the Enterprise Manager (OEM) Event and Job scripts. OraTCL is installed with the OEM Intelligent Agent, and is thus also available for use by Oracle DBAs and developers.

Executing OraTCP scripts

One can start OraTCL in interactive or batch mode by running the oratclsh command (Unix and Windows) located in your ORACLE_HOME/bin directory.

Interactive mode

$ oratclsh
ORATCLSH for Solaris: Version 9.2.9.4.0 - Production on 08-OCT-2003 21:14:33
(c) Copyright 2003 Oracle Corporation.  All rights reserved.

oratclsh[1]- set x "Look Ma, "
oratclsh[2]- set y "I can write TCL scripts."
oratclsh[3]- puts "$x $y"
Look Ma, I can write TCL script.
oratclsh[4]- exit

Batch mode

Write a TCL script, say, hello.tcl and execute with command "oratclsh hello.tcl":

puts "Hello World...\n"
puts "TCL version = [info tclversion]"
puts "Script Name = [info script]"
exit 0

Connecting to Oracle

One can use the "oralogon" function to establish an Oracle connection and the "oralogoff" function to disconnect from the database. Look at this sample OraTCL script:

set connect scott/tiger@orcl
set lda [oralogon $connect]
if {$oramsg(rc) == 0} {                              # ????
  puts "Successfully connected to Oracle."
} else {
  puts "Unable to connect: $oramsg(rc)"
}
oralogoff $lda

SELECT, INSERT, UPDATE and DELETE data

Look at this example:

set connect scott/tiger@orcl
set lda [oralogon $connect]
set cur [oraopen $lda]
set sql {select tname from tab}

orasql $cur $sql
set row [orafetch $cur]
while {$oramsg(rc) == 0} {
  puts $row
  set row [orafetch $cur]
}

oraclose $cur
oralogoff $lda

Database transactions

The "ORACOMMIT logon-handle" and "ORAROLL logon-handle" function calls can be used to commit or rollback pending transactions.

OraTCL does not autocommit transactions by default. Nevertheless, one can enable or disable AUTOCOMMIT using the following syntax:

oraautocom logon-handle true
oraautocom logon-handle false

Handling database errors

After each OCI command the "rc" variable are set to either 0 for successful execution, or the Oracle server error. OraTcl maintains a Tcl global array called "oramsg" that provides information on Oracle server error messages.

oralogon scott/tiger
if {$oramsg(rc) != ""} {
   puts stderr "Unable to connect to database:"
   puts stderr "  Error Code = ORA-$oramsg(rc)"
   puts stderr "  Error Message = $oramsg(errortxt"
   exit
}

Call stored procedures

One can execute procedures, functions and anonymous PL/SQL blocks from OraTCL using the "oraplexec" function call. Look at this example:

package require Oratcl
set con [oralogon "SCOTT/TIGER@ORCL"]

set plblock {
   begin
      SELECT count(*) into :n
        FROM XQTE.FAC_PARTS_MASTER
       WHERE maker_code = :mcode;
   end;
}

set cur [oraopen $con]
foreach e {"SELF" "BMON12"} {
   oraplexec $cur $plblock :n "" :mcode $e
   set r [orafetch $cur]
   puts "maker=[lindex $r 1] count=[lindex $r 0]"
}
oraclose $cur
oralogoff $con

External links

• The Tcl Developer Xchange - download and learn about TCL
• OraTCL at Sourceforge - Download the OraTCL package
• Tom Poindexter's Tcl Page - Oratcl was originally written by Tom Poindexter