Oracle connector¶

The oracle package exposes some functionality of OCI. With this package, Tarantool Lua applications can send and receive data over Oracle protocol.

The advantage of integrating oracle with Tarantool, which is an application server plus a DBMS, is that anyone can handle all of the tasks associated with Oracle (control, manipulation, storage, access) with the same high-level language (Lua) and with minimal delay.

Prerequisites¶

An operating system with developer tools including cmake, C compiler with gnu99 support, git and Lua.

Tarantool 1.6.5+ with header files (tarantool and tarantool-dev packages).

Oracle OCI 10.0+ header files and dynamic libs.

Automatic build¶

Important: Builder requires Oracle Instant Client zip archives. You need to download them from Oracle into the source tree:

curl -O https://raw.githubusercontent.com/bumpx/oracle-instantclient/master/instantclient-basic-linux.x64-12.2.0.1.0.zip
curl -O https://raw.githubusercontent.com/bumpx/oracle-instantclient/master/instantclient-sdk-linux.x64-12.2.0.1.0.zip
sha256sum -c instantclient.sha256sum

To build a complete oracle package, you need to run package.sh script first (depends on docker). Packages will be available in build/ directory. Example:

wget <oracle-client.rpm>
wget <oracle-devel.rpm>
$./package.sh
...
done
$ls -1 build
oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
tarantool-oracle-1.0.0.0-1.el7.centos.src.rpm
tarantool-oracle-1.0.0.0-1.el7.centos.x86_64.rpm
tarantool-oracle-debuginfo-1.0.0.0-1.el7.centos.x86_64.rpm

After that you can install oracle package on the target machine:

rpm -Uvh tarantool-oracle-1.0.0.0-1.el7.centos.x86_64.rpm

Getting started¶

Start Tarantool in the interactive mode. Execute these requests:

tarantool> oracle = require('oracle')
tarantool> env, errmsg = oracle.new()
tarantool> if not env then error("Failed to create environment: "..errmsg) end
tarantool> c, errmsg = env:connect({username='system', password='oracle', db='localhost:1511/myspace'})
tarantool> if not c then error("Failed to connect: "..errmsg) end
tarantool> c:exec('CREATE TABLE test(i int, s varchar(20))')
tarantool> c:exec('INSERT INTO test(i, s) VALUES(:I, :S)', {I=1, S='Hello!'})
tarantool> rc, result_set = c:exec('SELECT * FROM test')

If all goes well, you should see:

tarantool> result_set[1][2] -- 'Hello!'

This means that you have successfully installed tarantool/oracle and successfully executed an instruction that brought data from an Oracle database.

API reference¶

function new([opts])¶

Create Oracle connection environment.

Accepts parameters:

[optional] table of options:
- charset - client-side character and national character set. If not set or set improperly, NLS_LANG setting is used.

Returns: * env - environment object in case of success, nil otherwise, * err [OPTIONAL] - error string in case of error.

function env:connect(credentials [, additional options])¶

Connect to the Oracle database.

Accepts parameters: * credentials (table):

username (str) - user login,

password (str) - user password,

db (str) - database URL.

additional options (as table):
- prefetch_count (int) - prefetch row count amount from Oracle,
- prefetch_size (int) - memory limit for prefetching (in MB),
- batch_size (int) - the size of each SELECT loop batch on exec() and cursor:fetchall().

Returns: * conn - connection object in case of success, nil otherwise, * err [OPTIONAL] - error string or table structure in case of error.

function env:version()¶

Get version string.

function conn:exec(sql [, args])¶

Execute an operation.

Accepts parameters:

sql - SQL statement,
[optional] statement arguments.

Returns:

rc - result code (0 - Success, 1 - Error)
result_set - result table, err - table with error (see below) in case of error
row_count - number of rows in result_set
err [OPTIONAL] - table with error in case of warning from Oracle

Examples:

--  Schema - create table(a int, b varchar(25), c number)
conn:exec("insert into table(a, b, c) values(:A, :B, :C)",  {A=1, B='string', C=0.1})

