Skip to content

AI for free with Oracle

AI and Oracle for free? really?

In this blog post I will describe a use-case with a free AI model provided by Oracle that can be used in the 26ai free edition.

The use-case is: I have a table with additional infos about bands (country, genre, homepage, facebook, youtube, …). And I have a table with events (concerts, festivals) and the bands playing there. If the bandname matches the bandname in the bands table, then I can see the additional info. And sure, I do not have additional infos for all bands. But maybe there are just bands with different spellings.

Theory

The classical way

Let’s see what Oracle already offered to find similar content

lower

Compare the data case-insensitive. If it is written “Meier” or “meier”, that is the same for us

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
select name from persons where lower(name) = 'meier';

like

We will use placeholders for parts we are not interested in. If the band is written “the young gods” or “young gods”, it’s probably the same

select * from bands where band like '%young gods'

regexp_like

more powerful than like, using regular expressions. The following is more precise. At the beginning must be 0 or 1 time “the “.

select * from bands where regexp_like(band, '(^the\s+)?young gods');

Soundex

Start with the 1st char, then calculate an unique code for similar words. “Meier” and “Mayer” both maps to soundex M600. The disadvantage is that only the first few characters are evaluated.

select name from persons where soundex(name) like soundex('meier');

Return data with similar content – with AI vector search. For example, we have images with animals and want to search all images with a horse.

We have to create an algorithm to create vectors of the image. This is the so-called “model”. And it is not easy to create such a model. Imagine a horse, a donkey, and a mule. What are the differences between them? In comparison, a duck is easy to distinguish. So our model must provide a significantly different vector for the duck than for the other animals. “We”, or at least most of us will use existing models…

And now, how we can retrieve images of horses? Calculate the vector of a horse image, then return the data having the smallest vector distance to our calculated vector.

Usually vectors gets quite big. There are more attributes for animals, e.g. color, size, skin/fur, …
And a vector is calculated for each of these features

The all-MiniLM-L 12-v2 model

Oracle offers the all-MiniLM-L 12-v2 model for download. The file is in the ONNX format and can be loaded in the 26ai database. Download the file to /u01/models in this example.

create user if not exists vector identified by vector quota unlimited on users;
grant create session, db_developer_role, create mining model to vector;
create or replace directory model_dir as '/u01/models';
grant read, write on directory model_dir to vector;
connect vector/vector@//localhost:1521/orclpdb.erfinder
begin
  dbms_vector.drop_onnx_model (model_name => 'ALL_MINILM_L12_V2', force => true);
  dbms_vector.load_onnx_model (
  directory => 'model_dir',
  file_name => 'all_MiniLM_L12_v2.onnx',
  model_name => 'ALL_MINILM_L12_V2');
