What’s new for Developers in Oracle Database 23.6

By /

For more information on what’s new in Oracle Database 23.5, see: What’s new for Developers in Oracle Database 23.5

Table of Contents

Sessionless Transactions

When you build systems that use external APIs, transactions may need to mix calls to your database and the external interfaces. For example:

  1. Fetch data from the database
  2. Call an external API
  3. Write data to the database
  4. Call another external API
  5. Write data to the database 

To ensure that your database data is transactionally consistent, you need to keep the database connection open all the time. This allows you to roll back the changes if there are any errors in the process. 

While calling external APIs, the database connection is idle. This wastes resources.  

The alternative was to commit your work after each database call so you could release the connection. But this made rollbacks difficult if there was an error – you had to write code to reverse database changes made up to that point. 

Sessionless transactions in 23.6 resolve this challenge. You can start a transaction in one session, suspend it, then resume and complete the same transaction in a different session. A transaction is no longer bound to a given session, making it sessionless. To start a sessionless transaction, a transaction start call is required – your existing transactions that you have loved and used up until now stay exactly the same as they have always been.

For example, this starts a sessionless transaction in session 214, inserts a row into table my_table, then suspends the transaction:

drop table if exists my_table;
create table my_table (id int, text varchar2(255));

set serveroutput on;
DECLARE
  -- define a name for your transaction (alternatively, you can just pass in NULL and have one generated)
  v_xid_raw RAW(21) := UTL_RAW.CAST_TO_RAW('your_transaction_name');
  -- Variable for generated binary transaction id representation
  v_xid_chars  VARCHAR2(42); -- double the length of your transaction name as the string representation is hexadecimal
BEGIN
  v_xid_chars :=
    DBMS_TRANSACTION.START_TRANSACTION (
      v_xid_raw,                                      -- txn name in binary format
      DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS,  -- txn type
      30,                                             -- txn timeout
      DBMS_TRANSACTION.TRANSACTION_NEW                -- txn state
    );

  DBMS_OUTPUT.PUT_LINE('-- v_xid_chars: ' || v_xid_chars);
END;
/
-- v_xid_chars: 796F75725F7472616E73616374696F6E5F6E616D65

insert into my_table (id, text) values (1, 'Gerald');

select t.*, sys_context ( 'USERENV', 'SID' ) sid FROM my_table t;

   ID TEXT      SID
_____ _________ ______
    1 Gerald    214

