What’s new for Developers in Oracle Database 23.5 

For more information on what’s new in Oracle Database 23ai, see: Oracle Database 23 New Features Guide.

Table of Contents

• Binary VECTOR dimensions
• JSON Collections
• TIME_AT_DBTIMEZONE Parameter
• JSON_ID SQL Function

Binary VECTOR dimensions

The VECTOR data type enables you to store the output of embedding models in the database. The default dimension format for these is float32.

A binary vector stores the values as packed uint8 bytes, meaning that a single byte represents exactly 8 BINARY dimensions. The number of dimensions in a binary vector must be a multiple of 8.

The advantages of this format are:

• The storage footprint of vectors can be reduced by a factor of 32 compared to the default float32 format.  
• Distance computations between two vectors are up to 40 times faster. 

The downside of binary vectors is that they can be less accurate. However, the loss is often not very substantial, and their accuracy often remains greater than 90% compared to float32 vectors. 

The following example creates a table with a binary vector column that has 16 binary dimensions. The second and third insert statements fail because: 

• The second statement includes the value 256, which is outside the range of a single byte (0-255) and is too big to fit in the first dimension.
• The third statement has three values in the array. This corresponds to 3 bytes, which is greater than the 16 dimensions, i.e., the 2-byte limit defined for the column.

create table my_vectors (
  id        integer,
  embedding vector ( 16, binary )
);
Table MY_VECTORS created.

insert into my_vectors values ( 1, '[1,2]' );
1 row inserted. 

insert into my_vectors values ( 2, '[256,2]' );
ORA-51806: Vector column is not properly formatted (dimension value 1 is outside the allowed precision range).

insert into my_vectors values ( 3, '[1,2,3]' ); 
ORA-51803: Vector dimension count must match the dimension count specified in the column definition (actual: 24, required: 16). 

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/binary-vectors.html

JSON Collections

Oracle Database 21c added the JSON data type to optimize and enhance the storage of JSON data. JSON collection tables added in 23.5 further simplify storing JSON values.

JSON collection tables are special, one-column tables that store JSON data in a document-store-compatible format. They include a mandatory _id attribute whose values must be unique within a collection.

When you store JSON documents in a collection without an _id, the database will autogenerate this with a unique value.

The database will also store a hash of the document in a _metadata.etag attribute if you use the with etag clause when creating the collection.

For example:

CREATE JSON COLLECTION TABLE json_data WITH ETAG;

INSERT INTO json_data VALUES ( ' { "attr" : "value" }' );

SELECT * FROM json_data;

__________________________________________________
{
  "_id" : "681a97e15b9acc7ee3118613",
  "_metadata" :
  {
    "etag" : "3480C81186147EE3E0635100000A9ACC"
  },
  "attr" : "value"
}

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-collections.html

TIME_AT_DBTIMEZONE Parameter

By default, the time functions SYSDATE and SYSTIMETAMP return values from the operating system on which the database runs. If the server is running on New Zealand time and the database has a US West Coast time zone, this can lead to unexpected results.

With the time_at_dbtimezone parameter, you can change these functions to report their time using the PDB’s time zone instead.

For example, this changes a database running in UTC time to use India Standard Time (+05:30). This changes the time reported from around 1:30pm to just after 7pm the same day:

SELECT SYSDATE, SYSTIMESTAMP, DBTIMEZONE;

SYSDATE                SYSTIMESTAMP                           DBTIMEZONE
______________________ ______________________________________ _____________
2025-04-29 23:46:58    29-APR-25 11.46.58.375292000 PM GMT    +00:00

ALTER SYSTEM SET time_at_dbtimezone = database SCOPE = spfile;
System altered.

ALTER DATABASE SET time_zone = 'Asia/Kolkata';
Database altered.

shutdown -- need to restart for parameter to take effect
Pluggable Database closed
startup
Pluggable Database opened

SELECT SYSDATE, SYSTIMESTAMP, DBTIMEZONE;

SYSDATE                SYSTIMESTAMP                                    DBTIMEZONE
______________________ _______________________________________________ _______________
2025-04-30 05:17:45    30-APR-25 05.17.45.952229000 AM ASIA/KOLKATA    Asia/Kolkata

Values for the time_at_dbtimezone parameter are: 

• OFF: For all time-dependent operations, the database uses the time zone of the database host system that was in effect when the database was started. This is the behavior in releases prior to Oracle Database 23ai. 

• USER_SQL: The SQL functions SYSDATE and SYSTIMESTAMP return the date and time based on the DBTIMEZONE setting for the PDB. For all other time-dependent operations, the database uses the time zone of the database host system that was in effect when the database was started. 

• DATABASE: For all time-dependent operations, the database uses the time zone specified by the DBTIMEZONE setting for the PDB. These operations include the job scheduler, materialized view refreshes, and Flashback operations. 

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/TIME_AT_DBTIMEZONE.html

JSON_ID SQL Function

Oracle Database has long had the sys_guid function for generating 16-byte unique identifiers. How this generates values is platform-dependent. The json_id function in 23.5 gives you more options for creating unique values. 

It has one parameter which controls the format of its output: 

• OID: a 12-byte raw that is compatible with the Oracle Database API for MongoDB.
• UUID: a 16-byte raw that respects the IETF Universally Unique IDentifiers (UUIDs) proposed standard, RFC 9562. 

For example:

SELECT
  json_id ( 'OID' ) raw_12_byte,
  json_id ( 'UUID' ) raw_16_byte;

RAW_12_BYTE                 RAW_16_BYTE
___________________________ ___________________________________
681166880875B20110DB9986    6B99E7C0A0B04F68BF0487270315DFBD

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-collections.html

This blog post was co-authored with Chris Saxon.

This post was originally published at: https://blogs.oracle.com/developers/post/new-for-developer-oracle-database-23-5