Oratcl
NAME
Oratcl - Oracle Database Server access commands for Tcl
INTRODUCTION
Oratcl is an extension to the Tcl language designed to pro-
vide access to an Oracle Database Server. Each Oratcl com-
mand generally invokes several Oracle Call Interface (OCI)
library functions. Programmers using Oratcl should be fami-
liar with basic concepts of OCI programming. Oratcl is
loaded into the Tcl interpreter with the tcl
package com-
mand.
Example
package require Oratcl
package require -exact Oratcl 4.5
ORATCL COMMANDS
oralogon connect-
str ?-
async? ?-
failovercallback procname?
Connect to an Oracle server using
connect-
str. The
connect string should be a valid Oracle connect string,
in the form:
name/
password
name/
password@
n:
dbname
If using system authentication, the connect string
should be in the form:
/
/@
dbname
If connecting as SYSOPER or SYSDBA, the connect string
should be in the form:
sysdba
sysoper
A logon handle is returned and should be used for all
other Oratcl commands using this connection that
require a logon handle. Multiple connections to the
same or different servers are allowed.
Oralogon raises
a Tcl error if the connection is not made for any rea-
son (login or password incorrect, network unavailable,
etc.). If the connect string does not include a data-
base specification, a connection will be established
with the server specified in the environment variable
ORACLE_SID.
The optional -
async argument specifies that all com-
mands allowing asynchronous (nonblocking) operation
will do so. The commands affected are:
oraparse,
oraexec,
orafetch and those that make use of these such
as
orasql,
orabindexec and
oraplexec. These commands
will immediately return a code of OCI_STILL_EXECUTING
which is equal to the numeric value of -3123. Repeat
calls to these commands with the same arguments until
a return value of OCI_SUCCESS, a 0, is returned. See
the section below on ASYNCHRONOUS TRANSACTION HANDLING.
The optional -
failovercallback procname arguments
provide a TAF failover functionality to Oratcl. The
given procname is invoked on a TAF failover. It is
often used to re-execute "alter session" statements
after the automatic reconnect to another RAC node.
oralogoff logon-
handle
Logoff from the Oracle server connection associated
with
logon-
handle.
Logon-
handle must be a valid handle
previously opened with
oralogon.
Oralogoff raises a
Tcl error if the logon handle specified is not open.
Oralogoff closes any open statement handles opened with
logon-
handle.
Oralogoff returns the return code from
OCISessionEnd().
oraopen logon-
handle
Open a statement handle to the server.
Oraopen returns
a handle to be used on subsequent Oratcl commands that
require a statement handle.
Logon-
handle must be a
valid handle previously opened with
oralogon. Multiple
statement handles can be opened through the same or
different logon handles.
Oraopen raises a Tcl error if
the logon handle specified is not open.
oraconfig statement-
handle ?
name ?
value??
Configure a
statement-
handle or retrieve configuration
information about a
statement-
handle. If no arguments
are provided,
oraconfig returns a list of
name-
value
pairs. If
name is provided, the associated
value will
be returned. If both
name and
value are provided,
oraconfig will configure the
statement-
handle as
directed. For numeric
values, a
value less than or
equal to zero or greater than the stated maximum will
cause
Oratcl to raise a TCL error.
Name may be one of
the following:
longsize Sets or returns the maximum amount of LONG or
LONG RAW data returned by
orafetch. The
default is 40960 bytes and the maximum is
2147483647 bytes.
bindsize Sets or returns the size of the buffer used
for storing bind variable values. This is
used in
orabindexec and
oraplexec to allow
the buffer to be reused on subsequent calls.
The default is 2000 bytes and the maximum is
4000 bytes.
nullvalue Sets or returns the NULL value behavior. A
value of "default" causes
orafetch to substi-
tute zeros for NULLs in numeric columns and
null strings "{}" for NULLs in character
columns. Any other
value causes that
value
to be returned as a string for all NULLs.
The default is "default".
fetchrows Sets or returns the number of rows pre-
fetched by
orafetch.
Orafetch attempts to
fetch
fetchrows rows from the Oracle server
at once and then returns single rows until
the cache is exhausted and another set of
rows is retrieved. Setting the
fetchrows to
larger numbers for queries that return many
rows may dramatically decrease the time spent
fetching the rows. Changes to
fetchrows only
affects subsequent
orasql commands.
Fetchrows defaults to 10 rows and the maximum
is dependent upon available memory.
lobpsize Sets or returns the amount of of data (in
characters) used in piecewise reads and
writes to LOB types in the
oralob command.
Lobpsize defaults to 10,000 characters and
the maximum is 15,000 characters.
longpsize Sets or returns the amount of of data (in
characters) used in piecewise reads and
writes to LONG types in the
oralong command.
Longpsize defaults to 50,000 characters and
the maximum is 2,147,483,648 characters.
utfmode Sets or returns the UTF translation behavior.
Setting this value to true causes
orasql,
orabindexec,
oraplexec,
orafetch,
oralong and
oralob to perform UTF translation on values
written to and read from the database with
the system encoding. It is not recommended
that this function be enabled when reading or
writing long raw type values with
oralong.
The default is false.
numbsize Sets or returns the amount of of data (in
characters) used to represent a number column.
Numbsize defaults to 40 characters and the maximum
is 4000 characters.
datesize Sets or returns the amount of of data (in
characters) used to represent a date column.
Datesize defaults to 75 characters and the maximum
is 7500 characters.
oraclose statement-
handle
Closes the cursor associated with
statement-
handle.
Oraclose raises a Tcl error if the
statement-
handle
specified is not open.
oramsg handle option
all returns all values as a list in the format
{rc error rows peo ocicode sqltype}
rc returns the result code of the last OCI
library function called by Oratcl with this
handle. This code is a numeric value that
often corresponds to an Oracle error code.
Refer to the Oracle Error Messages and Codes
manual for more detailed information. Typi-
cal values are:
0 - Function completed normally
1403 - End of data in
orafetch command
1406 - Fetched column was truncated.
-3123 - Asynchronous command still processing
error returns the message text associated with the
return code.
rows returns the number of rows affected by an
insert,
update,
delete statement by
oraexec,
or the number of rows fetched to date by
orafetch.
peo returns the parse error offset, an index
position in an SQL string that failed to
parse due to a syntax error.
ocicode returns the OCI code of the last OCI function
called by Oratcl. See the OCI manual for
descriptions.
sqltype returns a code set by last SQL or PL/SQL
parsed with
oraparse. Valid values are:
1 == SELECT corresponds to OCI_STMT_SELECT
2 == UPDATE corresponds to OCI_STMT_UPDATE
3 == DELETE corresponds to OCI_STMT_DELETE
4 == INSERT corresponds to OCI_STMT_INSERT
5 == CREATE corresponds to OCI_STMT_CREATE
6 == DROP corresponds to OCI_STMT_DROP
7 == ALTER corresponds to OCI_STMT_ALTER
8 == BEGIN corresponds to OCI_STMT_BEGIN
9 == DECLARE corresponds to OCI_STMT_DECLARE
arraydml_errors returns a list of errors that occurred
during an array dml operation. Each element of the list
will contain the row the error occurred on, the sql
error code, and the sql error tesxt.
Oramsg raises a Tcl error if the
handle cannot be
located in the set of open statement-handles and con-
nected logon-handle.
oraparse statement-
handle statement-
text
Parse the statement
statment-
text on the Oracle server.
Statement-
handle must be a valid handle previously
opened with
oraopen.
Statement-
text can be either a
SQL or anonymous PL/SQL statement. the
Statement-
text
may contain bind variables that begin with a colon ':'.
Oraparse will return the numeric return code "0" on
successful parsing of the
statement-
text, and the
error code returned by Oracle when parsing fails.
Ora-
parse raises a Tcl error if the
statement-
handle speci-
fied is not open, or if the
statment-
text is syntacti-
cally incorrect.
orabind statement-
handle ?
-arraydml? ?:
varname value ...?
Bind values SQL variables in a previously parsed SQL
statement.
Statement-
handle must be a valid handle pre-
viously opened with
oraopen. An SQL or PL/SQL state-
ment must have previously been parsed by executing
ora-
parse.
Orabind may be executed repeatedly on a previ-
ously parsed statement. Binding should only be done in
conjunction with sql types (1-4) select, insert, update,
delete and with the PL/SQL types (8-9) begin and
declare type statements.
Optional :
varname value pairs allow substitutions on
SQL bind variables. As many :varname value pairs
should be specified as there are defined in the previ-
ously parsed SQL statement. Varnames must be prefixed
by a colon ":". It is not an error to call
orabind
without any :
varname value pairs, but no binding will
occur.
When using the
-arraydml option, each value should
contain a list of values. Each :varname value pair
should have the same number of elements in their lists.
The
orabind command will raise an error if they are not.
The
-arraydml option can only be used for updates and
inserts.
Orabind will return "0" when bindings are successful;
"1003" if a previous SQL has not been parsed with
ora-
parse; "1008" if not all SQL bind variables have been
specified. Refer to Oracle error numbers and messages
for other possible values.
Orabind raises a Tcl error if the
statement-
handle
specified has not b
oraexec statement-
handle ?-
commit?
Execute a previously parsed and optionally bound SQL
statement.
Statement-
handle must be a valid handle pre-
viously opened with
oraopen. An SQL statement must
have previously been parsed by executing
oraparse.
Orabind and
oraexec commands may be repeatedly issued
after a statement is parsed.
The optional -
commit argument specifies the that SQL
will be committed upon successful execution.
orafetch statement-
handle ?options ...?
Retrieve data from the database as specified by prior
oraparse,
orabind,
oraxec calls. All values are
converted to character strings except ref_cursors which
will be represented in the datavariable list as a null
string.
Orafetch returns the code from the OCIStmtExe-
cute() library function. Likely values include 0 for
success, 1403 for no more data, and -3123 for asynchro-
nous still executing.
-
datavariable Specifies the variable to be set with a
list containing the row of data
fetched. The list returned in the data-
variable by
orafetch contains the
values of the selected columns in the
order specified by
select.
-
dataarray Specifies the array in which the indi-
vidual columns of data fetched will be
set.
-
indexbyname When combined with the -
dataarray
option,
orafetch will use the column
names from the query as the index
(hash) values of the array.
-
indexbynumber When combinded with the -
dataarray
option,
orafetch will use the column
position number from the query as the
index (hash) values of the array.
-
command Specifies a script to eval when
orafetch retrieves a row of data. This
script may reference the variable and
array specified by other options.
Orafetch raises a Tcl error if the
statement-
handle
specified is not open, or if an unknown option is
specified.
orasql statement-
handle sql-
statement ?-
parseonly? ?-
commit?
Execute the SQL statement
sql-
statement on the Oracle
server.
Statement-
handle must be a valid handle previ-
ously opened with
oraopen.
Orasql will return the
numeric return code "0" on successful execution of the
sql-
statement.
The optional -
parseonly argument causes
orasql to parse
but not execute the SQL statement. The SQL statement
may contain bind variables that begin with a colon
(':'). The statement may then be executed with the
orabindexec command, allowing bind variables to be sub-
stituted with values. Bind variables should only be
used for SQL statements
select, insert, update, or
delete.
The optional -
commit argument specifies the that SQL
will be committed upon successful execution.
Orasql raises a Tcl error if the statement handle
specified is not open, or if the SQL statement is syn-
tactically incorrect.
Table inserts made with orasql should follow conversion
rules in the Oracle SQL Reference manual.
orabindexec statement-
handle ?-
commit? ?:
varname value ...?
Execute a previously parsed SQL statement, optionally
binding values to SQL variables.
Statement-
handle must
be a valid handle previously opened with
oraopen. An
SQL statement must have previously been parsed by exe-
cuting
oraparse or
orasql with the -
parseonly option.
Orabindexec may be repeatedly executed after a state-
ment is parsed with bind variables substituted on each
execution.
Orabindexec does not re-parse SQL state-
ments before execution.
The optional -
commit argument specifies that the SQL
will commit upon successful execution.
Optional :
varname value pairs allow substitutions on
SQL bind variables before execution. As many :varname
value pairs should be specified as there are defined in
the previously parsed SQL statement. Varnames must be
prefixed by a colon ":".
Orabindexec will return "0" when the SQL is executed
successfully; "1003" if a previous SQL has not been
parsed with orasql; "1008" if not all SQL bind vari-
ables have been specified. Refer to Oracle error
numbers and messages for other possible values.
oraplexec statement-
handle pl-
block ?:
varname value ...?
Execute an anonymous PL block, optionally binding
values to PL/SQL variables.
Statement-
handle must be a valid handle previously
opened with
oraopen.
Pl-
block may either be a complete
PL/SQL procedure or a call to a stored procedure coded
as an anonymous PL/SQL block. Optional :
varname value
pairs may follow the pl-block. Varnames must be pre-
fixed by a colon ":", and match the subtsitution bind
names used in the procedure. Any :varname that is not
matched with a value is ignored. If a :varname is used
for output, the value should be coded as a null list,
{}.
Ref-cursor variables may be returned from a PL/SQL
block by specifying an open
statement-
handle as the bind
value for a :varname bind variable. The handle must
have previously been opened by
oraopen using the same
logon-
handle as the cursor used to execute the
ora-
plexec command. After
oraplexec completes, the handle
may be used to fetch result rows by using orafetch;
column information is available by using
oracols.
Oraplexec will return "0" when executed successfully;
Use the command
orafetch to retrieve the bind results.
Oraplexec raises a Tcl error if the cursor handle
specified is not open, or if the PL/SQL block is in
error.
oralob sub-
command handle ?
options ...?
Perform operations on Oracle Long Objects.
Handle must be either a valid statement handle previ-
ously opened with
oraopen or a LOB handle created with
the
alloc sub-command. Both Binary Long Object (BLOB)
and Character Long Object (CLOB) columns are supported
by the
oralob command.
The following
sub-
commands are available:
alloc statement-
handle -
table $
table -
column
$
column -
rowid $
rowid
Create and return a LOB handle that refers to
the LOB specified by ($
table, $
column,
$
rowid).
statement-
handle must be a state-
ment handle previously created with
oraopen
and will be used implicitly by the other
oralob sub-commands that operate on this LOB.
free LOB-
handle
Destroy the LOB handle and free any resources
associated with it.
read LOB-
handle -
datavar varname
Read the LOB specified by
LOB-
handle into the
variable identified by
varname.
varname
substr LOB-
handle -
start $
start -
stop $
stop -
datavar
Reads characters of the LOB specified by
LOB-
handle, beginning at $
startpos and ending
at $
stoppos, into
varname. $
startpos and
$
stoppos both default to 0.
write LOB-
handle -
datavar varname
Write the data in the variable identified by
varname into the LOB specified by
LOB-
handle.
writeappend LOB-
handle -
datavar varname
Append the data in the variable identified by
varname to the end of the LOB specified by
LOB-
handle.
append LOB-
handle1 LOB-
handle2
Appends the contents of the LOB specified by
LOB-
handle2 to the LOB specified by
LOB-
handle1. Both LOBs must be of the same
type (Binary or Character).
erase LOB-
handle -
start $
start -
stop $
stop
Overwrites the data in the LOB specified by
LOB-
handle from $
start to $
stop with NULL
characters. $
start and $
stop both default to
0.
trim LOB-
handle -
length $
length
Trims the LOB specified by
LOB-
handle to
$
length characters or bytes.
length LOB-
handle
Returns the length (in characters or bytes)
of the LOB specified by
LOB-
handle.
instr LOB-
handle -
pattern $
pattern -
start $
start
-
nth $
nth
Returns the position in the LOB specified by
LOB-
handle at which the $
nth occurrence of
the pattern $
pattern appears. The search is
started at $
start. $
start defaults to 0 and
$
nth defaults to 1.
compare LOB-
handle1 LOB-
handle2 -
start1 $
start1
-
start2 $
start2 -
length $
length
Compares the two LOBs specified by
LOB-
handle1 and
LOB-
handle2. The comparison
is begun at the position indicated by $
start1
(in LOB 1) and $
start2 (LOB 2) and continues
for $
length positions. A return value of 0
indicates that the two LOBs are identical
through the positions specified. A non-zero
return value indicates that the two LOBs
differ.
The
oralob commands are a collection of TCL and
anonymous PL/SQL wrappers for the Oracle
dbms_lob
PL/SQL package and therefore require the
rowid (as well
as the table name and column name) of the LOB in order
to operate. The
rowid of a row may be determined
easily, as shown in the example below.
LOB Example
# Assume that $sth is a valid statement-handle
# opened earlier
oraparse $sth "select rowid from my_table \
where my_key = 'keyvalue'"
oraexec $sth
orafetch $sth -datavariable rowid
set data "abcdeabcdeabcde"
set lobid [oralob alloc $sth -table "my_table" \
-column "clob_col" -rowid $rowid]
oralob write $lobid -datavar data
set l [oralob length $lobid]
# $l == 15
set data ""
oralob read $lobid -datavar data
# $data contains "abcdeabcdeabcde"
set i [oralob instr $lobid -pattern "eab" -start 3 -nth 2]
# $i == 9 -- TCL-like indexing, not Oracle indexing
NOTE:
The PL/SQL DBMS_LOB package used by the
oralob
command requires BLOB and CLOB fields to be ini-
tialized before they may be operated upon. You
may automatically initialize a LOB field by using
EMPTY_BLOB() or EMPTY_CLOB() in the DEFAULT clause
of a table definition or initialize before use by
inserting an EMPTY_BLOB() or EMPTY_CLOB().
Table Definition Example:
create table test_lob_1 (
lob_key
varchar2(10) primary key,
lob_clob clob default empty_clob(),
lob_blob blob default empty_blob()
)
Initialize Before Use Example:
# Assume the following table definition:
# create table test_lob_2 (
# lob_key
varchar2(10),
# lob_clob clob,
# lob_blob blob
# )
set sql { \
insert into test_lob_2 \
(lob_key, lob_clob, lob_blob) \
values (:lob_key, empty_clob(), empty_blob() )\
}
orasql $sth $sql -parseonly
# Create a new row in test_lob_2 with lob_clob
# and lob_blob properly initialized.
orabindexec $sth :lob_key "AAAAAAAAAA"
oralong sub-
command handle ?
options ...?
Perform operations on Oracle LONG column-types.
Handle must be either a valid statement handle
previously opened with
oraopen or a LONG handle
created with the
alloc sub-command. Both LONG and
LONG RAW columns are supported by the
oralong com-
mand.
The following
sub-
commands are available:
alloc statement-
handle -
table $
table -
column
$
column -
rowid $
rowid
Create and return a LONG handle that refers
to the LONG specified by ($
table, $
column,
$
rowid).
statement-
handle must be a state-
ment handle previously created with
oraopen
and will be used implicitly by the other
ora-
long sub-commands that operate on this LONG.
free LONG-
handle
Destroy the LONG handle and free any
resources associated with it.
read LONG-
handle -
datavar varname
Read the LONG specified by
LONG-
handle into
the variable identified by
varname.
write LONG-
handle -
datavar varname
Write the data in the variable identified by
varname into the LONG specified by
LONG-
handle.
LONG Example
# Assume that $sth is a valid statement-handle
# opened earlier with logon handle $lda
set chr_data [string repeat 0123456789---------- 10000]
# Find the ROWID for the LONG handle
oraparse $sth {select rowid from oratcl_long \
where field = 'value'}
oraexec $sth
orafetch $sth -datavariable rowid
set longid [oralong alloc $sth -table oratcl_long \
-column mp3 -rowid $rowid]
oralong write $longid -datavar chr_data
oracommit $lda
oralong read $longid -datavar out_data
oralong free $longid
if {[string equal $chr_data $out_data]} {
puts "write/read results are equal"
}
oracols statement-
handle ?
option?
Return the names of the columns from the last
orasql,
orafetch, or
oraplexec command as a Tcl list. The
oracols may be used after
oraplexec, in which case the
bound variable names are returned. The
option parame-
ter can be used to alter the result as follows:
all returns all values as a list of lists in the
format {{name size type precision scale nul-
lok} {...}}
name returns a list of column names. This is the
default.
size returns a list of column sizes.
type returns a list of column types.
precision returns a list of column precisions.
scale returns a list of column scales.
nullok returns a list of column "NULLOK" values.
"NULLOK" will be 1 if the column may be NULL,
or 0 otherwise.
The
oracols command raises a Tcl error if the
statement-
handle specified is not open. The
oracols
command raises a Tcl error if the
option is not valid.
oradesc logon-
handle table-
name
Describes the columns of
table-
name. Returns a list
containing lists in the format {name size type preci-
sion scale nullok} for each column of the table.
Ora-
desc will also describe the columns of a table refer-
enced by a private or public synonym when given the
name of a synonym as the
table-
name argument.
Oradesc
will describe an object in the connecting schema before
describing a public synonym when the names are the
same.
oracommit logon-
handle
Commit any pending transactions from prior
orasql, ora-
bindexec, or
oraplexec commands that use a statement
handle opened through the connection specified by
logon-
handle.
Logon-
handle must be a valid handle pre-
viously opened with
oralogon.
Oracommit raises a Tcl
error if the
logon-
handle specified is not open.
oraroll logon-
handle
Rollback any pending transactions from prior
orasql,
orabindexec, or
oraplexec commands that use a statement
handle opened through the connection specified by
logon-
handle.
Logon-
handle must be a valid handle pre-
viously opened with
oralogon.
Oraroll raises a Tcl
error if the
logon-
handle specified is not open.
oraautocom logon-
handle boolean
Enables or disables automatic commit of SQL data mani-
pulation statements using a statement handle opened
through the connection specified by
logon-
handle.
Logon-
handle must be a valid handle previously opened
with
oralogon.
Boolean may be any value that evaluates
to boolean true (1, on, true) to enable automatic com-
mit, or boolean false to disable. After setting the
automatic commit status,
oraautocom returns the new
commit status (1 for on, 0 for off) for validation pur-
poses. The automatic commit feature defaults to "off".
Oraautocom raises a Tcl error if the
logon-
handle
specified is not open.
oraldalist
Return a list of all opened logon-handles.
orastmlist logon-
handle
Return a list of all opened statement-handles associ-
ated with the
logon-
handle.
orainfo option ?
args?
Retrieves information about oratcl.
Option may be
either "version", "server", or "logonhandle".
"
orainfo version" returns the current oratcl version.
"
orainfo server" requires a valid logon handle previously opened
with
oralogon as an argument and returns the oracle
server information.
"
orainfo client" returns the oracle client version information. I.E. 10.2.0.1.0
"
orainfo status" requires a valid logon handle previously opened
with
oralogon as an argument and returns the oracle server
connection status (1 connected, 0 not connected).
"
orainfo logonhandle" requires a
statement handle previously opened with
oraopen and
returns the login handle that the statement handle was
opened under.
EXAMPLES
puts [orainfo version]
set lda [oralogon username/password@db]
puts [orainfo server $lda]
set sth [oraopen $lda]
set mylda [orainfo logonhandle $sth]
NOTES
Tcl errors can also be raised by any Oratcl command if a
command's internal calls to OCI library routines fail.
Dates
Oracle is very particular about using date literals in
SQL. Date literals should match the default date for-
mat for your oracle session. default date formats can
be modified with an "alter session" SQL statement.
alter session set nls_date_format = 'DD-MON-YYYY'
Connection Limits
The limit of the number of simultaneous connection han-
dles and statement handles is determined by the Oracle
server configuration. The processes
init.
ora is the
most common limitation.
Multithreading
Oratcl may be used with the thread extension.
Slave Interpreters
Oratcl may be used in a Tcl slave interpreter. How-
ever, logon handles and statement handles are only
accessible from the interpreter in which they are
created. The test suite provides examples of slave
interpreter interaction.
Long and Long Raw
The maximum amount of LONG or LONG RAW data returned by
orafetch is ultimately dependent on Oratcl's ability to
malloc()
maxlong bytes of memory for each LONG/LONG RAW
column retrieved. Configuring maxlong to too high a
value may cause core dumps or memory shortages.
Ref Cursor
Ref-cursor variables returned by oraplexec must be
specified as a currently open statement handle from the
same logon connection:
set lda [oralogon scott/tiger]
set exec_cur [oraopen $lda]
set fetch_cur [oraopen $lda]
set plsql { begin
open :fetchcur for select empno, ename
from emp where job = :job ;
end;
}
oraplexec $exec_cur $plsql :job ANALYST :fetchcur $fetch_cur
orafetch $fetch_cur -arrayvariable dbres -indexbyname
while {[oramsg $fetch_cur rc] == 0} {
puts "$dbres(EMPNO) $dbres(ENAME)"
orafetch $fetch_cur -arrayvariable dbres -indexbyname
}
Bind Variables
Using SQL bind variables is more efficient than letting
Oracle reparse SQL statements. Use a combination of
oraparse / orabind / oraexec:
set sql "insert into name_tab(first_name) values(:firstname)"
oraparse $cur $sql
foreach name [list Ted Alice John Sue] {
orabind $cur :firstname $name
oraexec $cur
}
rather than:
foreach name [list Ted Alice John Sue] {
set sql "insert into name_tab(first_name) values('$name')"
oraparse $cur $sql
oraexec $cur
}
ASYNCHRONOUS TRANSACTION PROCESSING
set lda [oralogon scott/tiger -async]
set sth [oraopen $lda]
set sql {select empno, ename from emp where job = :job}
#parse phase
while {[oraparse $sth $sql] == $::oratcl::codes(OCI_STILL_EXECUTING)} {
...
process other events
...
}
#bind phase
orabind $sth :job ANALYST
#execution phase
while {[oraexec $sth] == $::oratcl::codes(OCI_STILL_EXECUTING)} {
...
process other events
...
}
#fetch one row
while {[orafetch $sth -datavar row] == $::oratcl::codes(OCI_STILL_EXECUTING)} {
...
process other events
...
}
#while row found, process and fetch another row
while {[oramsg $sth rc] == 0} {
puts "row [oramsg $sth rows] == $row"
while {[orafetch $sth -datavar row] == $::oratcl::codes(OCI_STILL_EXECUTING)} {
...
process other events
...
}
}
ENVIRONMENT VARIABLES
ORACLE_SID
The default Oracle server system ID.
ORACLE_HOME
The path to the Oracle home directory for ORACLE_SID.
ORACLE_LIBRARY
The path to the Oracle dynamic client library.
Used to override the default path of $ORACLE_HOME/lib/libclntsh.so
FILES
/etc/oratab or /var/opt/oracle/oratab
$ORACLE_HOME/network/admin/tnsnames.ora
/etc/tnsnames.ora
/var/opt/oracle/tnsnames.ora
$HOME/.tnsnames.ora - definitions for Oracle servers.
BUGS
None known.
AUTHOR
Todd Helfter, Version 4.5
thelfter@gmail.com