From Oracle FAQ
Jump to: navigation, search

TCL and its Oracle equivalent, OraTCL are scripting languages.


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 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[edit]

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[edit]

$ oratclsh
ORATCLSH for Solaris: Version - 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[edit]

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[edit]

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


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[edit]

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[edit]

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"

Call stored procedures[edit]

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 {
      SELECT count(*) into :n
       WHERE maker_code = :mcode;

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[edit]