The widely used SQLAlchemy “Python SQL Toolkit and Object Relational Mapper” now supports the Oracle Database 23ai VECTOR data type, enabling your AI applications.

The new support for vectors in SQLAlchemy is courtesy of a code contribution from Suraj Shaw, a member of Oracle Database’s driver development group. A big thanks to Mike Bayer and Federico Caselli from the SQLAlchemy team for merging the enhancement into SQLAlchemy 2.0.41.

Vector data in Oracle Database 23ai

Oracle Database 23ai introduced Oracle AI Vector Search as part of the database at no additional charge. Vectors are commonly used in AI to represent the semantics of unstructured data such as images, documents, video, and audio. With the new database feature set, a VECTOR data type was added, being represented as an homogeneous array of 8-bit unsigned integers, 8-bit signed integers, 32-bit floating point numbers, or 64-bit floating point numbers.

Vector columns in Oracle Database 23ai can be created as type:

VECTOR(<vectorDimensions>, <vectorDimensionFormat>)

where the attributes are:

• vectorDimensions: the number of dimensions for the vector data. For example, a point in 3D space is defined by vector data of 3 dimensions, i.e., the (x,y,z) coordinates. For the BINARY vector format, the number of dimensions should be a multiple of 8.
• vectorDimensionFormat: one of the keywords BINARY, INT8, FLOAT32, or FLOAT64 to define the storage format for each dimension value in the vector. The INT8, FLOAT32, or FLOAT64 formats are supported with Oracle Database 23.4. The BINARY format was introduced in Oracle Database 23.5.

Both the number of dimension and format can be “flexible”, allowing vectors in a column to have different shapes.

For example, the SQL to create a table T that includes a VECTOR column called EMBEDDING using FLOAT64 storage is:

CREATE TABLE t (id INTEGER NOT NULL,
                name VARCHAR2(20 CHAR),
                embedding VECTOR(3,FLOAT64),
                PRIMARY KEY (id));

Oracle Database 23.7 introduced the ability to store sparse vectors as an efficient way to represent VECTOR data where most dimensions have zero values. These are not yet supported through SQLAlchemy.

For more information about vectors in the database, refer to the Oracle AI Vector Search User’s Guide.

Requirements

To use VECTORs, you need Oracle Database 23ai. Information at oracle.com/database/free will show you how to access a free cloud database or alternatively install one also for free. Vector support is being improved in each database “release update” so use the latest available version.

Next, you need at least version 2.0.41 of sqlalchemy. You should also update oracledb, which itself has been keeping pace with the database's vector improvements:

python -m pip install sqlalchemy oracledb --upgrade

Creating VECTORs in SQLAlchemy

[The full code of example used in this post is available on GitHub here].

In SQLAlchemy, the following creates the same table schema as earlier shown in SQL*Plus:

      from sqlalchemy import MetaData, Table, Column, Integer, String
from sqlalchemy.dialects.oracle import VECTOR, VectorStorageFormat

metadata = MetaData()

t = Table(
    "t",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(20)),
    Column(
        "embedding",
        VECTOR(dim=3, storage_format=VectorStorageFormat.FLOAT64),
    )
)

Indexes on VECTOR columns can aid performance, see the SQLAlchemy documentation VECTOR Indexes. An example “Inverted File Flat (IVF)” index is:

from sqlalchemy.dialects.oracle import VectorIndexConfig, VectorIndexType, VectorDistanceType

