begin
execute immediate q'~
create table my_sparse_vectors(
vec vector(4, int8, sparse)
)
~';
end;
/
ORA-00406: COMPATIBLE parameter needs to be 23.6.0.0.0 or greater
ORA-00722: Feature "SQL VECTOR SPARSE type"You can’t always get what you want…. Sometimes new features that come with a release update require the compatible initialization parameter to be set to a specific release or greater.
VirtualBox 23ai
Setting this parameter from a privileged account in a pluggable database won’t work:
ALTER SYSTEM SET COMPATIBLE = '23.7.0.0' SCOPE=SPFILE;
Error report -
ORA-65040: Operation is not allowed from within a pluggable database.
https://docs.oracle.com/error-help/db/ora-65040/65040. 00000 - "Operation is not allowed from within a pluggable database."
*Cause: An operation was attempted that can only be performed in the root
or application root container.
*Action: Switch to the root or application root container to perform the
operation.If you are running a 23AI Free database on virtual box, you will have to look at the /opt/Oracle/product/23ai/dbhomefree/network/admin/tnsnames.ora file to discover that the CDB service name is FREE. The PDB service name is FREEPDB1 as noted in the readme file that comes on the home desktop in the virtual appliance.
Reconnect as the sys user to the CDB and the alter system command works:
ALTER SYSTEM SET COMPATIBLE = '23.7.0.0' SCOPE=SPFILE;
System altered.The 23.7 release of virtual box development environment actually comes with the compatible parameter set to 23.6.0.0, so its not necessary to do anything to work with sparse vector storage in this environment, but its good to know how. The 23.6 release on VirtualBox also had this parameter already set.
OCI Autonomous Database
Since the compatible parameter is already set in Virtualbox releases of 23ai, you won’t be able to reproduce the compatible error there. If you are doing your research in the OCI Autonomous Database with an Always Free instance, the story is a bit longer.
When the feature for sparse vector storage was first announced in the release notes documentation, the OCI database would not allow creating a table with sparse vectors with an odd error:
Error report -
ORA-45493: operation was unsupported during rolling upgrade
ORA-00722: Feature "SQL VECTOR SPARSE format"
45493. 00000 - "operation was unsupported during rolling upgrade"
*Document: NO
*Cause: An unsupported operation was attempted during logical standby
rolling upgrade.
*Action: Wait until the rolling upgrade has completed or clear the
BLOCK_UNSUPPORTED rolling upgrade parameter and retry
the rolling upgrade. Then retry the operation.Since having a free OCI account with a couple of Always Free database instances also gives me access to My Oracle Support and the ability to file Service Requests, I reported this bug on the OCI platform and the development team had resolved it around the time the 23.7 virtual box was released.
Trying to test if the ‘rolling upgrade’ issue was resolved led to the new compatible parameter issue that I introduced this post with.
The interesting thing about the Autonomous Database is that there is no access to the CDB at all. I tried to alter the system with an account that I had given PDB DBA privileges, but that doesn’t work:
ORA-01031: insufficient privilegesLogging in with the primary ADMIN account also failed due to insufficient privileges.
By this time I received a reply on my Service Request about the new insufficient privileges error and the development team confirmed that there would be no way to set the compatible parameter in the Autonomous Database and they are currently working on this issue.
I will update this post when I get a solution from support for creating tables with sparse vector storage. The solution should be interesting, either a sufficiently privileged account should be able to set the parameter or the automatic release updates for autonomous database will set the compatible parameter as well.
Summary
If you try sometimes, you get what you need… Back in my local VirtualBox environment all is well and I am happily researching the Sparse vector storage feature:
begin
execute immediate q'~
create table my_sparse_vectors(
vec vector(4, int8, sparse)
)
~';
end;
/
Table MY_SPARSE_VECTORS created.In addition to understanding how to set the compatible parameter in an on-premises database instance, the main takeaway from this post should be an understanding of how much Oracle has empowered the individual developer at this point in time.
Unlike previous versions, there have been regular releases of the new release updates on the 23ai Free VirtualBox appliances.
select banner_full from v$version;
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.7.0.25.01With an Always Free autonomous database, its free to access the Enterprise Edition release on the OCI cloud and automatically get release updates and patches.
select banner_full from v$version;
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.7.0.25.03"The liveSQL site has also been upgraded to Oracle 23ai, exposing the new Extreme Performance Enterprise Edition with some limitations due to the implementation of liveSQL. This is a great site allowing access to a session running on the Oracle database with a simple website that is worth a separate post exploring the capabilities exposed here.
select banner_full from v$version;
Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07With a free OCI cloud account developers have the unprecedented privilege of creating Service Requests with Oracle support. Previously, this could only be done for customers who purchased Oracle database licenses.
Oracle continues to make their database technology freely available to developers who want to research the capabilities and features of the database and Pl/sql themselves before introducing a technique in their workplace environment.
As Oracle developers, we are the first to explore new features on the cutting edge. Because of this, we can run into bugs first, before they are discovered in production environments. Allowing individual developers access to filing Service Requests is the best part of a free OCI account, and the best way for Oracle to continue leading the way in database technology.
Enjoy your explorations into the groundbreaking new features of the Oracle database, and create an OCI account for yourself so that you can help pave the way for everyone to use these new features. As a global community of developers, we can lend our combined strength to helping Oracle database continue to be the database of choice for any enterprise.
–Anthony Harper
Note: Update as of April 17, 2025
It looks like there is a new update for OCI Autonomous database, we are now on 23.8!
select banner_full from v$version;
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.8.0.25.05Looking at the compatible parameter, it has been updated to 23.5:
SELECT value
FROM v$parameter
WHERE name = 'compatible';
23.5.0 Creation of tables with sparse vector storage now works without issue on OCI:
create table if not exists quiz_vectors(
v vector(4,int8, sparse)
)
/
Table QUIZ_VECTORS created.Great, now OCI Autonomous database supports tables with sparse vector storage! This already works on the 23.7 VirtualBox. Time to work on researching sparse vector storage!
–Anthony Harper
Practical Pl/Sql 
Leave a reply to iudithd5bf8e4d8d Cancel reply