fetched and buffered by internal calls to the database when fetching rows

from SELECT statements and REF CURSORS. The value can drastically affect

the performance of a query since it directly affects the number of network

round trips between Python and the database. For methods like

:meth:`~Cursor.fetchone()` and :meth:`~Cursor.fetchall()` it does not change

how many rows are returned to the application. For

Oracle Client library fetches when a SELECT statement is executed. This

value can reduce the number of round-trips to the database that are required

to fetch rows but at the cost of additional memory. Setting this value to 0

using a set of NoSQL-style cx_Oracle methods. By default, documents are JSON

strings. See the :ref:`user manual <sodausermanual>` for examples.

.. _sodarequirements:

SODA Requirements

To use SODA, the role SODA_APP must be granted to the user. To create

collections, users need the CREATE TABLE privilege. These can be granted by a

DBA:

.. code-block:: sql

Advanced users who are using Oracle sequences for keys will also need the CREATE

SEQUENCE privilege.

Otherwise you may get errors such as "ORA-40842: unsupported value JSON in

the metadata for the field sqlType" or "ORA-40659: Data type does not match

.. _usinginitoracleclient:

``C:\oracle\instantclient_19_9`` on Windows or

``$HOME/Downloads/instantclient_19_8`` on macOS, then you can use:

Note the use of a 'raw' string ``r"..."`` on Windows so that backslashes are

treated as directory separators.

The :meth:`~cx_Oracle.init_oracle_client()` function can only be called once.

``instantclient-basic-windows.x64-19.9.0.0.0dbru.zip`` to

``C:\oracle\instantclient_19_9``.

``C:\oracle\instantclient_19_9\network\admin``. This is the default

- On Windows and macOS, try using :meth:`~cx_Oracle.init_oracle_client()`.

See :ref:`usinginitoracleclient`.

- Check that Python and your Oracle Client libraries are both 64-bit, or

both 32-bit. The ``DPI-1047`` message will tell you whether the 64-bit

or 32-bit Oracle Client is needed for your Python.

- Set the environment variable ``DPI_DEBUG_LEVEL`` to 64 and restart

cx_Oracle. The trace messages will show how and where cx_Oracle is

looking for the Oracle Client libraries.

At a Windows command prompt, this could be done with::

On Linux and macOS, you might use::

Oracle SODA implementations are also available in `Node.js

<https://oracle.github.io/node-oracledb/doc/api.html#sodaoverview>`__, `Java

<https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/java/adsda/index.html>`__,

`PL/SQL <https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADSDP>`__,

`Oracle Call Interface

<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-23206C89-891E-43D7-827C-5C6367AD62FD>`__

and via `REST

<https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/rest/index.html>`__.

and the Oracle Database `Introduction to Simple Oracle Document Access (SODA)

<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADSDI>`__ manual.

For specified requirements see the cx_Oracle :ref:`SODA requirements <sodarequirements>`.

SODA Examples

Committing SODA Work

The general recommendation for SODA applications is to turn on

:attr:`~Connection.autocommit` globally:

.. code-block:: python

If your SODA document write operations are mostly independent of each other,

this removes the overhead of application transaction management and the need for

explicit :meth:`Connection.commit()` calls.

When deciding how to commit transactions, beware of transactional consistency

and performance requirements. If you are using individual SODA calls to insert

or update a large number of documents with individual calls, you should turn

:attr:`~Connection.autocommit` off and issue a single, explicit

:meth:`~Connection.commit()` after all documents have been processed. Also

consider using :meth:`SodaCollection.insertMany()` or

:meth:`SodaCollection.insertManyAndGet()` which have performance benefits.

If you are not autocommitting, and one of the SODA operations in your

transaction fails, then previous uncommitted operations will not be rolled back.

Your application should explicitly roll back the transaction with

:meth:`Connection.rollback()` to prevent any later commits from committing a

partial transaction.

Note:

- SODA DDL operations do not commit an open transaction the way that SQL always does for DDL statements.

- When :attr:`~Connection.autocommit` is ``True``, most SODA methods will issue a commit before successful return.

- SODA provides optimistic locking, see :meth:`SodaOperation.version()`.

- When mixing SODA and relational access, any commit or rollback on the connection will affect all work.

rows = [

(1, "First"),

(2, "Second"),

(3, "Third"),

(4, "Fourth"),

(5, None), # Insert a NULL value

(6, "Sixth"),

(7, "Seventh")

]

cursor.setinputsizes(None, 20)

rows = [

{"d": "Eighth", "i": 8},

{"d": "Ninth", "i": 9},

{"d": "Tenth", "i": 10}

]

cursor = connection.cursor()

cursor.setinputsizes(d=20)

cursor.executemany("insert into mytab(id, data) values (:i, :d)", rows)

rows = [

("Eleventh",),

("Twelth",)

]

cursor = connection.cursor()

cursor.executemany("insert into mytab(id, data) values (11, :1)", rows)

cursor.prefetchrows = 1000