Index(
    "iv

Truncated by Planet PHP, read more at the original (another 5671 bytes)

On the Roave Discord recently, there was a discussion about not breaking BC in interfaces inspired by this post by Jérôme Tamarelle:

It's clearly true that if you add a new parameter to a method on an interface, then that's a BC break as every concrete implementation of the interface needs to change their signature.

However, Gina commented that you don't need to use func_get_arg() as concrete implementations can add additional optional arguments.

WHAT?!!!

I didn't know this and it had never occurred to me to try, so I had to go 3v4l.org and check

<?php

interface Foo
{
    public function bar(int $a): void;
}

class Baz implements Foo
{
    public function bar(int $a, int $b=2): void
    {
        echo "$a: $b";
    }
}

(new Baz)->bar(1,3);

Sure enough, this code works!

I don't have much of a need for this, but it's useful to know.

Python-oracledb 3.0 allows you to name pools when they are created. You can access them later by that name instead of having to pass around a pool handle. This feature is very helpful when your application spans many code files, or consists of independent libraries.

Multi-user (and some single-user) database applications should almost always use a driver connection pool. This has performance, scalability, and reliability benefits. Check out my previous posts on this topic are A driver connection pool, a DRCP pool, or an Implicit Connection Pool? and Always Use Connection Pools — and How.

But when your application spans multiple files, it can be tricky to pass the pool handle between your code modules. In python-oracledb 3.0 we introduced a driver connection pool cache to simplify your life. You can use the pool cache in both python-oracledb Thin and Thick modes with all the Oracle Database versions that python-oracledb supports. The same cache concept has already proven itself in our node-oracledb driver.

To put pool caching into practice, consider the new code connection_pool_pc.py which is a variant of the sample connection_pool.py. (Follow those links to see the full files).

The original connection_pool.py code creates a pool and returns its handle to the rest of the application:

      pool = oracledb.create_pool(
        user=sample_env.get_main_user(),
        password=sample_env.get_main_password(),
        dsn=sample_env.get_connect_string(),
        params=sample_env.get_pool_params(),
        min=pool_min,
        max=pool_max,
        increment=pool_inc,
        session_callback=init_session,
    )

    return pool

The new code in connection_pool_pc.py adds a pool_alias=my_pool_alias parameter to create_pool(). It doesn’t retain, or use, the pool handle returned by create_pool():

      my_pool_alias = 'mypool'

    oracledb.create_pool(
pool_alias=my_pool_alias,
        user=sample_env.get_main_user(),
        password=sample_env.get_main_password(),
        dsn=sample_env.get_connect_string(),
        params=sample_env.get_pool_params(),
        min=pool_min,
        max=pool_max,
        increment=pool_inc,
        session_callback=init_session,
    )

Every time a connection is needed from the pool, the old code:

with pool.acquire() as connection:

is replaced to access the pool directly from the oracledb module:

with oracledb.connect(pool_alias=my_pool_alias) as connection:

The full diff between the files is:

      71a72,73
> my_pool_alias = 'mypool'
> 
88c90,91
<     pool = oracledb.create_pool(
---
>     oracledb.create_pool(
>         pool_alias=my_pool_alias,
99d101
<     return pool
101d102
< 
128c129
<     with pool.acquire() as connection:
---
>     with oracledb.connect(pool_alias=my_pool_alias) as connection:
172c173
<     with pool.acquire() as connection:
---
>     with oracledb.connect(pool_alias=my_pool_alias) as connection:
190c191
<     with pool.acquire() as connection:
---
>     with oracledb.connect(pool_alias=my_pool_alias) as connection:
201c202
<     pool = start_pool()
---
>     start_pool()

The files run identically.

The benefit of pool caching is that modules and libraries that access a pool only need to agree on a name (or names — if you have multiple pools). After importing oracledb, each part of the code can access a pool directly off the imported oracledb module by using the agreed name.

You can also pass options to oracledb.connect() that you might have previously passed to pool.acquire(). The documented example is when you are using a heterogeneous pool where each connection could be a different user. In

Truncated by Planet PHP, read more at the original (another 1879 bytes)

The self-paced python-oracledb tutorial has been refreshed.

Our Python and Oracle Database: The New Wave of Scripting tutorial has had a refresh. This self-paced tutorial shows you how to use the python-oracledb driver to access Oracle Database. It has exercises and solutions. We have run this at conferences and had positive feedback from people new to the Python world. You can try it out on your own computer.

Once you have done the tutorial, you may also be interested in the container buildable from https://github.com/oracle/python-oracledb/tree/main/samples/containers/samples_and_db that installs the general python-oracledb sample scripts in a container with Oracle Database.

If you’re new to this world, check out my colleague’s post and video Exploring python-oracledb: A Simplified Approach to Oracle Database Connectivity.

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. It is used by many frameworks, ORMs, and libraries.

Links:

• Installation instructions
• Documentation
• Discussions
• Source code on GitHub
• PyPI

Password managers are still the best way to manage credentials, and if you are doing things on the web, you should be using one.

Increasingly, websites are using multi-step login forms. They might first ask for just an email address and only after present users with a password field.

Unfortunately, this doesn’t always work well with password managers. Password managers are such an integral part of the web, I think it’s important as web developers to make this work as best as possible. It’s also an accessibility issue.

Why do websites use multi-step login forms?

The short version is that many companies want to centralize their login / password management systems for all their employees. This lets them change passwords in a single place, and also disable accounts after an employee leaves the company. This is called Single Sign-On (SSO) and is often facilitates using the SAML or OpenID Connect protocols.

When you log in with a web application that supports this, and you enter your email, the first thing the system needs to do is check if a SSO system is in place for your account (or the domainname of your email address), and if so they will redirect out to your sign-on system.

Only if this is not the case, they will present you with a password field.

Another reason is that systems increasingly allow users to auhtenticate with means other than a password, and similarly they first need to know your email address / username before they can know what login flow to present you with.

Password managers

Password managers such as KeepassXC, Bitwarden and 1Password, but also the ‘save password’ feature that’s built into browsers look for HTML forms that are roughly ‘login form shaped’ to do their thing. If something looks like a login form, they will suggest or auto-fill your username and password.

The problem with multi-step login forms that ask for an email address first, is that they no longer look like plain old login forms, which either means that a user has to do more clicks to complete the login, or in the worst case the password manager can’t detect the fields at all and you end up copy-pasting your password. This is pretty annoying and something I see people often yelling about on the internet.

So, how do you fix this?

1. Make sure you have the correct autocomplete attribute

If you have a lone <input> field for the username, it should have the attribute to signal password managers that this is a username field:

<input type="text" autocomplete="username" name="username" required />

or:

<input type="email" autocomplete="username" name="username" required />

2. Add a hidden username field on the password page

The Chrome Wiki recommends that when you collect the email first, and redirect the user after to a password page, to include the username field again (prefilled), and hidden with CSS.

<input
    type="email"
    autocomplete="username"
    name="username"
    required
    value="spam@evertpot.com"
    style="display:none"
/>

<input
    type="password"

Truncated by Planet PHP, read more at the original (another 4807 bytes)

The python-oracledb 3.1 release is now available on PyPI.

This is hot on the heels of last month’s big 3.0 release (see New release of python-oracledb 3.0 Fetches Directly to Data Frames, has Thin Mode AQ, supports Centralized Configuration Providers, and more). We had originally intended just to do a 3.0.x bug fix release, but some enhancements were ready to ship, so we decided to share them with you now as a feature release.

The enhancements are:

• Advanced Queueing in Thin mode now supports JSON payloads, and bulk enqueueing and dequeuing. These changes continue the progress towards Thin mode parity with Thick mode. AQ support was also extended to the python-oracledb async API too.
• The python-oracledb data frame query functionality was extended with support for CLOB, BLOB, and RAW data types.
• Support for scrollable cursors was added to python-oracledb Thin mode. Scrollable cursors are now useable in both modes.
• The other notable change was that we dropped support for Python 3.8, which the upstream maintainers already ceased supporting last year.

There are also plenty of bug fixes. Check the release notes for all the details.

Installing or Upgrading python-oracledb

You can install or upgrade python-oracledb by running:

python3 -m pip install oracledb --upgrade

The pip options --proxy and --user may be useful in some environments. See Installing python-oracledb for details.

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is used by frameworks, ORMs, SQL generation libraries, and other projects.

• Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html
• Documentation: python-oracledb.readthedocs.io/en/latest/index.html
• Discussions: github.com/oracle/python-oracledb/discussions
• Source Code: github.com/oracle/python-oracledb

This is a demonstration of how Oracle Database’s Application Continuity feature helps PHP OCI8 applications continue running smoothly during unexpected connection and database outages. Users remain unaware of issues. No complex application error handling logic is needed.

Introduction

Oracle Database’s Application Continuity feature reduces the incidence of application errors by automatically reconnecting and replaying interrupted, in-flight transactions after a database connectivity outage. It restores application state seamlessly. AC masks hardware, software, network, storage errors, and timeouts. Applications continue running and users are unaware of outages. The application’s code doesn’t need unnecessary, complex recovery logic. AC, and its sibling Transparent Application Continuity, are usable with various configurations of Oracle Database, such as RAC and Oracle Autonomous Database.

AC and TAC are supported by many language drivers including PHP OCI8.

Application Continuity is recommended for OLTP applications that use an Oracle Database driver pool (such as the Oracle Call Interface session pool), or for apps that that provide explicit request boundaries. Transparent Application Continuity (TAC) is a functional mode of Application Continuity that doesn’t need the application to use an Oracle session pool. It transparently tracks, and records, sessions and transactional states.

For applications that do use an Oracle Database driver pool, it is your choice whether to use AC or TAC.

To understand all the differences between AC and TAC, and see the best practice information and fine print, check the references at the end of this post.

Demo

Following my earlier blog post Oracle Application Continuity — for continuous availability, I enabled TAC on the “high” service of my Oracle Autonomous Database:

$ sqlplus -l admin@'tcps://adb.melb.oraclecloud.com:1522/abc_cjmtls_high.adb.oraclecloud.com?wallet_location=/Users/cjones/alwaysfree/CJMTLS'

SQL> execute dbms_app_cont_admin.enable_tac('abc_cjmtls_high.adb.oraclecloud.com', 'AUTO', 600);

And checked it was enabled:

      SQL> set pagesize 1000 linesize 150
SQL> col name format a60
SQL> col failover_type format a15
SQL> select name, failover_type from dba_services;

NAME                                                         FAILOVER_TYPE
------------------------------------------------------------ ---------------
ABC_CJMTLS_tp.adb.oraclecloud.com
ABC_CJMTLS_high.adb.oraclecloud.com                          AUTO
ABC_CJMTLS_medium.adb.oraclecloud.com
ABC_CJMTLS_low.adb.oraclecloud.com
ABC_CJMTLS_tpurgent.adb.oraclecloud.com
ABC_CJMTLS

In real life you may prefer to enable it on your ‘TP’ service, per the documentation.

The PHP OCI8 Application

The full PHP app I used is available as a GitHub gist here.

It first prints out the connection’s unique session ID and serial number as a handy “kill” statement that a DBA can use to destroy the connection. This interruption to the running application is how the demo simulates an unplanned connectivity outage:

      // Display the SQL that an administrator can run to kill the connection
$killsql = "select unique 'alter system kill session '''||sid||','||serial#||''';'||'' from v\$session_connect_info where sid = sys_context('USERENV', 'SID')";
$s = oci_parse($c, $killsql);
oci_execute($s);
$r = oci_fetch_row($s);
print("While this script is running, use SQL*Plus to execute:\n   ".$r[0]. "\n");

For example this might print:

While this script is running, use SQL*Plus to execute:
  alter system kill session '16198,41975';

The main code loop inserts some data, shows the connection’s current unique session ID and serial number, and then sleeps for a couple of seconds before committing:

for ($i = 1; $i <= 10; $i++) {

    $data = "a" . $i;
    $s1 = oci_parse($

Truncated by Planet PHP, read more at the original (another 6719 bytes)

Tamás Gulácsi’s excellent godror driver for the Go language now supports the Oracle Database 23ai VECTOR data type, courtesy of a pull request by Sudarshan Soma, a senior member of Oracle Database’s driver development group.

Oracle Database 23ai introduced a VECTOR data type to aid artificial intelligence and machine learning search operations. Vectors are an homogeneous array of 8-bit signed integers, 8-bit unsigned integers, 32-bit floating-point numbers, or 64-bit floating-point numbers. You can optionally define the number of dimensions for the data. Vectors can be “dense” (the default), or “sparse” when data is mostly zeroes.

For example to create a table with two VECTOR columns, one being “dense” containing 20 dimensions of 64-bit floating point numbers, and the other column being a sparse vector of 35 8-bit signed integers:

create table vector_table (
    v64 vector(20, float64),
    v8  vector(35, int8, sparse)
)

Oracle Database 23ai supports a number of advanced operations such as similarity searches on vector embeddings stored as the VECTOR data type. See the Oracle AI Vector Search User’s Guide for all the details.

Here is a basic example in Go that uses godror 0.48 (or later). You also need Oracle Database 23.7 (or later). The code simply inserts and fetches vectors to show the godror side of working with them.

      package main

import (
  "context"
  "database/sql"
  "fmt"
  "github.com/godror/godror"
  "log"
  "math/rand/v2"
  "strconv"
  "time"
)

// Generates a slice of random float32 numbers
func randomFloat32Slice(size int) []float32 {
  slice := make([]float32, size)
  for i := range slice {
    slice[i] = rand.Float32() * 10
  }
  return slice
}

func main() {

  db, err := sql.Open("godror", ` user="scott" password="tiger" connectString="localhost/orclpdb1" `)
  if err != nil {
    log.Fatalf("Failed to connect to database: %v", err)
  }
  defer db.Close()

  ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
  defer cancel()

  dropTable := `DROP TABLE IF EXISTS text_search`
  _, err = db.ExecContext(ctx, dropTable)

  // Create a table with VECTOR columns
  dimensions := 5
  createTable := `CREATE TABLE text_search (
      id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      text_column CLOB NOT NULL,
      dense_embedding VECTOR(` + strconv.Itoa(dimensions) + `),
      sparse_embedding VECTOR(` + strconv.Itoa(dimensions) + `, FLOAT32, SPARSE))`
  _, err = db.ExecContext(ctx, createTable)
  if err != nil {
    log.Fatalf("Error creating table: %v", err)
  }
  fmt.Println("Table created successfully.")

  conn, err := db.Conn(ctx)
  if err != nil {
    log.Fatal(err)
  }
  defer conn.Close()

  stmt, err := conn.PrepareContext(ctx, `INSERT INTO text_search (text_column, dense_embedding, sparse_embedding)
      VALUES (:1,:2,:3)`)
  if err != nil {
    log.Fatal(err)
  }
  defer stmt.Close()

  // Insert some vector data
  float32Vector := godror.Vector{Values: randomFloat32Slice(dimensions)}
  sparseFloat32Vector := godror.Vector{Dimensions: uint32(dimensions), Indices: []uint32{0, 2, 4}, Values: randomFloat32Slice(3)}
  _, err = stmt.ExecContext(ctx, "SAMPLE TEXT1", &float32Vector, &sparseFloat32Vector)
  if err != nil {
    log.Fatal(err)
  }

  float32Vector = godror.Vector{Values: randomFloat32Slice(dimensions)}
  sparseFloat32Vector = godror.Vector{Dimensions: uint32(dimensions), Indices: []uint32{1, 3, 4}, Values: randomFloat32Slice(3)}
  _, err = stmt.ExecContext(ctx, "SAMPLE TEXT2", &float32Vector, &sparseFloat32Vector)
  if err != nil {
    log.Fatal(err)
  }
  fmt.Println("Inserted rows successfully.")

  // Query the vectors
  rows, err := conn.QueryContext(ctx, fmt.Sprintf(`
    SELECT id, text_column, dense_em

Truncated by Planet PHP, read more at the original (another 1954 bytes)

The n8n workflow automation tool describes itself as “a workflow automation tool that combines AI capabilities with business process automation.” Several users have created modules for workflow nodes that connect to Oracle Database. Here I try one of them out for the first time.

n8n requires a Node.js environment. I have this all set up locally so I followed the n8n instructions Install globally with npm. You may want to go down the Docker route instead.

I installed n8n into Node.js using:

$ npm install n8n -g

And then installed the n8n-nodes-oracle-database-parameterization module which seems to be the most advanced of the Oracle Database connectors. (Note this is a 3rd party module — Oracle does not contribute to it). Underneath, it does use Oracle’s node-oracledb driver:

$ npm install n8n-nodes-oracle-database-parameterization

Starting n8n was a simple matter of running:

$ export N8N_RUNNERS_ENABLED=true
$ export N8N_CUSTOM_EXTENSIONS=$(pwd)/node_modules/n8n-nodes-oracle-database-parameterization
$ n8n start

With this in place, I opened the default page http://localhost:5678 in my browser, and chose some credentials. The workflow pane then appeared:

To test out Oracle Database connectivity, I clicked on “Add first step…”. There are a lot of nodes that could be used!:

I searched for Oracle. If you don’t see it, make sure that N8N_CUSTOM_EXTENSIONS is correctly set and restart n8n:

I selected it. As well as adding an Oracle Database node, this will automatically create a default trigger node to start the workflow on a user click.

In the Oracle Database node pane I first clicked on “Select Credential”:

At the top I hovered over the “Oracle Credentials account” field and set a name “cj”:

I then entered my database credentials and connection string, and clicked “Save”:

Back on the Oracle Database pane I entered a SQL statement, here a simple query from the EMP table:

I didn’t add any parameters. I also left the Settings tab unchanged:

Clicking “Test step” produced output, here shown in Tabular form on the right. (N8n also allows you to see the same output as JSON or in a schema form):

Back on the workflow pane I decided to add another Oracle node, by clicking on the “+” sign and again searching for “Oracle” in the node list:

In this node I queried the DEPT table using bind variable placeholders:

To assign values to those placeholders, I clicked “Add Parameter”, set the placeholder name “en”, and simply dragged from the word “DEPTNO” in the “rows” column on the left, into the parameter “Value” field. N8n automatically changed the field to the appropriate syntax, and changed the type to an expression. This connects the output from the workflow’s previous node into the current one:

I added a parameter and did the same for the second bind variable placeholder “dno”, dragging the text “DEPTNO” from the left into the Values field:

Note n8n-nodes-oracle-database-parameterization only supports String and Number binds.

Testing the step showed the query from DEPT worked:

Truncated by Planet PHP, read more at the original (another 3212 bytes)