-- Suspend current transaction (it's not committed nor rolled back)
exec DBMS_TRANSACTION.SUSPEND_TRANSACTION;

At this point, the change to my_table is uncommitted. Querying this table in session 179 below yields no rows. However, you can now resume the transaction in this second session, see the changes from session 214, and commit them: 

SELECT count(*) c, sys_context ( 'USERENV', 'SID' ) sid FROM my_table;

   C SID
____ ______
   0 179       -- new session with a different session ID (SID) seeing no rows in the table yet

set serveroutput on;
DECLARE
  v_xid_raw    RAW(21) := UTL_RAW.CAST_TO_RAW('your_transaction_name');
  v_xid_chars  VARCHAR2(42);
BEGIN
  -- Resume named sessionless transaction, this name matches the one from the START_TRANSACTION call
  v_xid_chars :=
    DBMS_TRANSACTION.START_TRANSACTION (
      v_xid_raw,
      DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS,
      30,
      DBMS_TRANSACTION.TRANSACTION_RESUME
    );

  DBMS_OUTPUT.PUT_LINE('-- v_xid_chars: ' || v_xid_chars);
END;
/
-- v_xid_chars: 796F75725F7472616E73616374696F6E5F6E616D65    -- same transaction id as from previous session

SELECT * FROM my_table;

   ID TEXT
_____ _________
    1 Gerald          -- resumed transaction => can see not yet committed rows inserted by session 214

commit;

Using sessionless transactions simplifies your transaction coordination code and frees up database resources.

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/developing-applications-sessionless-transactions.html

Hybrid Vector Index

Vector search enables you to do semantic searches to find data related to your search terms. In many cases, this gives a better experience when you don’t need to know the exact keywords. If you do want specific keywords to be in the results, you need to filter the data further.

Oracle Text has powerful string search capabilities to help you find data with specific keywords. It is great when you know what you’re looking for, but it can lead to you running many searches with variations on the keywords if you’re unsure what’s in the data.

Hybrid vector indexes enable you to combine both of these capabilities in one index. You can define which search terms can use semantic search and which can use keyword search.

For example, this creates a Hybrid Vector Index on the products table in the SH sample schema. It uses the LLM model ALL_MINILM_L12_V2 loaded into the database (see Import ONNX Models into Oracle Database End-to-End Example for more information on how to load ONNX models into the database) to vectorize the product names:

create hybrid vector index prod_name_i
  on products ( prod_name )
  parameters ( 'model ALL_MINILM_L12_V2' );

You can then use the dbms_hybrid_vector package to combine keyword and semantic search. These two queries both find the product “Shin Guards” in different ways. The first uses semantic search for the term “protector” and keyword search for “shin”. The second uses semantic search for the term “leg” and keyword search for “guards”:

set long 10000 pagesize 1000

select json_serialize (
  dbms_hybrid_vector.search (
   json ( '{ 
    "hybrid_index_name" : "prod_name_i",
    "search_scorer"     : "rsf",
    "search_fusion"     : "INTERSECT",    
    "vector"            : { "search_text" : "protector" },
    "text"              : { "contains"    : "shin" },
    "return"            : { "topN"        : 10 }
   }' ) ) 
  returning clob pretty 
) shin_protector_matches;

SHIN_PROTECTOR_MATCHES
________________________________
[
  {
    "rowid" : "AAATYAABeAAECT2AAq",
    "score" : 56.59,
    "vector_score" : 61.35,
    "text_score" : 9,
    "vector_rank" : 3,
    "text_rank" : 1,
    "chunk_text" : "Shin Guards",
    "chunk_id" : "1"
  }
]

select json_serialize (
  dbms_hybrid_vector.search (
   json ( '{ 
    "hybrid_index_name" : "prod_name_i",
    "search_scorer"     : "rsf",
    "search_fusion"     : "INTERSECT",    
    "vector"            : { "search_text" : "leg" },
    "text"              : { "contains"    : "guards" },
    "return"            : { "topN"        : 10 }
   }' ) ) 
  returning clob pretty 
) leg_guards_matches;

LEG_GUARDS_MATCHES
________________________________
[
  {
    "rowid" : "AAATYAABeAAECT2AAq",
    "score" : 54.24,
    "vector_score" : 58.76,
    "text_score" : 9,
    "vector_rank" : 12,
    "text_rank" : 1,
    "chunk_text" : "Shin Guards",
    "chunk_id" : "1"
  }
]

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/manage-hybrid-vector-indexes.html

Hidden Fields in JSON-Relational Duality Views

You can use expressions to generate the value of fields in JSON-relational duality views. These values can be based on other fields, which are hidden from the document returned by the view.

Hiding fields only used to calculate values for other fields can simplify the documents returned.

In this example, an employee is deemed a high earner if their compensation is greater than the average for their department. The JSON-relational duality view calculates the highEarner fields by comparing the generated fields totalComp and deptAvgSalary. To simplify the document returned, deptAvgSalary is marked as a hidden field and so omitted from the view’s output:

create or replace json relational duality view employee_dv as
  select json {
    '_id'            : employee_id,
    'departmentId'   : department_id,
    'totalComp'      : generated using ( 
      e.salary + coalesce ( e.commission_pct, 0 ) 
    ),
    'deptAvgSalary'  : generated using (
      avg ( salary + coalesce ( e.commission_pct, 0 ) ) 
        over ( partition by department_id )
    ) hidden,
    'highEarner'     : generated using path '
      $.totalComp > $.deptAvgSalary
    '
  }
  from employees e;

select json_serialize(data returning clob pretty) data
 from employee_dv d
  where d.data.departmentId = 20;

DATA
------------------------------------------------------------
{
  "_id" : 201,
  "_metadata" :
  {
    "etag" : "3FE26039DB956B97357A852B38A56
2F5",
    "asof" : "000000000354B88E"
  },
  "departmentId" : 20,
  "totalComp" : 13000,
  "highEarner" : true
}

{
  "_id" : 202,
  "_metadata" :
  {
    "etag" : "9BB62124C72D4256AA05BF37CCBCB
668",
    "asof" : "000000000354B88E"
  },
  "departmentId" : 20,
  "totalComp" : 6000,
  "highEarner" : false
}

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/jsnvu/generated-fields-hidden-fields.html

This blog post was co-authored with Chris Saxon.

This blog post was originally published at: https://blogs.oracle.com/developers/post/whats-new-for-developers-in-oracle-database-23-6

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top