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