ORATCL

NAME

oratcl - Tcl 9 interface to Oracle via ODPI-C 5.6.2

SYNOPSIS

oralogon  connect-string ?-pool min max incr? ?-homogeneous 0|1? ?-getmode wait|nowait|force?
oralogoff logon-handle

oraopen   logon-handle           # open a statement handle
orastmt   logon-handle           # alias of oraopen
oraclose  statement-handle

oraparse  statement-handle sql-text
orasql    statement-handle sql-text ?-parseonly? ?-commit?
oraplexec statement-handle {pl/sql block} ?-commit?
oraexec   statement-handle ?-commit?

orabind      statement-handle :name value ? :name value ... ?
orabindexec  statement-handle ?-commit? ?-arraydml? :name list ...

orafetch statement-handle
         ?-datavariable varName?
         ?-dataarray arrayName?
         ?-indexbyname|-indexbynumber?
         ?-command script?
         ?-max N?
         ?-resultvariable varName?
         ?-returnrows?
         ?-asdict?

oracols statement-handle
oradesc statement-handle           # {name type} pairs

oramsg  handle rc|error|rows|peo|ocicode|sqltype|fn|action|sqlstate|recoverable|warning|offset|all|allx

oralob  size|read|write|trim|close lob-handle ?args...?

oraautocommit logon-handle 0|1
oracommit    logon-handle
orarollback  logon-handle
orabreak     logon-handle         # cancel active call

oraexecasync  statement-handle ?-commit?
orawaitasync  statement-handle ?-timeout milliseconds?

DESCRIPTION

oratcl 9.0 implements the classic Oratcl API on top of ODPI-C (no OCI). It targets Tcl 9: command signatures use Tcl_Size and are thread/multi-interp safe. One ODPI context is created per process; per-interp state (handles, registries) is created on each load. Async execution uses per-statement workers with proper addRef/release of underlying dpi handles.

COMMANDS

Connection

"-pool Create/acquire via session pool.

"-homogeneous Homogeneous pool (default 1).

"-getmode Pool get mode.

Returns a logon-handle (e.g. "oraL1").

oralogon connect-string ?options?: Connect using either username/password@connect_identifier, or external auth (leave user/pw empty). Options:
oralogoff logon-handle: Close the connection (see ORA_MSG behavior for error reporting).
oraautocommit logon-handle 0|1: Set autocommit. DML/PLSQL commit on success when enabled or when -commit is supplied to exec commands.
orainfo logon-handle: Return a dict with the current connection info. Currently it returns autocommit only.

Statements & execution

oraopen logon-handle (alias: orastmt): Open a statement handle.
orasql stmt sql ?-parseonly? ?-commit?: Parse and, unless -parseonly, execute once. Clears the per-statement stored-bind cache if text changes.
oraexec stmt ?-commit?: Execute the already-parsed statement (single execution).
oraplexec stmt {pl/sql} ?-commit?: Prepare and execute a PL/SQL block.
orabind stmt :name value ...: Bind scalars by name. LOB type is inferred by name and/or value representation.
orabindexec stmt ?-commit? ?-arraydml? :name list ...: Array DML with name→list pairs. BYTES elements are copied to private buffers to remain stable until executeMany().

Fetching

orafetch stmt ?options?: Fetches up to -max N rows and returns 0 while data remains, or 1403 at end-of-data. Use -returnrows or -resultvariable to obtain a list of rows; -asdict returns dicts keyed by column names.

Metadata

oracols stmt: Return column names of the current query.
oradesc stmt: Return {name type} pairs.

LOBs

oralob read lob-handle offset amount: Read as a bytearray. When inlineLobs is enabled at the connection, orafetch returns raw data instead of handles.

Async

oraexecasync stmt ?-commit?: Begin execution in a worker thread. The worker holds addRef() on the underlying dpi handles.
orawaitasync stmt ?-timeout ms?: Wait for completion or timeout. On completion, temp LOBs created by orabind are released.

CONFIGURATION

Use oraconfig on a logon-handle or a statement-handle.

Connection-level keys:

stmtcachesize, fetcharraysize, prefetchrows, prefetchmemory, calltimeout (ms),

inlineLobs (0/1), and failover policy: foMaxAttempts, foBackoffMs, foBackoffFactor,

foErrorClasses, foDebounceMs, -failovercallback.

Statement-level keys:

fetchrows, prefetchrows (overrides connection defaults when the statement exists).

EXAMPLES

Basic select:

set L [oralogon "scott/tiger@//dbhost/pdb"]
set S [oraopen $L]
oraparse $S {select empno, ename from emp where deptno = :d}
orabind  $S :d 10
oraexec  $S
set rows {}
while 1 {
    set rc [orafetch $S -asdict -datavariable row]
    if {$rc == 1403} break
    lappend rows [dict get $row EMPNO] [dict get $row ENAME]
}
oraclose $S
oralogoff $L

Array DML:

set S [oraopen $L]
oraparse $S {insert into t(x,y) values(:x,:y)}
orabindexec $S -commit -arraydml :x {1 2 3} :y {A B C}

Async:

oraparse $S {begin dbms_lock.sleep(2); end;}
oraexecasync $S
# do other work...
orawaitasync $S -timeout 3000

ENVIRONMENT

The test suite uses ORATCL_CONNECT for the connect string.

THREADS & MULTIPLE INTERPRETERS

One global ODPI context is created lazily and destroyed on process exit.

Each interpreter has isolated handle registries. When statically linked into tclsh, every interp calls oratcl_Init; the driver ensures client libraries are loaded only once.

↑ Top