Skip to content

Commit 855d03c

Browse files
Christopher Jonessqla-tester
Christopher Jones
authored and
sqla-tester
committed
Update Oracle dialect doc, mostly to prefer python-oracledb
### Description Small updates for Oracle Database dialect documentation. - prefer python-oracledb over cx_Oracle - Prefer the product name 'Oracle Database' over the company name 'Oracle' - update links - modernize This is a refresh of existing content. I decided the apparently now duplicated sections between cx_Oracle and python-oracledb were justified for clarity due to the inevitable differences. This pull request is: - [x] A documentation / typographical / small typing error fix - Good to go, no issue or tests are needed **Have a nice day!** Closes: sqlalchemy#12078 Pull-request: sqlalchemy#12078 Pull-request-sha: 7c4dcf9 Change-Id: I3678976f5524ee164dc31b3122b224ae37060b71
1 parent fa76fbd commit 855d03c

38 files changed

Lines changed: 1091 additions & 601 deletions

‎doc/build/core/connections.rst‎

Lines changed: 26 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -419,7 +419,7 @@ reverted when a connection is returned to the connection pool.
419419

420420
:ref:`SQL Server Transaction Isolation <mssql_isolation_level>`
421421

422-
:ref:`Oracle Transaction Isolation <oracle_isolation_level>`
422+
:ref:`Oracle Database Transaction Isolation <oracle_isolation_level>`
423423

424424
:ref:`session_transaction_isolation` - for the ORM
425425

@@ -588,17 +588,17 @@ To sum up:
588588
Using Server Side Cursors (a.k.a. stream results)
589589
-------------------------------------------------
590590

591-
Some backends feature explicit support for the concept of "server
592-
side cursors" versus "client side cursors". A client side cursor here
593-
means that the database driver fully fetches all rows from a result set
594-
into memory before returning from a statement execution. Drivers such as
595-
those of PostgreSQL and MySQL/MariaDB generally use client side cursors
596-
by default. A server side cursor, by contrast, indicates that result rows
597-
remain pending within the database server's state as result rows are consumed
598-
by the client. The drivers for Oracle generally use a "server side" model,
599-
for example, and the SQLite dialect, while not using a real "client / server"
600-
architecture, still uses an unbuffered result fetching approach that will
601-
leave result rows outside of process memory before they are consumed.
591+
Some backends feature explicit support for the concept of "server side cursors"
592+
versus "client side cursors". A client side cursor here means that the
593+
database driver fully fetches all rows from a result set into memory before
594+
returning from a statement execution. Drivers such as those of PostgreSQL and
595+
MySQL/MariaDB generally use client side cursors by default. A server side
596+
cursor, by contrast, indicates that result rows remain pending within the
597+
database server's state as result rows are consumed by the client. The drivers
598+
for Oracle Database generally use a "server side" model, for example, and the
599+
SQLite dialect, while not using a real "client / server" architecture, still
600+
uses an unbuffered result fetching approach that will leave result rows outside
601+
of process memory before they are consumed.
602602

603603
.. topic:: What we really mean is "buffered" vs. "unbuffered" results
604604

@@ -1807,17 +1807,18 @@ Current Support
18071807
~~~~~~~~~~~~~~~
18081808

18091809
The feature is enabled for all backend included in SQLAlchemy that support
1810-
RETURNING, with the exception of Oracle for which both the cx_Oracle and
1811-
OracleDB drivers offer their own equivalent feature. The feature normally takes
1812-
place when making use of the :meth:`_dml.Insert.returning` method of an
1813-
:class:`_dml.Insert` construct in conjunction with :term:`executemany`
1814-
execution, which occurs when passing a list of dictionaries to the
1815-
:paramref:`_engine.Connection.execute.parameters` parameter of the
1816-
:meth:`_engine.Connection.execute` or :meth:`_orm.Session.execute` methods (as
1817-
well as equivalent methods under :ref:`asyncio <asyncio_toplevel>` and
1818-
shorthand methods like :meth:`_orm.Session.scalars`). It also takes place
1819-
within the ORM :term:`unit of work` process when using methods such as
1820-
:meth:`_orm.Session.add` and :meth:`_orm.Session.add_all` to add rows.
1810+
RETURNING, with the exception of Oracle Database for which both the
1811+
python-oracledb and cx_Oracle drivers offer their own equivalent feature. The
1812+
feature normally takes place when making use of the
1813+
:meth:`_dml.Insert.returning` method of an :class:`_dml.Insert` construct in
1814+
conjunction with :term:`executemany` execution, which occurs when passing a
1815+
list of dictionaries to the :paramref:`_engine.Connection.execute.parameters`
1816+
parameter of the :meth:`_engine.Connection.execute` or
1817+
:meth:`_orm.Session.execute` methods (as well as equivalent methods under
1818+
:ref:`asyncio <asyncio_toplevel>` and shorthand methods like
1819+
:meth:`_orm.Session.scalars`). It also takes place within the ORM :term:`unit
1820+
of work` process when using methods such as :meth:`_orm.Session.add` and
1821+
:meth:`_orm.Session.add_all` to add rows.
18211822

