6de Ip
.br
.ie \\n(.$>=3 .ne \\$3
.el .ne 3
.IP "\\$1" \\$2
..
.TH "Oratcl" TCL "6/September/2001" "Tcl"
.SH NAME
Oratcl - Oracle Database Server access commands for Tcl
.sp
.SH "INTRODUCTION"
.P
Oratcl is an extension to the Tcl language designed to
provide access to an Oracle Database Server.
Each Oratcl command generally invokes several 
Oracle Call Interface (OCI) library functions.
Programmers using Oratcl should be familiar with basic concepts of
OCI programming.  Oratcl is loaded into the Tcl interpreter with
the tcl \fBpackage\fR command. 
.RS .5i
.sp
.B Example
.IP
package require Oratcl
package require -exact Oratcl 4.6
.sp
.SH "ORATCL COMMANDS"
.TP
.B oralogon \fIconnect-str ?-async? ?-failovercallback <procname>?\fR
.br
Connect to an Oracle server using \fIconnect-str\fR.
The connect string should be a valid Oracle connect string, in the form:  
.sp
	\fIname/password\fR
.br
	\fIname/password@n:dbname\fR
.sp
If using system authentication, the connect string should be in the form:
.sp
	\fI/\fR
.br
	\fI/@dbname\fR
.sp
If connecting as SYSOPER or SYSDBA, the connect string should be in the form:
.sp
	\fIsysdba\fR
.br
	\fIsysoper\fR
.sp
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.
\fBOralogon\fR raises a Tcl error if the connection 
is not made for any reason (login or password incorrect, 
network unavailable, etc.).  If the connect string does not include a
database specification, a connection will be established with the 
server specified in the environment variable ORACLE_SID.
.sp
The optional \fI-async\fR argument specifies that all commands 
allowing asynchronous (nonblocking) operation will do so.  The commands
affected are: \fBoraparse\fR, \fBoraexec\fR, \fBorafetch\fR and those
that make use of these such as \fBorasql\fR, \fBorabindexec\fR and \fBoraplexec\fR.
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.
.sp
The optional \fI-failovercallback <procname>\fR arguments provide a TAF
failover functionality to Oratcl.  The given procedure is called on a TAF
failover.  It is often used to re-execute "alter session" statements after
the automatic reconnect to another RAC node.
.TP
.B oralogoff \fIlogon\-handle\fR
.br
Logoff from the Oracle server
connection associated with \fIlogon\-handle\fR.  
\fILogon-handle\fR must be a valid handle previously opened with 
\fBoralogon\fR.
\fBOralogoff\fR raises a Tcl error if the logon handle specified is not open.
\fBOralogoff\fR closes any open statement handles opened with 
\fIlogon-handle\fR.
\fBOralogoff\fR returns the return code from OCISessionEnd().
.sp
.TP
.B oraopen \fIlogon\-handle\fR
.br
Open a statement handle to the server.
\fBOraopen\fR returns a handle to be used 
on subsequent Oratcl
commands that require a statement handle.
\fILogon\-handle\fR must be a valid handle previously opened with 
\fBoralogon\fR.
Multiple statement handles can be opened through the same or different logon
handles.
\fBOraopen\fR raises a Tcl error if the logon handle specified is not open.
.sp
.TP
.B oraconfig \fIstatement\-handle ?name ?value??\fR
.br
Configure a \fIstatement\-handle\fR or retrieve configuration information
about a \fIstatement\-handle\fR.  If no arguments are provided, 
\fBoraconfig\fR returns a list of \fIname\-value\fR pairs.  If \fIname\fR
is provided, the associated \fIvalue\fR will be returned.  If both 
\fIname\fR and \fIvalue\fR are provided, \fBoraconfig\fR will configure
the \fIstatement\-handle\fR as directed.  For numeric \fIvalue\fRs, 
a \fIvalue\fR less than or equal to zero or greater than the stated 
maximum will cause \fBOratcl\fR to raise a TCL error.  \fIName\fR may 
be one of the following:
.RS .5i
.IP \fIlongsize\fR 1i
Sets or returns the maximum amount of LONG or LONG RAW data returned
by \fBorafetch\fR.  The default is 40960 bytes and the maximum is 2147483647
bytes.
.IP \fIbindsize\fR
Sets or returns the size of the buffer used for storing bind variable values.
This is used in \fBorabindexec\fR and \fBoraplexec\fR to allow the buffer to 
be reused on subsequent calls.  The default is 2000 bytes and the maximum 
is 4000 bytes.
.IP \fInullvalue\fR 
Sets or returns the NULL value behavior.  A \fIvalue\fR of "default" causes
\fBorafetch\fR to substitute zeros for NULLs in numeric columns and null
strings "{}" for NULLs in character columns.  Any other \fIvalue\fR 
causes that \fIvalue\fR to be returned as a string for all NULLs.  The
default is "default".
.IP \fIfetchrows\fR 
Sets or returns the number of rows pre-fetched by \fBorafetch\fR.  
\fBOrafetch\fR attempts to fetch \fIfetchrows\fR 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 \fIfetchrows\fR to 
larger numbers for queries that return many rows may dramatically decrease
the time spent fetching the rows.  Changes to \fIfetchrows\fR only affects
subsequent \fBorasql\fR commands.  \fIFetchrows\fR defaults to 10 rows 
and the maximum is dependent upon available memory.
.IP \fIlobpsize\fR
Sets or returns the amount of of data (in characters) used in piecewise
reads and writes to LOB types in the \fBoralob\fR command.  \fILobpsize\fR
defaults to 10,000 characters and the maximum is 15,000 characters.
.IP \fIlongpsize\fR
Sets or returns the amount of of data (in characters) used in piecewise
reads and writes to LONG types in the \fBoralong\fR command.  \fILongpsize\fR
defaults to 50,000 characters and the maximum is 2,147,483,648 characters.
.IP \fIutfmode\fR
Sets or returns the UTF translation behavior.  Setting this value to true
causes \fIorasql\fR, \fIorabindexec\fR, \fIoraplexec\fR, \fIorafetch\fR,
\fIoralong\fR and \fIoralob\fR 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 \fIoralong\fR. The default is false. 
.IP \fInumbsize\fR
Sets or returns the amount of of data (in characters) used to store a number
column.  \fINumbsize\fR defaults to 40 characters and the maximum is 4000 characters.
.IP \fIdatesize\fR
Sets or returns the amount of of data (in characters) used to store a date
column.  \fIDatesize\fR defaults to 75 characters and the maximum is 7500 characters.
.RE
.sp
.TP
.B oraclose \fIstatement\-handle\fR
.br
Closes the cursor associated with \fIstatement\-handle\fR.
\fBOraclose\fR raises a Tcl error if the \fIstatement\-handle\fR specified is not 
open.
.sp
.TP
.B oramsg \fIhandle option\fR
.RS .5i
.IP \fIall\fR 1i
returns all values as a list in the format
{rc error rows peo ocicode sqltype}
.IP \fIrc\fR
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.  Typical values are:
.sp
.br
    0 - Function completed normally
.br
 1403 - End of data in \fBorafetch\fR command
.br
 1406 - Fetched column was truncated.
.br
-3123 - Asynchronous command still processing
.br
.IP \fIerror\fR 
returns the message text associated with the return code.
.IP \fIrows\fR 
returns the number of rows affected by an \fIinsert\fR, \fIupdate\fR,
\fIdelete\fR statement by \fBoraexec\fR, or the number of
rows fetched to date by \fBorafetch\fR.
.IP \fIpeo\fR 
returns the parse error offset, an index position in an SQL string that
failed to parse due to a syntax error.
.IP \fIocicode\fR 
returns the OCI code of the last OCI function called by Oratcl.  See the OCI
manual for descriptions.
.IP \fIsqltype\fR 
returns a code set by last SQL or PL/SQL parsed with \fBoraparse\fR.
Valid values are:
.sp
.br
1 == SELECT  corresponds to OCI_STMT_SELECT
.br
2 == UPDATE  corresponds to OCI_STMT_UPDATE
.br
3 == DELETE  corresponds to OCI_STMT_DELETE
.br
4 == INSERT  corresponds to OCI_STMT_INSERT
.br
5 == CREATE  corresponds to OCI_STMT_CREATE
.br
6 == DROP    corresponds to OCI_STMT_DROP
.br
7 == ALTER   corresponds to OCI_STMT_ALTER
.br
8 == BEGIN   corresponds to OCI_STMT_BEGIN
.br
9 == DECLARE corresponds to OCI_STMT_DECLARE
.IP \fIarraydml_errors\fR
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 text.
.br
.RE
.sp
.RS .5i
\fBOramsg\fR raises a Tcl error if the \fIhandle\fR cannot be located
in the set of open statement-handles and connected logon-handle.
.RE
.sp
.TP
.B oraparse \fIstatement-handle statement-text\fR
.br
Parse the statement \fIstatment\-text\fR on the Oracle server.
\fIStatement-handle\fR must be a valid handle previously opened with 
\fBoraopen\fR.
\fIStatement-text\fR can be either a SQL or anonymous PL/SQL statement.
the \fIStatement-text\fR may contain bind variables that begin with a
colon ':'.
.sp
\fBOraparse\fR will return the numeric return code "0" on successful
parsing of the  \fIstatement\-text\fR, and the error code returned by
Oracle when parsing fails.
\fBOraparse\fR raises a Tcl error if the \fIstatement\-handle\fR specified is
not open, or if the \fIstatment-text\fR is syntactically incorrect.
.sp
.TP
.B orabind \fIstatement\-handle ?-arraydml? ?:varname  value ...?\fR
.br
Bind values SQL variables in a previously parsed SQL statement. 
\fIStatement-handle\fR must be a valid handle previously opened with 
\fBoraopen\fR.
An SQL or PL/SQL statement must have previously been parsed by executing
\fBoraparse\fR.
\fBOrabind\fR may be executed repeatedly on a previously 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.
.sp
Optional \fI:varname value\fR pairs allow substitutions on SQL bind variables.
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 ":".
It is not an error to call \fBorabind\fR without any \fI:varname value\fR
pairs, but no binding will occur.
.sp
When using the \fI-arraydml\fR option, each value should contain a list of
values.  Each \fI:varname value\fR pair should have the same number of elements
in their lists.  The \fBorabind\fR command will raise an error if they are not.
The \fI-arraydml\fR option can only be used for updates and inserts.
.sp
\fBOrabind\fR will return "0" when bindings are successful; 
"1003" if a previous SQL has not been parsed with \fBoraparse\fR;
"1008" if not all SQL bind variables have been specified.  Refer to Oracle
error numbers and messages for other possible values.
.sp
\fBOrabind\fR raises a Tcl error if the \fIstatement\-handle\fR specified has
not been opened with \fBoraopen\fR.
.sp
.TP
.B oraexec \fIstatement\-handle ?-commit?\fR
.br
Execute a previously parsed and optionally bound SQL statement. 
\fIStatement-handle\fR must be a valid handle previously opened with 
\fBoraopen\fR.
An SQL statement must have previously been parsed by executing \fBoraparse\fR.
\fBOrabind\fR and \fBoraexec\fR commands may be repeatedly issued after a
statement is parsed.
.sp
The optional \fI-commit\fR argument specifies that the SQL will be
committed upon successful execution.
.sp
.TP
.B orafetch \fIstatement\-handle\fR ?options ...?
Retrieve data from the database as specified by prior
\fBoraparse\fR, \fBorabind\fR, \fBoraxec\fR calls.  All
values are converted to character strings except ref_cursors which
will be represented in the datavariable list as a null string.  
\fBOrafetch\fR returns the code from the OCIStmtExecute() library
function.  Likely values include 0 for success, 1403 for no more data,
and -3123 for asynchronous still executing.
.RS .5i
.IP \fI-datavariable\fR 1.6i
Specifies the variable to be set with a list containing the row of data fetched. 
The list returned in the datavariable by \fBorafetch\fR contains the values of the selected columns in the order specified by \fIselect\fR.
.IP \fI-dataarray\fR
Specifies the array in which the individual columns of data fetched will be set. 
.IP \fI-indexbyname\fR
When combined with the \fI-dataarray\fR option, \fBorafetch\fR will use the
column names from the query as the index (hash) values of the array.
.IP \fI-indexbynumber\fR
When combinded with the \fI-dataarray\fR option, \fBorafetch\fR will use the
column position number from the query as the index (hash) values of the array.
.IP \fI-command\fR
Specifies a script to eval when \fBorafetch\fR retrieves a row of data.
This script may reference the variable and array specified by other options.
.br
.RE
.sp
.RS .5i
\fBOrafetch\fR raises a Tcl error if the \fIstatement\-handle\fR specified is not 
open, or if an unknown option is specified.
.RE
.sp
.TP
.B orasql \fIstatement-handle sql-statement  ?-parseonly?  ?-commit? \fR
.br
Execute the SQL statement \fIsql\-statement\fR on the Oracle server.
\fIStatement-handle\fR must be a valid handle previously opened with 
\fBoraopen\fR.
\fBOrasql\fR will return the numeric return code "0" on successful execution of
the  \fIsql\-statement\fR.
.sp
The optional \fI-parseonly\fR argument causes \fBorasql\fR 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 \fBorabindexec\fR command, allowing bind variables
to be substituted with values.  Bind variables should only be used for
SQL statements \fBselect, insert, update,\fR or \fBdelete\fR.
.sp
The optional \fI-commit\fR argument specifies the that SQL will be
committed upon successful execution.
.sp
\fBOrasql\fR raises a Tcl error if the statement handle specified is
not open, or if the SQL statement is syntactically incorrect.
.sp
Table inserts made with orasql should follow conversion rules in
the Oracle SQL Reference manual.
.sp
.TP 
.B orabindexec \fIstatement\-handle  ?\-commit?  ?:varname  value ...?\fR
.br
Execute a previously parsed SQL statement, optionally binding values 
to SQL variables.  
\fIStatement-handle\fR must be a valid handle previously opened with 
\fBoraopen\fR.
An SQL statement must have previously been parsed by executing \fBoraparse\fR
or \fBorasql\fR
with the \fI-parseonly\fR option.  \fBOrabindexec\fR may be repeatedly executed
after a statement is parsed with bind variables substituted on each execution.
\fBOrabindexec\fR does not re-parse SQL statements before execution.
.sp
The optional \fI-commit\fR argument specifies the that SQL 
will commit upon successful execution.
.sp
Optional \fI:varname value\fR 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 ":".
.sp
\fBOrabindexec\fR 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 variables have
been specified.  Refer to Oracle error numbers and messages for other
possible values.
.sp
.TP 
.B oraplexec \fIstatement-handle  pl-block  ?:varname  value ...?\fR
.br
Execute an anonymous PL block, optionally binding values to PL/SQL variables.
.sp
\fIStatement-handle\fR must be a valid handle previously opened with 
\fBoraopen\fR.
\fIPl\-block\fR may either be a complete PL/SQL procedure or a call to
a stored procedure coded as an anonymous PL/SQL block.  Optional
\fI:varname value\fR pairs may follow the pl-block.  Varnames must be
prefixed by a colon ":", and match the substitution 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, {}.
.sp
Ref-cursor variables may be returned from a PL/SQL block by specifying an
open \fIstatement\-handle\fR as the bind value for a :varname bind variable.  
The handle must have previously been opened by \fBoraopen\fR using the 
same \fIlogon\-handle\fR as the cursor used to execute the \fBoraplexec\fR
command.
After \fBoraplexec\fR completes, the handle may be used to fetch result rows
by using orafetch; column information is available by using \fBoracols\fR.
.sp
\fBOraplexec\fR will return "0" when executed successfully;  Use the command
\fBorafetch\fR to retrieve the bind results. 
.sp
\fBOraplexec\fR raises a Tcl error if the cursor handle specified is not open,
or if the PL/SQL block is in error.  
.sp
.TP 
.B oralob \fIsub\-command handle ?options ...?\fR
.br
Perform operations on Oracle Long Objects.
.sp
\fIHandle\fR must be either a valid statement handle previously opened
with \fBoraopen\fR or a LOB handle created with the
\fBalloc\fR sub-command.  Both Binary Long Object (BLOB) and Character
Long Object (CLOB) columns are supported by the \fBoralob\fR command.
.sp
The following \fIsub\-commands\fR are
available:
.RE
.RS 1i
.sp
\fBalloc \fIstatement\-handle -table $table -column $column -rowid $rowid\fR
.RS
Create and return a LOB handle that refers to the LOB specified by
(\fI$table\fR, \fI$column\fR, \fI$rowid\fR).  \fIstatement\-handle\fR
must be a statement handle previously created with \fBoraopen\fR and
will be used implicitly by the other \fBoralob\fR sub-commands that
operate on this LOB.
.RE
.sp
.TP
.B free \fILOB\-handle\fR
.br
Destroy the LOB handle and free any resources associated with it.
.sp
.TP
.B read \fILOB\-handle \-datavariable varname\fR
.br
Read the LOB specified by \fILOB\-handle\fR into the variable identified by
\fIvarname\fR.
.sp
.TP
\fBsubstr \fILOB\-handle \-start $start \-stop $stop \-datavariable varname\fR
.br
Reads characters of the LOB specified by \fILOB\-handle\fR, beginning at
\fI$startpos\fR and ending at \fI$stoppos\fR, into \fIvarname\fR.
\fI$startpos\fR and \fI$stoppos\fR both default to 0.
.sp
.TP
.B write \fILOB\-handle \-datavariable varname\fR
.br
Write the data in the variable identified by \fIvarname\fR into the
LOB specified by \fILOB\-handle\fR.
.sp
.TP
.B writeappend \fILOB\-handle \-datavariable varname\fR
.br
Append the data in the variable identified by \fIvarname\fR to the
end of the LOB specified by \fILOB\-handle\fR.
.sp
.TP
.B append \fILOB\-handle1 LOB\-handle2\fR
.br
Appends the contents of the LOB specified by \fILOB\-handle2\fR to the
LOB specified by \fILOB\-handle1\fR.  Both LOBs must be of the same
type (Binary or Character).
.sp
.TP
.B erase \fILOB\-handle \-start $start\fR \fI\-stop $stop\fR
.br
Overwrites the data in the LOB specified by \fILOB\-handle\fR from
\fI$start\fR to \fI$stop\fR with NULL characters.
\fI$start\fR and \fI$stop\fR both default to 0.
.sp
.TP
.B trim \fILOB\-handle \-length $length\fR
.br
Trims the LOB specified by \fILOB\-handle\fR to \fI$length\fR characters or
bytes.
.sp
.TP
.B length \fILOB\-handle\fR
.br
Returns the length (in characters or bytes) of the LOB specified by
\fILOB\-handle\fR.
.sp
.RE
.RS 1i
\fBinstr \fILOB\-handle \-pattern $pattern \-start $start \-nth $nth\fR
.br
.RS
Returns the position in the LOB specified by \fILOB\-handle\fR at which the
\fI$nth\fR occurrence of the pattern \fI$pattern\fR appears.  The
search is started at \fI$start\fR.  \fI$start\fR defaults to 0
and \fI$nth\fR defaults to 1.
.sp
.RE
\fBcompare \fILOB\-handle1 LOB\-handle2 \-start1 $start1 \-start2 $start2 \-length $length\fR
.br
.RS
Compares the two LOBs specified by \fILOB\-handle1\fR and
\fILOB\-handle2\fR.  The comparison is begun at the position indicated
by \fI$start1\fR (in LOB 1) and \fI$start2\fR (LOB 2) and continues
for \fI$length\fR 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.
.RE
.sp
.RE
.RS .5i
The \fBoralob\fR commands are a collection of TCL and anonymous
PL/SQL wrappers for the Oracle \fBdbms_lob\fR PL/SQL package and
therefore require the \fIrowid\fR (as well as the table name and
column name) of the LOB in order to operate.  The \fIrowid\fR
of a row may be determined easily, as shown in the example below.
.sp
.B LOB Example
.sp
.RS 
.nf
# 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 -datavariable data
set l [oralob length $lobid]
# $l == 15
set data ""
oralob read $lobid -datavariable data
# $data contains "abcdeabcdeabcde"
set i [oralob instr $lobid -pattern "eab" -start 3 -nth 2]
# $i == 9 -- TCL-like indexing, not Oracle indexing
.br
.fi
.RE
.sp
.B NOTE:
.RS
The PL/SQL DBMS_LOB package used by the \fBoralob\fR command requires
BLOB and CLOB fields to be initialized 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().
.sp
.B Table Definition Example:
.RS
.nf
create table test_lob_1 (
    lob_key     varchar2(10)  primary key,
    lob_clob    clob          default empty_clob(),
    lob_blob    blob          default empty_blob()
)
.fi
.RE
.sp
.B Initialize Before Use Example:
.RS
.nf
# Assume the following table definition:
# create table test_lob_2 (
#     lob_key     varchar2(10),
#     lob_clob    clob,
#     lob_blob    blob
# )
.sp
set sql { \\
	insert into test_lob_2 \\
	  (lob_key, lob_clob, lob_blob) \\
	values (:lob_key, empty_clob(), empty_blob() )\\
}
.sp
orasql $sth $sql -parseonly
.sp
# Create a new row in test_lob_2 with lob_clob 
# and lob_blob properly initialized.
orabindexec $sth :lob_key "AAAAAAAAAA"
.fi
.RE
.RE
.RE
.RS
.sp
.TP 
.B oralong \fIsub\-command handle ?options ...?\fR
.br
Perform operations on Oracle LONG column-types.
.sp
\fIHandle\fR must be either a valid statement handle previously opened
with \fBoraopen\fR or a LONG handle created with the \fBalloc\fR
sub-command.  Both LONG and LONG RAW columns are supported by the
\fBoralong\fR command.
.sp
The following \fIsub\-commands\fR are
available:
.RE
.RS 1i
.sp
\fBalloc \fIstatement\-handle -table $table -column $column -rowid $rowid\fR
.RS
Create and return a LONG handle that refers to the LONG specified by
(\fI$table\fR, \fI$column\fR, \fI$rowid\fR).  \fIstatement\-handle\fR
must be a statement handle previously created with \fBoraopen\fR and
will be used implicitly by the other \fBoralong\fR sub-commands that
operate on this LONG.
.RE
.sp
.TP
.B free \fILONG\-handle\fR
.br
Destroy the LONG handle and free any resources associated with it.
.sp
.TP
.B read \fILONG\-handle \-datavariable varname\fR
.br
Read the LONG specified by \fILONG\-handle\fR into the variable identified by
\fIvarname\fR.
.sp
.TP
.B write \fILONG\-handle \-datavariable varname\fR
.br
Write the data in the variable identified by \fIvarname\fR into the
LONG specified by \fILONG\-handle\fR.
.RE
.sp
.RS .5i
.B LONG Example
.sp
.RS 
.nf
# 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 -datavariable chr_data
oracommit $lda
oralong read $longid -datavariable out_data
oralong free $longid

if {[string equal $chr_data $out_data]} {
        puts "write/read results are equal"
}
.br
.fi
.RE
.RE
.RE
.RS
.sp
.TP 
.B oracols \fIstatement-handle ?option?\fR
.br
Return the names of the columns from the last \fBorasql, orafetch,\fR or
\fBoraplexec\fR command as a Tcl list.
The \fBoracols\fR may be used after \fBoraplexec\fR, in which case the 
bound variable names are returned.  The \fIoption\fR parameter can be used
to alter the result as follows:
.RS .5i
.IP \fIall\fR 1i
returns all values as a list of lists in the format
{{name size type precision scale nullok} {...}}
.IP \fIname\fR
returns a list of column names.  This is the default.
.IP \fIsize\fR 
returns a list of column sizes.
.IP \fItype\fR 
returns a list of column types.
.IP \fIprecision\fR 
returns a list of column precisions.
.IP \fIscale\fR 
returns a list of column scales.
.IP \fInullok\fR 
returns a list of column "NULLOK" values.  "NULLOK" will be 1 if
the column may be NULL, or 0 otherwise.
.RE
.RS .5i
.sp
The \fBoracols\fR command raises a Tcl error if the \fIstatement\-handle\fR
specified is not open.
The \fBoracols\fR command raises a Tcl error if the \fIoption\fR is 
not valid.
.RE
.sp
.TP 
.B oradesc \fIlogon\-handle table\-name\fR
.br
Describes the columns of \fItable\-name\fR.  Returns a list containing lists
in the format {name size type precision scale nullok} for each column of
the table.  \fBOradesc\fR will also describe the columns of a table referenced
by a private or public synonym when given the name of a synonym as the \fItable-name\fR
argument.  \fBOradesc\fR will describe an object in the connecting schema before
describing a public synonym when the names are the same.
.sp
.TP 
.B oracommit \fIlogon\-handle\fR
.br
Commit any pending transactions from prior \fBorasql, orabindexec,\fR or
\fBoraplexec\fR commands that use a statement handle opened 
through the connection specified by \fIlogon-handle\fR.  
\fILogon-handle\fR must be a valid handle previously opened with 
\fBoralogon\fR.
\fBOracommit\fR raises a Tcl error if the \fIlogon\-handle\fR 
specified is not open.
.sp
.TP 
.B oraroll \fIlogon-handle\fR
.br
Rollback any pending transactions from prior \fBorasql, orabindexec,\fR or
\fBoraplexec\fR commands that use a
statement handle opened through the connection specified by 
\fIlogon-handle\fR.  
\fILogon-handle\fR must be a valid handle previously opened with 
\fBoralogon\fR.
\fBOraroll\fR raises a Tcl error if the \fIlogon\-handle\fR specified 
is not open.
.sp
.TP 
.B oraautocom \fIlogon-handle boolean\fR
.br
Enables or disables automatic commit of SQL data manipulation
statements using a statement handle opened through the connection
specified by \fIlogon\-handle\fR.  
\fILogon-handle\fR must be a valid handle previously opened with
\fBoralogon\fR.
\fIBoolean\fR may be any value that evaluates to boolean true (1, on,
true) to enable automatic commit, or boolean false to disable.
After setting the automatic commit status, \fBoraautocom\fR returns
the new commit status (1 for on, 0 for off) for validation purposes.
The automatic commit feature defaults to "off".  \fBOraautocom\fR raises a
Tcl error if the \fIlogon-handle\fR specified is not open.
.sp
.TP 
.B oraldalist
.br
Return a list of all opened logon-handles.
.TP 
.B orastmlist \fIlogon-handle\fR
.br
Return a list of all opened statement-handles associated with the
\fIlogon-handle\fR.
.TP
.B orainfo \fIoption ?args?\fR
.br
Retrieves information about oratcl.  \fIOption\fR may be either
"version", "server", "client", "status", "logonhandle", or "nlsgetinfo".  
.sp
"\fBorainfo\fR version" returns the current oratcl version.  
.sp
"\fBorainfo\fR server" requires a valid logon
handle previously opened with \fBoralogon\fR as an argument and
returns the oracle server information.  
.sp
"\fBorainfo\fR client" returns the oracle client version information. I.E. 10.2.0.1.0
.sp
"\fBorainfo\fR status" requires a valid logon
handle previously opened with \fBoralogon\fR as an argument and
returns the oracle server connection status (1 connected, 0 not connected).  
.sp
"\fBorainfo\fR logonhandle" requires
a statement handle previously opened with \fBoraopen\fR and returns the 
login handle that the statement handle was opened under.
.sp
"\fBorainfo\fR nlsgetinfo" requires a valid logon
handle previously opened with \fBoralogon\fR and an NLS_PARAMETER
namet.   The function returns the value associated with the NLS_PARAMETER.  
.sp
.RS .5i
.B EXAMPLES
.nf
	puts [orainfo version]
.sp
	set lda [oralogon username/password@db]
	puts [orainfo server $lda]
.sp
	set sth [oraopen $lda]
	set mylda [orainfo logonhandle $sth]
.sp
	puts [orainfo nlsgetinfo $lda NLS_CHARACTER_SET]
.fi
.RE	

.P
.SH NOTES
.P
Tcl errors can also be raised by any Oratcl command if a command's internal
calls to OCI library routines fail.  
.TP
.B Dates
Oracle is very particular about using date literals in SQL.  Date literals
should match the default date format for your oracle session.  default date
formats can be modified with an "alter session" SQL statement.
.sp
	alter session set nls_date_format = 'DD-MON-YYYY'
.TP
.B Connection Limits
The limit of the number of simultaneous connection handles and statement
handles is determined by the Oracle server configuration.  The processes
\fIinit.ora\fR is the most common limitation.
.TP
.B Multithreading
Oratcl may be used with the thread extension.
.TP
.B Slave Interpreters
Oratcl may be used in a Tcl slave interpreter.  However, 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.
.TP
.B Long and Long Raw
The maximum amount of LONG or LONG RAW data returned by \fBorafetch\fR
is ultimately dependent on Oratcl's ability to malloc() \fImaxlong\fR
bytes of memory for each LONG/LONG RAW column retrieved.  Configuring
maxlong to too high a value may cause core dumps or memory shortages.
.TP
.B Ref Cursor
Ref-cursor variables returned by oraplexec must be specified as a currently
open statement handle from the same logon connection:
.RS
.nf
.sp
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
}
.fi
.RE
.TP
.B Bind Variables
Using SQL bind variables is more efficient than letting Oracle reparse
SQL statements.  Use a combination of oraparse / orabind / oraexec:
.sp
.RS
.nf
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
}
.sp
.fi
.RE
rather than:
.sp
.RS
.nf
foreach name [list Ted Alice John Sue] {
  set sql "insert into name_tab(first_name) values('$name')"
  oraparse $cur $sql
  oraexec $cur
}
.RE
.TP
.B Array DML
Using array dml for inserts and updates can greatly improve performance.  Oratcl
will pass all information to be inserted or updated in a single execute.
.sp
.RS
.nf
set sql {insert into name_tab(first_name) values (:firstname)}
set names [list Jack John Joe Jacob]
oraparse $cur $sql
orabind $cur -arraydml :firstname $names
oraexec $cur
.sp
.fi
.RE
.sp
.SH ASYNCHRONOUS TRANSACTION PROCESSING
.nf
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 -datavariable 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 -datavariable row] == $::oratcl::codes(OCI_STILL_EXECUTING)} {
		...
		process other events
		...
	}
}

.fi
.sp
.sp
.SH ENVIRONMENT VARIABLES
.TP
ORACLE_SID 
The default Oracle server system ID.
.TP
ORACLE_HOME 
The path to the Oracle home directory for ORACLE_SID.
.TP
ORACLE_LIBRARY 
The path to the Oracle dynamic client library 'libclntsh'.
Used to override the default path of $ORACLE_HOME/lib/libclntsh.so
.sp
.SH FILES
.TP
/etc/oratab or /var/opt/oracle/oratab
.TP
$ORACLE_HOME/network/admin/tnsnames.ora 
.TP
/etc/tnsnames.ora 
.TP
/var/opt/oracle/tnsnames.ora
.TP
$HOME/.tnsnames.ora - definitions for Oracle servers.
.sp
.SH BUGS
None known.
.sp
.SH AUTHOR
Todd Helfter  Version 4.6 not yet released.
.P
thelfter@gmail.com
.sp