end;
/
select model_name, algorithm, mining_function from user_mining_models;
select vector_embedding(all_minilm_l12_v2 using 'Bob Dylan' as data) AS my_vector;
-- To grant it for other users:
grant select on mining model vector.ALL_MINILM_L12_V2 to myuser;
[-3.28829587E-002,7.12544471E-002,-4.57076691E-002,3.34124677E-002,-1.0640244E-002,4.99891154E-002,1.04386434E-001,-6.72798455E-002,-7.81936664E-003,-8.73053167E-003,-2.36021634E-002,2.01056106E-003,-2.46723387E-002,-5.60818836E-002,-3.7508145E-002,1.28634855E-001,5.22060208E-002,-8.21595732E-003,1.95498653E-002,-5.5897072E-002,2.00462937E-002,1.13573E-001,-1.04404532E-003,4.60094661E-002,-2.34374423E-002,3.59080993E-002,-1.83110982E-002,4.1176632E-002,-2.09390521E-002,-4.84892018E-002,-6.01600558E-002,1.95941841E-003,-9.55247506E-002,-4.36511822E-002,2.03858018E-002,5.24709793E-003,-8.09903815E-002,2.58681905E-002,8.45895056E-003,-5.15323766E-002,2.27247402E-002,-3.28555144E-002,-2.27001943E-002,-5.32310866E-002,-9.1472546E-005,-1.03046363E-002,-1.69764727E-003,-1.54329734E-002,4.56825159E-002,7.77621344E-002,3.46121155E-002,-7.57156834E-002,-3.00063612E-003,-4.53864262E-002,-3.84752005E-002,-6.36761934E-002,-2.88341157E-002,5.90838827E-002,-8.12250748E-003,-4.64118458E-002,-3.17675695E-002,-4.25537676E-002,-2.16702688E-002,2.84322235E-003,8.61015916E-003,-2.14451943E-002,8.40565786E-002,-5.86571507E-002,-9.2464231E-002,1.56575125E-002,-3.73052713E-003,8.10047705E-003,1.00164764E-001,-6.44701393E-003,4.39257845E-002,-1.54028821E-003,5.98016754E-002,1.09822132E-001,-2.7124064E-002,-4.46104561E-004,3.20078549E-003,2.79765911E-002,5.03437296E-002,-8.46643522E-002,-9.36391726E-002,-6.73487186E-002,1.84180625E-002,9.63722449E-003,1.24550518E-002,-8.65510926E-002,-7.59489909E-002,1.26308557E-002,3.50978156E-003,-6.28657341E-002,-1.58169977E-002,7.58793652E-002,3.8126424E-002,-5.30581661E-002,-6.27498478E-002,2.64155805E-001,-8.89125839E-003,4.76834886E-002,-3.44932079E-002,3.28368917E-002,4.07917947E-002,-2.3367377E-002,-4.72930893E-002,4.23461609E-002,3.70606966E-003,-4.84246351E-002,3.95867899E-002,-6.20418042E-003,-5.71911037E-002,2.7675597E-002,1.08781736E-002,-8.90007392E-002,2.75215507E-002,-1.67274829E-002,-1.55357672E-002,9.31895003E-002,-5.24692051E-002,3.33056785E-003,-6.93096919E-003,-1.27648907E-002,-1.31879762E-001,-5.87911345E-002,-3.01394444E-002,-1.30496789E-002,1.229118E-002,-1.03035718E-002,3.59751955E-002,-6.99997321E-002,1.10877313E-001,-2.81976648E-002,-6.04905449E-002,3.60108092E-002,-1.06216678E-002,-4.39494215E-002,7.80562013E-002,-1.6695112E-001,1.50056444E-002,7.97785446E-002,2.97659002E-002,4.84109372E-002,-2.56663887E-003,-6.71984777E-002,3.34760435E-002,-2.94934344E-002,-9.33799371E-002,-2.78656222E-002,-4.63584922E-002,-1.08145699E-002,7.80638028E-003,-2.63266789E-004,-8.88938643E-003,3.12825069E-002,6.60058409E-002,2.45609395E-002,2.61974223E-002,4.66002934E-002,5.83518781E-002,1.80750992E-003,-2.74173599E-002,-3.43227871E-002,-2.48813797E-002,5.47519652E-003,-5.82680777E-002,-7.43228644E-002,-1.88004076E-002,-1.98282674E-003,2.54240092E-002,-2.25611627E-002,-6.18117265E-002,7.44435415E-002,9.6341148E-003,1.5786672E-002,-1.04088732E-003,3.61631215E-002,-3.38860713E-002,-4.13281173E-002,-4.50802706E-002,1.95613988E-002,-1.99177042E-002,5.17900707E-003,4.90007438E-002,5.03397081E-003,-6.19004597E-004,-1.13240853E-002,-1.02219107E-002,8.01912174E-002,7.19214156E-002,4.65811603E-002,3.64658721E-002,-4.94964374E-003,2.24289093E-002,-6.02491722E-002,7.99450616E-004,-7.01370239E-002,-7.43662044E-002,-5.69670312E-002,4.62124683E-002,-4.12194617E-002,8.31926242E-003,-9.16962977E-003,5.90781793E-002,-8.93979073E-002,2.46489439E-002,2.81920489E-002,3.73301096E-002,-2.34126672E-002,-2.66622845E-002,-4.46192324E-002,-1.0867619E-002,-3.94763537E-002,-6.28683269E-002,-9.89502668E-002,2.84670247E-003,-4.54771668E-002,-5.41057028E-002,1.49319759E-002,-8.64058435E-002,-2.36107074E-002,-8.96388739E-002,7.36331425E-033,-1.30065847E-002,9.65155736E-002,8.05562809E-002,7.79263452E-002,1.354397E-002,-1.92665216E-002,1.22084673E-002,-4.27422523E-002,5.58627024E-002,4.50925622E-003,-2.63720471E-002,1.77200157E-002,3.12984958E-002,-5.7092566E-002,4.52824384E-002,2.39689071E-002,-6.85803741E-002,8.76181796E-002,7.43384063E-002,3.8558092E-002,3.15629202E-003,3.26407105E-002,4.78902496E-002,1.39964083E-002,-8.43583196E-002,-4.61399555E-002,-9.19615477E-003,7.43216872E-002,5.51957591E-003,-2.65764855E-002,3.59744169E-002,2.61799637E-002,-1.89742539E-002,-4.89917509E-002,-9.24255606E-003,2.38266308E-002,5.41158468E-002,5.97609766E-002,5.02838008E-002,-3.57549414E-002,6.40272647E-002,5.00370339E-002,-3.69527489E-002,4.64274175E-002,2.49757692E-002,-6.76745037E-003,8.21680669E-003,5.42990454E-002,-3.16958688E-002,1.39125353E-002,-6.24617971E-002,2.69391462E-002,1.52521878E-002,1.6519472E-002,-1.17503544E-002,-4.99484092E-002,4.65028994E-002,2.52046362E-002,2.21878756E-002,-1.3119689E-003,-6.08432367E-002,3.77141424E-002,4.42493074E-002,-3.14246006E-002,6.79688305E-002,-8.38463474E-003,-4.85547185E-002,4.65472452E-002,-2.1941036E-002,-4.92079766E-004,-5.7777293E-002,2.28965795E-003,-8.90555829E-002,2.1388296E-002,-2.90373433E-002,6.60333186E-002,2.44755242E-002,-1.03102364E-001,-5.32066338E-002,3.33813578E-002,-5.51838949E-002,2.94785537E-002,-6.78635314E-002,3.5423249E-002,-2.19605342E-002,-4.11156788E-002,7.0969604E-002,-5.22063114E-002,3.73266009E-003,1.00158148E-001,4.24587019E-002,-7.08252415E-002,-2.02396E-003,9.50025488E-003,2.54774839E-002,2.5477032E-035,1.43452147E-002,5.16638942E-002,-1.19648194E-002,-2.49146926E-003,1.24402598E-001,5.28191328E-002,1.02975033E-001,-4.78652567E-002,-5.45930974E-002,1.24237491E-002,1.61334034E-002,-7.59155899E-002,3.52150798E-002,4.61573005E-002,8.28202143E-002,5.92956669E-004,-8.56052712E-002,-1.63979232E-002,8.12467374E-003,2.49185823E-002,7.56010786E-002,8.44534859E-002,8.50686207E-002,-3.74653749E-002,5.28270602E-002,2.15788018E-002,3.67684476E-002,8.69528204E-003,-3.50426733E-002,5.644219E-002,2.85430346E-002,6.9013454E-002,-8.73260349E-002,8.75519589E-002,-1.54081145E-002,-6.64589033E-002,1.22624952E-002,-5.2193813E-002,5.41517045E-003,4.24271524E-002,-2.26328755E-003,1.00552432E-001,1.41395396E-002,2.75035575E-003,3.79429497E-002,-3.93360481E-002,1.01929337E-001,1.10946614E-002,8.39847848E-002,2.47093942E-002,-4.82656285E-002,-3.38978358E-002,8.23562592E-002,9.43520106E-003,1.99154788E-003,-1.32039923E-003,-1.74975079E-002,5.25957383E-002,-9.51754451E-002,-3.40001434E-002,1.5815651E-002,5.44813403E-004,-7.75640979E-002,-1.0712678E-001]

