Oracle Database 23ai introduces a powerful integration of AI within enterprise databases. Unlike public Large Language Models such as ChatGPT, Cohere, Google Gemini, or Meta LLaMA, which are limited to their training data, Oracle 23ai enables the enhancement of LLMs with real-time organizational data, allowing users to ask questions and receive insights grounded in internal business information.
This release eliminates the need for a separate vector database by supporting native AI vector search within Oracle itself. Embeddings, which are multi-dimensional representations of text, images, videos, or documents, can be stored and queried directly. This approach avoids data fragmentation, simplifies architecture, and provides seamless access using standard SQL without the need for complex pipelines or transformations.
ONNX - Open Neural Network Exchange
Open Neural Network Exchange (ONNX) is an open standard designed to represent machine learning models. It enables the conversion of pre-trained transformer models such as those from Hugging Face into ONNX format, which can then be imported into Oracle 23ai. This allows for vector-based search capabilities directly within the database using native PL/SQL functions, eliminating the need for external LLM API calls over HTTPS and removing the complexity of managing API keys for model access.
With Oracle 23ai, a new VECTOR datatype allows for the creation of table columns specifically for storing embeddings whether from user input or retrieved document chunks through Retrieval-Augmented Generation (RAG). These embeddings can be stored in VECTOR columns to enable semantic searches across text or document content directly within the database.
Downloaidng ONNX Model and Loading in Database
Code: Select all
DECLARE
LOCATION_URI VARCHAR2(200) := 'https://objectstorage.ap-singapore-1.oraclecloud.com/p/jIePvxP3prg2wBcY5ZpuQ0zZkwgISxttbXGx0RA1ZxAOJmG7ylOlRYaEa-9Vek4D/n/axa0amw0dxog/b/JS_bucket-20240720-0855/o/'; -- File uploaded and shared by ERPstuff.com can be removed anytime without prior notice.
ONNX_MODEL_FILE VARCHAR2(100) := 'all_MiniLM_L12_v2.onnx';
ONNX_MODEL_NAME VARCHAR2(500) := 'ALL_MINILM_L12_V2';
BEGIN
DBMS_OUTPUT.PUT_LINE('ONNX model file name in Object Storage is: ' || ONNX_MODEL_FILE);
DBMS_OUTPUT.PUT_LINE('Model will be loaded and saved with name: ' || ONNX_MODEL_NAME);
-- Attempt to drop the model if it exists before loading
BEGIN
DBMS_DATA_MINING.DROP_MODEL(model_name => ONNX_MODEL_NAME);
DBMS_OUTPUT.PUT_LINE('Model droppted');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-----------------------------------------------------
-- Read the ONNX model file from Object Storage into the Autonomous Database data pump directory and replace if file already exists
-----------------------------------------------------
DBMS_CLOUD.GET_OBJECT(
directory_name => 'DATA_PUMP_DIR',
object_uri => LOCATION_URI || ONNX_MODEL_FILE);
-----------------------------------------
-- Load the ONNX model to the database
-----------------------------------------
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'DATA_PUMP_DIR',
file_name => ONNX_MODEL_FILE,
model_name => ONNX_MODEL_NAME);
DBMS_OUTPUT.PUT_LINE('New model successfully loaded with name: ' || ONNX_MODEL_NAME);
END;ALL_MINILM_L12_V2 ONNX EMBEDDING
SELECT VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Thanks to ERPstuff.com for such a nice code.' as DATA) AS embedding;
[-9.10044536E-002,-2.67778919E-003,5.17894179E-002,-4.70024683E-002,7.6275818E-002,-8.31164047E-002,-4.95431609E-002,2.3995975E-002,-6.08266145E-002,8.31800327E-002,1.56745072E-002,1.93556994E-002,5.71021847E-002,4.54464443E-002,-2.84771211E-002,6.2028423E-002,-5.77570451E-003,3.24352086E-002,4.50691506E-002,-6.19382039E-002,9.17982236E-002,-2.04077493E-002,3.02712433E-002,2.30532531E-002,2.39695869E-002,1.51124783E-002,-8.10240358E-002,-2.68804003E-003,-8.39882158E-003,-9.86560248E-003,3.22607942E-002,-2.03690939E-002,6.92864582E-002,5.66801056E-003,-4.60770279E-002,6.14631968E-003,-3.97096649E-002,7.2588902E-003,-3.26002389E-002,-5.32517955E-002,-1.38300704E-002,1.01067625E-001,-1.11658432E-001,6.51943013E-002,7.03549981E-002,7.33178062E-003,-8.9396406E-003,1.1956281E-002,-3.41375498E-003,-3.64477038E-002,1.92294437E-002,-1.84440203E-002,-7.86611438E-002,-3.28817815E-002,-5.13391979E-002,-6.00522757E-002,3.85557348E-003,-1.0312153E-002,-7.40277618E-002,2.90450696E-002,1.22106737E-002,-2.35588904E-002,-5.18267825E-002,4.34625298E-002,7.71245211E-002,1.29341977E-002,-2.942314E-002,3.05729229E-002,-9.24550742E-002,-6.47853315E-002,-1.41640022E-001,2.59255152E-003,-4.90079895E-002,6.77945912E-002,4.0796129E-003,3.3075653E-002,-9.84537695E-003,1.56520177E-002,-6.92633018E-002,-1.89616662E-002,-6.79093301E-002,-9.57086235E-002,-1.00883725E-003,5.98025955E-002,8.84013996E-002,2.98672039E-002,1.30695803E-002,-8.52552578E-002,1.07016787E-001,-1.16155138E-002,-1.81278698E-002,4.50051762E-002,3.58682089E-002,-2.32994892E-002,-3.61205712E-002,2.07261927E-002,-3.15952785E-002,4.48434206E-004,3.4486033E-002,1.54120505E-001,-8.30543041E-002,3.71225514E-002,7.01219589E-002,8.77647009E-003,-1.65821239E-002,-9.46628302E-002,6.99564144E-002,4.57173809E-002,3.76958214E-002,-1.28221989E-001,-7.05199689E-003,-5.51759312E-003,3.4089949E-002,6.10231534E-002,-6.71508117E-003,8.13934132E-002,8.12237151E-003,-2.33594608E-002,4.99002263E-002,1.17401304E-002,2.68279333E-002,3.42812538E-002,-5.2167058E-002,2.33765654E-002,6.86784238E-002,-9.17911679E-002,8.00241306E-002,-1.06932726E-002,3.68150286E-002,1.37887731E-001,4.15974706E-002,-5.50612435E-003,1.06188685E-001,1.55485142E-002,-3.87866683E-002,3.74200149E-003,-1.95060242E-002,8.9869678E-002,-3.72350328E-002,-2.37496309E-002,1.15535213E-002,5.03090248E-002,-8.50295573E-002,-9.36183259E-002,-4.3229077E-002,2.56159864E-002,6.30267039E-002,5.07819466E-003,2.64143711E-003,-2.7772123E-002,2.92501897E-002,-5.38668111E-002,9.83533636E-003,1.13707967E-001,9.80799831E-003,8.58501419E-002,1.70981824E-001,3.72141339E-002,4.09168191E-002,-5.77854775E-002,-2.29890067E-002,1.98935308E-002,-6.45144135E-002,-2.71293782E-002,7.78479734E-003,-6.30270913E-002,-7.38528743E-002,-4.23578219E-003,-2.66294405E-002,-3.93242761E-002,-3.87113281E-002,2.21198928E-002,9.98003874E-003,1.61890835E-002,-1.73464827E-002,6.05913773E-002,6.78166747E-002,6.35193987E-003,-6.1221458E-003,4.25139964E-002,-7.36764818E-003,-3.84787768E-002,-6.66346103E-002,1.8573042E-002,-3.76055017E-002,3.89982648E-002,-3.42059066E-003,8.9714393E-002,8.10559914E-002,-4.45996337E-002,-1.36285294E-002,-6.21804595E-002,7.39156231E-002,5.12055308E-002,5.8540456E-002,-3.50337438E-002,-2.08366867E-002,5.85134476E-002,8.42684507E-003,1.30549207E-001,2.28737518E-002,5.03935106E-002,3.45499031E-002,-1.74154717E-004,-1.87866818E-002,-1.18146166E-002,3.25396992E-002,-3.75701115E-002,4.53826942E-004,-1.72745325E-002,-3.53218205E-002,-3.24245282E-002,4.88725863E-003,-6.02328554E-002,3.23321819E-002,-4.18558493E-002,1.93886887E-002,-7.54050761E-002,5.6051001E-002,-7.38790333E-002,-5.9017376E-003,-6.36886805E-002,1.68569051E-002,8.89521995E-033,-1.10561652E-002,-7.24315643E-002,-1.78537127E-002,5.92459701E-002,5.23482682E-003,2.262105E-003,-5.31401187E-002,2.97869109E-002,4.75921705E-002,4.96222451E-002,8.92157201E-003,1.74374152E-002,-8.66525322E-002,-2.45007072E-002,-2.93564089E-002,2.51494646E-002,4.8306617E-003,-3.68980579E-002,-7.008343E-003,9.46700945E-003,5.44755831E-002,3.11230142E-002,4.747817E-004,-1.64011586E-002,-4.43975851E-002,3.01783755E-002,-4.53574806E-002,-3.60819697E-002,-4.95228283E-002,-4.21375483E-002,-4.73372191E-002,2.82848775E-002,-1.78676099E-001,3.6512617E-002,-1.67556331E-002,-8.18831995E-002,-1.25521142E-002,9.32840854E-002,4.64349203E-002,5.29365789E-004,6.02320349E-003,-5.29803894E-002,-3.9343629E-002,-8.66288319E-002,-7.52103254E-002,1.89226363E-002,-7.81483389E-003,1.63453892E-002,4.92810039E-004,1.74052529E-002,-8.40930343E-002,2.50832434E-003,7.74658024E-002,-3.73615101E-002,7.85167888E-002,7.6787591E-002,4.65724268E-004,-3.13163246E-003,2.19619423E-002,5.26575223E-002,-7.31483623E-002,-3.22772264E-002,-4.99532092E-003,2.81016473E-002,6.2155135E-002,2.6293885E-002,1.66268442E-002,3.12229767E-002,9.96051915E-003,8.73847678E-002,-1.20306648E-002,-3.02821603E-002,-5.88544868E-002,-1.63005609E-002,-4.50029559E-002,-1.83836315E-002,3.91022898E-002,2.55219582E-002,-8.06167945E-002,5.40305562E-002,1.57794282E-002,6.24048384E-003,4.31332216E-002,8.34493246E-003,3.96264382E-002,1.86254981E-003,2.9329157E-003,1.27873905E-002,-6.08143359E-002,2.2004107E-003,-2.02221014E-002,2.37012096E-002,7.43821859E-002,2.74268333E-002,-3.92474793E-003,5.77719033E-032,1.41519522E-002,-2.10681092E-002,-8.85311738E-002,-4.62087169E-002,2.14031134E-002,-3.22173387E-002,-5.63668758E-002,-8.14435855E-002,-1.36147127E-001,-8.92237127E-002,5.03409561E-003,-1.38293272E-002,-7.25341365E-002,2.3396261E-002,1.46178585E-002,-2.84038316E-002,-4.53023538E-002,3.73721085E-002,-7.16422275E-002,-5.40621504E-002,-8.81532505E-002,3.61735635E-002,2.81007448E-003,3.00033502E-002,1.53406076E-002,9.55336094E-002,4.74845851E-003,7.97788352E-002,7.64401257E-002,-6.21986613E-002,2.88688634E-002,1.93431657E-002,1.32946074E-002,-4.07983027E-002,3.19992565E-002,3.80200408E-002,-2.94915959E-003,-8.79625883E-003,-1.55317113E-002,-1.31500792E-002,-4.33472283E-002,-5.28505631E-002,4.67936397E-002,-2.57505812E-002,-6.66501969E-002,-2.00149859E-003,6.36886135E-002,-4.92427275E-002,-3.74290869E-002,-3.50835845E-002,-3.25358175E-002,-8.05914029E-002,5.04496209E-002,-4.46232334E-002,1.13091879E-002,-9.7881794E-002,-3.15648206E-002,-6.172226E-002,2.89271697E-002,5.97785972E-002,4.08440791E-002,6.5303348E-002,3.92012373E-002,5.84817976E-002]
Scripts
-- Oracle APEX Workspace schema
GRANT CONNECT TO wksp_jsss;
GRANT CONSOLE_DEVELOPER TO wksp_jsss;
GRANT DWROLE TO wksp_jsss;
GRANT OML_DEVELOPER TO wksp_jsss;
GRANT RESOURCE TO wksp_jsss;
Code: Select all
CREATE TABLE My_Table (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
str CLOB, -- Using CLOB for potentially large text inputs
embedding_vector VECTOR(384)
);
-- Example of inserting data with embeddings
INSERT INTO My_Table (str, embedding_vector)
VALUES (
'Your text to embed goes here',
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Your text to embed goes here' AS data)
);
INSERT INTO My_Table (str, embedding_vector)
VALUES (
'ERPstuff.com',
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'ERPstuff.com' AS data)
);
-- Example of querying with vector similarity search
SELECT
id,
str,
VECTOR_DISTANCE(embedding_vector,
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'ERPstuff.com' AS data)
) as distance
FROM My_Table
ORDER BY distance
FETCH FIRST 5 ROWS ONLY;"ID","STR","DISTANCE"
2,"ERPstuff.com",0.0000000000000002220446049250313
1,"Your text to embed goes here",0.8209019000290673