| ActiveTcl User Guide |

## 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