Contents
introduction
Oracle’s latest release, Oracle Database 23.6, is packed with significant enhancements that empower developers to build more intelligent, efficient, and modern applications. Building on the foundation of Oracle Database 23c, this release goes beyond incremental updates, offering powerful new tools for AI, transaction management, and JSON handling.
Let’s move past the headlines and dive into the technical details, complete with code examples, to see how you can leverage these new capabilities in your projects.
Supercharge Your Apps with AI Vector Search Enhancements
AI Vector Search was a headline feature in 23c, and version 23.6 makes it even more powerful and easier to use. Vectors are mathematical representations of unstructured data (like text, images, or audio), and vector search allows you to find items based on semantic similarity rather than just keywords.
- The Hybrid Vector Index: The Best of Both Worlds:The standout enhancement is the Hybrid Vector Index. This innovative index type combines traditional text search (using CTX_SEARCH) with semantic vector search in a single query. This hybrid approach delivers superior accuracy by understanding both the keywords and the contextual meaning.
- How it works: You can now apply a weight to both the text score and the vector score, allowing you to fine-tune the search results to prioritize keyword relevance, semantic similarity, or a balance of both.
- Code Example:Let's assume we have a table of product descriptions with their corresponding vector embeddings.
SQL
-- First, create the table to hold our product data and vectors
CREATE TABLE product_descriptions (
id NUMBER PRIMARY KEY,
description VARCHAR2(1000),
description_vector VECTOR(1536, FLOAT32) -- Example vector size
);
-- Insert some sample data (vectors would be generated by a model like OpenAI's text-embedding-ada-002)
-- For brevity, we'll imagine the vectors are populated here.
-- Now, create a HYBRID vector index
CREATE VECTOR INDEX product_hybrid_idx ON product_descriptions (description_vector)
ORGANIZATION HYBRID
PARAMETERS ('METADATA_TABLES(description_meta)');
-- The METADATA_TABLES clause links to the text index on the description column
CREATE INDEX product_text_idx ON product_descriptions(description) INDEXTYPE IS CTXSYS.CONTEXT;
-- Now, perform a hybrid search
-- We are looking for products similar to 'powerful and portable laptop'
SELECT
id,
description,
VECTOR_DISTANCE(description_vector, text_to_vector('powerful and portable laptop'), COSINE) as distance,
SCORE(1) as text_score
FROM
product_descriptions
WHERE
-- The VECTOR_COMPARE function now includes a text search component
VECTOR_COMPARE(
description_vector,
text_to_vector('powerful and portable laptop'),
'CTX_SEARCH(description, ''powerful and portable laptop'', ''score_by_relevance'')',
'weight(0.4 * text_score + 0.6 * vector_score)', -- Weighting text and vector scores
10, -- Return top 10 results
COSINE
)
ORDER BY
-- Order by the combined, weighted score
VECTOR_COMPARE_SCORE() DESC;
Sessionless Transactions: Decouple Your Application Logic
This is arguably one of the most significant new features for application architecture.
- Sessionless Transactions :allow a transaction to be detached from its original session, suspended, and then resumed later in a completely different session. This is a game-changer for architectures involving long-running workflows, external API callbacks, or event-driven systems. You no longer need to maintain a persistent, costly database connection while waiting for an external process to complete.
- How it works: You can now suspend a transaction, which returns a unique transaction handle. Your application can then close the database connection. When the external event occurs, a different application process can reconnect to the database and use the handle to resume the transaction right where it left off.
- Code Example:Imagine a scenario where a user initiates an order, but we need to call an external payment gateway before finalizing it.
SQL
--[Session 1: Application Server]
-- Initiates the transaction and suspends it after inserting the initial order status.
DECLARE
v_tx_handle VARCHAR2(128);
BEGIN
-- Begin a new transaction
DBMS_DATABASE.BEGIN_TRANSACTION;
-- Insert the initial order record
INSERT INTO orders (order_id, status, order_date) VALUES (123, 'PENDING_PAYMENT', SYSDATE);
-- Suspend the current transaction and get the handle
v_tx_handle := DBMS_DATABASE.SUSPEND_TRANSACTION;
-- Now we can close this session. The transaction is safely suspended.
-- The application would now call the external payment gateway API with the transaction handle.
-- For this example, we'll just print the handle.
DBMS_OUTPUT.PUT_LINE('Transaction Suspended. Handle: ' || v_tx_handle);
END;
/
-- At this point, Session 1 is closed. The database connection is released.
-- Some time later, the payment gateway sends a callback to our system.
--[Session 2: Callback Processor]
-- A different process receives the callback and resumes the transaction to complete it.
DECLARE
v_tx_handle VARCHAR2(128) := '... a_very_long_transaction_handle ...'; -- The handle from the previous step
BEGIN
-- Resume the transaction using the handle
DBMS_DATABASE.RESUME_TRANSACTION(v_tx_handle);
-- The transaction context is now restored.
-- We can now update the order status.
UPDATE orders SET status = 'PAYMENT_CONFIRMED' WHERE order_id = 123;
-- Commit the now-completed transaction
COMMIT;
DBMS_OUTPUT.PUT_LINE('Transaction for Order 123 Resumed and Committed.');
END;
/
Simplified JSON Handling with JSON Collection Views
Oracle continues to enhance its native JSON capabilities. JSON Collection Views are a new type of read-only view that makes it much simpler to query and analyze collections of JSON objects stored within a column. They essentially project the objects in a JSON array into a relational, row-like structure.
- How it works: You define a view over a JSON column, specifying the path to the array. The view will then present each object in the array as a separate row, with the object's keys available as columns.
- Code Example:Consider a table storing sensor data, where each row contains a device ID and a JSON array of readings.
SQL
-- Create a table to store sensor data
CREATE TABLE sensor_logs (
device_id VARCHAR2(50) PRIMARY KEY,
readings JSON
);
-- Insert a log with multiple readings in a JSON array
INSERT INTO sensor_logs (device_id, readings)
VALUES (
'thermo-01',
'{"readings": [
{"timestamp": "2025-06-18T14:10:00Z", "temperature": 22.5, "humidity": 55},
{"timestamp": "2025-06-18T14:11:00Z", "temperature": 22.6, "humidity": 54},
{"timestamp": "2025-06-18T14:12:00Z", "temperature": 22.7, "humidity": 56}
]}'
);
-- Create a JSON Collection View to easily query the readings
CREATE JSON COLLECTION VIEW sensor_readings_view FOR sensor_logs.readings.readings
COLUMNS (
device_id VARCHAR2(50) PATH '$.deviceId', -- Correlate with the base table
reading_time TIMESTAMP WITH TIME ZONE PATH '$.timestamp',
temperature NUMBER PATH '$.temperature',
humidity NUMBER PATH '$.humidity'
);
-- Now, query the view just like a regular relational table!
-- Find all readings where the temperature was above 22.6
SELECT
device_id,
reading_time,
temperature
FROM
sensor_readings_view
WHERE
temperature > 22.6;
Conclusion
Oracle Database 23.6 is a feature-rich release that directly addresses the needs of modern developers. The advancements in AI Vector Search, the architectural flexibility offered by Sessionless Transactions, and the simplified querying with JSON Collection Views provide powerful new patterns for building sophisticated applications.
At MillionLogics, we believe that mastering these tools can significantly accelerate development cycles and unlock new possibilities. We encourage our fellow developers to download the Oracle Database 23.6 Free edition and start experimenting with these exciting new features today.
At MillionLogics, we believe that mastering these tools can significantly accelerate development cycles and unlock new possibilities. We encourage our fellow developers to download the Oracle Database 23.6 Free edition and start experimenting with these exciting new features today.