18221823
For SQLAlchemy's included dialects, support or equivalent support is currently
18231824
as follows:
@@ -1827,8 +1828,8 @@ as follows:
18271828
* SQL Server - all supported SQL Server versions [#]_
18281829
* MariaDB - supported for MariaDB versions 10.5 and above
18291830
* MySQL - no support, no RETURNING feature is present
1830-
* Oracle - supports RETURNING with executemany using native cx_Oracle / OracleDB
1831-
APIs, for all supported Oracle versions 9 and above, using multi-row OUT
1831+
* Oracle Database - supports RETURNING with executemany using native python-oracledb / cx_Oracle
1832+
APIs, for all supported Oracle Database versions 9 and above, using multi-row OUT
18321833
parameters. This is not the same implementation as "executemanyvalues", however has
18331834
the same usage patterns and equivalent performance benefits.
18341835

‎doc/build/core/defaults.rst‎

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -349,7 +349,7 @@ SQLAlchemy represents database sequences using the
349349
:class:`~sqlalchemy.schema.Sequence` object, which is considered to be a
350350
special case of "column default". It only has an effect on databases which have
351351
explicit support for sequences, which among SQLAlchemy's included dialects
352-
includes PostgreSQL, Oracle, MS SQL Server, and MariaDB. The
352+
includes PostgreSQL, Oracle Database, MS SQL Server, and MariaDB. The
353353
:class:`~sqlalchemy.schema.Sequence` object is otherwise ignored.
354354

355355
.. tip::
@@ -466,8 +466,8 @@ column::
466466

467467
In the above example, ``CREATE TABLE`` for PostgreSQL will make use of the
468468
``SERIAL`` datatype for the ``cart_id`` column, and the ``cart_id_seq``
469-
sequence will be ignored. However on Oracle, the ``cart_id_seq`` sequence
470-
will be created explicitly.
469+
sequence will be ignored. However on Oracle Database, the ``cart_id_seq``
470+
sequence will be created explicitly.
471471

472472
.. tip::
473473

@@ -544,7 +544,7 @@ Associating a Sequence as the Server Side Default
544544
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
545545

546546
.. note:: The following technique is known to work only with the PostgreSQL
547-
database. It does not work with Oracle.
547+
database. It does not work with Oracle Database.
548548

549549
The preceding sections illustrate how to associate a :class:`.Sequence` with a
550550
:class:`_schema.Column` as the **Python side default generator**::
@@ -627,7 +627,7 @@ including the default schema, if any.
627627

628628
:ref:`postgresql_sequences` - in the PostgreSQL dialect documentation
629629

630-
:ref:`oracle_returning` - in the Oracle dialect documentation
630+
:ref:`oracle_returning` - in the Oracle Database dialect documentation
631631

632632
.. _computed_ddl:
633633

@@ -704,9 +704,9 @@ eagerly fetched.
704704

705705
* PostgreSQL as of version 12
706706

707-
* Oracle - with the caveat that RETURNING does not work correctly with UPDATE
708-
(a warning will be emitted to this effect when the UPDATE..RETURNING that
709-
includes a computed column is rendered)
707+
* Oracle Database - with the caveat that RETURNING does not work correctly with
708+
UPDATE (a warning will be emitted to this effect when the UPDATE..RETURNING
709+
that includes a computed column is rendered)
710710

711711
* Microsoft SQL Server
712712

@@ -792,7 +792,7 @@ The :class:`.Identity` construct is currently known to be supported by:
792792

793793
* PostgreSQL as of version 10.
794794

795-
* Oracle as of version 12. It also supports passing ``always=None`` to
795+
* Oracle Database as of version 12. It also supports passing ``always=None`` to
796796
enable the default generated mode and the parameter ``on_null=True`` to
797797
specify "ON NULL" in conjunction with a "BY DEFAULT" identity column.
798798

‎doc/build/core/engines.rst‎

Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -200,13 +200,23 @@ More notes on connecting to MySQL at :ref:`mysql_toplevel`.
200200
Oracle
201201
^^^^^^^^^^
202202

203-
The Oracle dialect uses cx_oracle as the default DBAPI::
203+
The preferred Oracle Database dialect uses the python-oracledb driver as the
204+
DBAPI::
204205

205-
engine = create_engine("oracle://scott:tiger@127.0.0.1:1521/sidname")
206+
engine = create_engine(
207+
"oracle+oracledb://scott:tiger@127.0.0.1:1521/?service_name=freepdb1"
208+
)
206209

207-
engine = create_engine("oracle+cx_oracle://scott:tiger@tnsname")
210+
engine = create_engine("oracle+oracledb://scott:tiger@tnsalias")
208211

209-
More notes on connecting to Oracle at :ref:`oracle_toplevel`.
212+
For historical reasons, the Oracle dialect uses the obsolete cx_Oracle driver
213+
as the default DBAPI::
214+
215+
engine = create_engine("oracle://scott:tiger@127.0.0.1:1521/?service_name=freepdb1")
216+
217+
engine = create_engine("oracle+cx_oracle://scott:tiger@tnsalias")
218+
219+
More notes on connecting to Oracle Database at :ref:`oracle_toplevel`.
210220

211221
Microsoft SQL Server
212222
^^^^^^^^^^^^^^^^^^^^
@@ -693,4 +703,3 @@ these parameters from being logged for privacy purposes, enable the
693703
... conn.execute(text("select :some_private_name"), {"some_private_name": "pii"})
694704
2020-10-24 12:48:32,808 INFO sqlalchemy.engine.Engine select ?
695705
2020-10-24 12:48:32,808 INFO sqlalchemy.engine.Engine [SQL parameters hidden due to hide_parameters=True]
696-

‎doc/build/core/metadata.rst‎

Lines changed: 21 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -296,9 +296,9 @@ refer to alternate sets of tables and other constructs. The server-side
296296
geometry of a "schema" takes many forms, including names of "schemas" under the
297297
scope of a particular database (e.g. PostgreSQL schemas), named sibling
298298
databases (e.g. MySQL / MariaDB access to other databases on the same server),
299-
as well as other concepts like tables owned by other usernames (Oracle, SQL
300-
Server) or even names that refer to alternate database files (SQLite ATTACH) or
301-
remote servers (Oracle DBLINK with synonyms).
299+
as well as other concepts like tables owned by other usernames (Oracle
300+
Database, SQL Server) or even names that refer to alternate database files
301+
(SQLite ATTACH) or remote servers (Oracle Database DBLINK with synonyms).
302302

303303
What all of the above approaches have (mostly) in common is that there's a way
304304
of referencing this alternate set of tables using a string name. SQLAlchemy
@@ -328,14 +328,15 @@ schema names on a per-connection or per-statement basis.
328328
"database" that typically has a single "owner". Within this database there
329329
can be any number of "schemas" which then contain the actual table objects.
330330

331-
A table within a specific schema is referenced explicitly using the
332-
syntax "<schemaname>.<tablename>". Contrast this to an architecture such
333-
as that of MySQL, where there are only "databases", however SQL statements
334-
can refer to multiple databases at once, using the same syntax except it
335-
is "<database>.<tablename>". On Oracle, this syntax refers to yet another
336-
concept, the "owner" of a table. Regardless of which kind of database is
337-
in use, SQLAlchemy uses the phrase "schema" to refer to the qualifying
338-
identifier within the general syntax of "<qualifier>.<tablename>".
331+
A table within a specific schema is referenced explicitly using the syntax
332+
"<schemaname>.<tablename>". Contrast this to an architecture such as that
333+
of MySQL, where there are only "databases", however SQL statements can
334+
refer to multiple databases at once, using the same syntax except it is
335+
"<database>.<tablename>". On Oracle Database, this syntax refers to yet
336+
another concept, the "owner" of a table. Regardless of which kind of
337+
database is in use, SQLAlchemy uses the phrase "schema" to refer to the
338+
qualifying identifier within the general syntax of
339+
"<qualifier>.<tablename>".
339340

340341
.. seealso::
341342

@@ -510,17 +511,19 @@ These names are usually configured at the login level, such as when connecting
510511
to a PostgreSQL database, the default "schema" is called "public".
511512

512513
There are often cases where the default "schema" cannot be set via the login
513-
itself and instead would usefully be configured each time a connection
514-
is made, using a statement such as "SET SEARCH_PATH" on PostgreSQL or
515-
"ALTER SESSION" on Oracle. These approaches may be achieved by using
516-
the :meth:`_pool.PoolEvents.connect` event, which allows access to the
517-
DBAPI connection when it is first created. For example, to set the
518-
Oracle CURRENT_SCHEMA variable to an alternate name::
514+
itself and instead would usefully be configured each time a connection is made,
515+
using a statement such as "SET SEARCH_PATH" on PostgreSQL or "ALTER SESSION" on
516+
Oracle Database. These approaches may be achieved by using the
517+
:meth:`_pool.PoolEvents.connect` event, which allows access to the DBAPI
518+
connection when it is first created. For example, to set the Oracle Database
519+
CURRENT_SCHEMA variable to an alternate name::
519520

520521
from sqlalchemy import event
521522
from sqlalchemy import create_engine
522523

523-
engine = create_engine("oracle+cx_oracle://scott:tiger@tsn_name")
524+
engine = create_engine(
525+
"oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
526+
)
524527

525528

526529
@event.listens_for(engine, "connect", insert=True)

‎doc/build/core/pooling.rst‎

Lines changed: 11 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -509,30 +509,32 @@ particular error should be considered a "disconnect" situation or not, as well
509509
as if this disconnect should cause the entire connection pool to be invalidated
510510
or not.
511511

512-
For example, to add support to consider the Oracle error codes
513-
``DPY-1001`` and ``DPY-4011`` to be handled as disconnect codes, apply an
514-
event handler to the engine after creation::
512+
For example, to add support to consider the Oracle Database driver error codes
513+
``DPY-1001`` and ``DPY-4011`` to be handled as disconnect codes, apply an event
514+
handler to the engine after creation::
515515

516516
import re
517517

518518
from sqlalchemy import create_engine
519519

520-
engine = create_engine("oracle://scott:tiger@dnsname")
520+
engine = create_engine(
521+
"oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
522+
)
521523

522524

523525
@event.listens_for(engine, "handle_error")
524526
def handle_exception(context: ExceptionContext) -> None:
525527
if not context.is_disconnect and re.match(
526-
r"^(?:DPI-1001|DPI-4011)", str(context.original_exception)
528+
r"^(?:DPY-1001|DPY-4011)", str(context.original_exception)
527529
):
528530
context.is_disconnect = True
529531

530532
return None
531533

532-
The above error processing function will be invoked for all Oracle errors
533-
raised, including those caught when using the
534-
:ref:`pool pre ping <pool_disconnects_pessimistic>` feature for those backends
535-
that rely upon disconnect error handling (new in 2.0).
534+
The above error processing function will be invoked for all Oracle Database
535+
errors raised, including those caught when using the :ref:`pool pre ping
536+
<pool_disconnects_pessimistic>` feature for those backends that rely upon
537+
disconnect error handling (new in 2.0).
536538

537539
.. seealso::
538540

@@ -760,4 +762,3 @@ API Documentation - Available Pool Implementations
760762
.. autoclass:: _ConnectionFairy
761763

762764
.. autoclass:: _ConnectionRecord
763-

‎doc/build/core/type_basics.rst‎

Lines changed: 3 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -63,9 +63,9 @@ not every backend has a real "boolean" datatype; some make use of integers
6363
or BIT values 0 and 1, some have boolean literal constants ``true`` and
6464
``false`` while others dont. For this datatype, :class:`_types.Boolean`
6565
may render ``BOOLEAN`` on a backend such as PostgreSQL, ``BIT`` on the
66-
MySQL backend and ``SMALLINT`` on Oracle. As data is sent and received
67-
from the database using this type, based on the dialect in use it may be
68-
interpreting Python numeric or boolean values.
66+
MySQL backend and ``SMALLINT`` on Oracle Database. As data is sent and
67+
received from the database using this type, based on the dialect in use it
68+
may be interpreting Python numeric or boolean values.
6969

7070
The typical SQLAlchemy application will likely wish to use primarily
7171
"CamelCase" types in the general case, as they will generally provide the best
@@ -336,5 +336,3 @@ its exact name in DDL with ``CREATE TABLE`` is issued.
336336

337337

338338
.. autoclass:: VARCHAR
339-
340-

‎doc/build/dialects/oracle.rst‎

Lines changed: 12 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -5,12 +5,12 @@ Oracle
55

66
.. automodule:: sqlalchemy.dialects.oracle.base
77

8-
Oracle Data Types
9-
-----------------
8+
Oracle Database Data Types
9+
--------------------------
1010

11-
As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
12-
valid with Oracle are importable from the top level dialect, whether
13-
they originate from :mod:`sqlalchemy.types` or from the local dialect::
11+
As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid
12+
with Oracle Database are importable from the top level dialect, whether they
13+
originate from :mod:`sqlalchemy.types` or from the local dialect::
1414

1515
from sqlalchemy.dialects.oracle import (
1616
BFILE,
@@ -36,7 +36,7 @@ they originate from :mod:`sqlalchemy.types` or from the local dialect::
3636
.. versionadded:: 1.2.19 Added :class:`_types.NCHAR` to the list of datatypes
3737
exported by the Oracle dialect.
3838

39-
Types which are specific to Oracle, or have Oracle-specific
39+
Types which are specific to Oracle Database, or have Oracle-specific
4040
construction arguments, are as follows:
4141

4242
.. currentmodule:: sqlalchemy.dialects.oracle
@@ -80,17 +80,16 @@ construction arguments, are as follows:
8080
.. autoclass:: TIMESTAMP
8181
:members: __init__
8282

83-
.. _cx_oracle:
84-
85-
cx_Oracle
86-
---------
87-
88-
.. automodule:: sqlalchemy.dialects.oracle.cx_oracle
89-
9083
.. _oracledb:
9184

9285
python-oracledb
9386
---------------
9487

9588
.. automodule:: sqlalchemy.dialects.oracle.oracledb
9689

90+
.. _cx_oracle:
91+
92+
cx_Oracle
93+
---------
94+
95+
.. automodule:: sqlalchemy.dialects.oracle.cx_oracle

‎doc/build/glossary.rst‎

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -298,7 +298,7 @@ Glossary
298298
A key limitation of the ``cursor.executemany()`` method as used with
299299
all known DBAPIs is that the ``cursor`` is not configured to return
300300
rows when this method is used. For **most** backends (a notable
301-
exception being the cx_Oracle, / OracleDB DBAPIs), this means that
301+
exception being the python-oracledb / cx_Oracle DBAPIs), this means that
302302
statements like ``INSERT..RETURNING`` typically cannot be used with
303303
``cursor.executemany()`` directly, since DBAPIs typically do not
304304
aggregate the single row from each INSERT execution together.
@@ -1158,16 +1158,17 @@ Glossary
11581158
values as they are not included otherwise (but note any series of columns
11591159
or SQL expressions can be placed into RETURNING, not just default-value columns).
11601160

1161-
The backends that currently support
1162-
RETURNING or a similar construct are PostgreSQL, SQL Server, Oracle,
1163-
and Firebird. The PostgreSQL and Firebird implementations are generally
1164-
full featured, whereas the implementations of SQL Server and Oracle
1165-
have caveats. On SQL Server, the clause is known as "OUTPUT INSERTED"
1166-
for INSERT and UPDATE statements and "OUTPUT DELETED" for DELETE statements;
1167-
the key caveat is that triggers are not supported in conjunction with this
1168-
keyword. On Oracle, it is known as "RETURNING...INTO", and requires that the
1169-
value be placed into an OUT parameter, meaning not only is the syntax awkward,
1170-
but it can also only be used for one row at a time.
1161+
The backends that currently support RETURNING or a similar construct
1162+
are PostgreSQL, SQL Server, Oracle Database, and Firebird. The
1163+
PostgreSQL and Firebird implementations are generally full featured,
1164+
whereas the implementations of SQL Server and Oracle Database have
1165+
caveats. On SQL Server, the clause is known as "OUTPUT INSERTED" for
1166+
INSERT and UPDATE statements and "OUTPUT DELETED" for DELETE
1167+
statements; the key caveat is that triggers are not supported in
1168+
conjunction with this keyword. In Oracle Database, it is known as
1169+
"RETURNING...INTO", and requires that the value be placed into an OUT
1170+
parameter, meaning not only is the syntax awkward, but it can also only
1171+
be used for one row at a time.
11711172

11721173
SQLAlchemy's :meth:`.UpdateBase.returning` system provides a layer of abstraction
11731174
on top of the RETURNING systems of these backends to provide a consistent
@@ -1702,4 +1703,3 @@ Glossary
17021703
.. seealso::
17031704

17041705
:ref:`session_object_states`
1705-

0 commit comments

Comments
 (0)