--  Schema - create table(a int, b varchar(25), c number)
rc, res = conn:exec("SELECT a, b, c FROM table")
res[1][1] -- a
res[1][2] -- b
res[1][3] -- c

function conn:cursor(sql [, args, opts])¶

Create cursor to fetch SELECT results.

Accepts parameters:

sql - SELECT SQL statement,
[optional] statement arguments,
[optional] table of options:
- scrollable - enable cursor scrollable mode (false by default).

Returns:

cursor - cursor object in case of success, nil otherwise
err [OPTIONAL] - table with error, same format as exec function one

function conn:close()¶

Close connection and all associated cursors.

function cursor:fetch_row()¶

Fetch one row of resulting set.

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch(fetch_size)¶

Fetch fetch_size rows of resulting set.

Accepts parameters:

fetch_size - number of rows (positive integer).

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch_all()¶

Fetch all remaining rows of resulting set.

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch_first(fetch_size)¶

Scrollable only.

Fetch first fetch_size rows of resulting set.

Accepts parameters:

fetch_size - number of rows (positive integer).

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch_last()¶

Scrollable only.

Fetch last row of resulting set.

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch_absolute(fetch_size, offset)¶

Scrollable only.

Fetch fetch_size rows of resulting set, starting from offset absolute position (including offset row).

Accepts parameters:

fetch_size - number of rows (positive integer),
offset - absolute cursor offset (positive integer).

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch_relative(fetch_size, offset)¶

Scrollable only.

Fetch fetch_size rows of resulting set, starting from current + offset absolute position(including current + offset row).

Accepts parameters:

fetch_size - number of rows (positive integer),
offset - relative cursor offset (signed integer).

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch_current(fetch_size)¶

Scrollable only.

Fetch fetch_size rows of resulting set, starting from current position (including current row).

Accepts parameters:

fetch_size - number of rows (positive integer).

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:fetch_prior(fetch_size)¶

Scrollable only.

Fetch fetch_size rows of resulting set, starting from previous row from the current position (including previous row).

Accepts parameters:

fetch_size - number of rows (positive integer).

Returns:

rc - result code (0 - Success, 1 - Error),
result_set - result table, err - table with error in case of error,
row_count - number of rows in result_set.

function cursor:get_position()¶

Scrollable only.

Get current cursor position.

Returns:

rc - result code (0 - Success, 1 - Error)
position - current cursor position, err - table with error in case of error

function cursor:close()¶

Closes cursor. After this was executed, cursor is no longer available for fetching results.

function cursor:is_closed()¶

Returns:

is_closed - true if closed, false otherwise.

function cursor:ipairs()¶

Lua 5.1 version of ipairs(cursor) operator.

Example:

-- Foo(row) is some function for row processing
for k, row in cursor:ipairs() do
    Foo(row)
end

function conn:close()¶

Close connection and all associated cursors.

Error handling¶

In case of error returns nil, err where err is table with the next fields:

type - type of error (1 - Oracle error is occured, 0 - Connector error is occured)
msg - message with text of error
code - error code (now defined ONLY for Oracle error codes)

Version:

Oracle connector¶

Table of contents¶

Prerequisites¶

Automatic build¶

Getting started¶

API reference¶

function new([opts])¶

function env:connect(credentials [, additional options])¶

function env:version()¶

function conn:exec(sql [, args])¶

function conn:cursor(sql [, args, opts])¶

function conn:close()¶

function cursor:fetch_row()¶

function cursor:fetch(fetch_size)¶

function cursor:fetch_all()¶

function cursor:fetch_first(fetch_size)¶

function cursor:fetch_last()¶

function cursor:fetch_absolute(fetch_size, offset)¶

function cursor:fetch_relative(fetch_size, offset)¶

function cursor:fetch_current(fetch_size)¶

function cursor:fetch_prior(fetch_size)¶

function cursor:get_position()¶

function cursor:close()¶

function cursor:is_closed()¶

function cursor:ipairs()¶

function conn:close()¶

Error handling¶