As you can see, a vector of this model is much bigger than the input “Bob Dylan”!

In 26ai we have a new datatype VECTOR

alter table bands add (band_vector vector);
update bands
set BAND_VECTOR = vector_embedding(all_minilm_l12_v2 using BAND as data);

That’s the table with the 9000 bands. In the table bands_unknown (11’000 bands) I will add the most probable band and the vector_dist field.

DECLARE
  CURSOR c_bands_unknown IS SELECT alias FROM bands_unknown;
  v_band
  bands.band%TYPE;
  v_vector_dist NUMBER;
BEGIN
  FOR r IN c_bands_unknown LOOP
    SELECT band, vector_distance(b.band_vector
    , vector_embedding(all_minilm_l12_v2 USING r.alias AS data)) INTO v_band, v_vector_dist
    FROM vector.bands b ORDER BY 2 FETCH FIRST 1 ROW ONLY;
    UPDATE bands_unknown
      SET band = v_band, vector_dist = v_vector_dist WHERE alias = r.alias;
END LOOP;
END;
/

Afterwards, we can sort the result by vector distance. And the result?
Practically all results with a vector distance < 0.01 are correct, except e.g.:

  • DC/AC -> AC/DC
    • AC/DC is a well-known Australian hardrock band
    • DC/AC is a small swiss AC/DC cover band
  • Kids -> Children
    • Children is a hardcore-punk band from Berlin
    • Kids is a punkrock band from Zurich

Up to a vector distance of 0.07 the results are quite good, afterwards, the number of false mappings significantly increases.

For me, AI is a great tool for finding duplicates here. However, relying solely on AI isn’t the solution. Some human oversight is still needed. For example, the model isn’t yet able to recognize that, when working with band-names, linguistic synonyms shouldn’t be merged.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Martin Bracher

Martin Bracher

Martin Bracher has more than 25 years of experience managing Oracle infrastructures, specializing in high availability (Data Guard, Grid Infrastructure), migrations, and automation with Shell scripting, Ansible, and Saltstack. He leads the Grid Infrastructure Workshop and contributes to the development of the DMK Database Management Kit and the YaK Oracle component. Prior to dbi services, he was Principal Consultant at Trivadis AG (Accenture) and Consultant at Opitz Consulting and CSC Ploenzke. He holds a Dipl. Phil. Nat. in Informatics from the University of Bern. His industry experience covers banking, telecommunications, and public administration.View Author posts

18 posts since May 2025

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

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