/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 14
AI CHATBOT KNOWLEDGE RETRIEVAL USING VECTOR SEARCH
=================================================================
Chatbots like ChatGPT use Retrieval Augmented Generation (RAG).
Process:
1 User asks question
2 Question converted into embedding
3 Oracle searches similar documents
4 Documents sent to LLM
5 LLM generates response
Oracle acts as the knowledge retrieval layer.
===============================================================*/
/*===============================================================
SECTION 1
CHATBOT KNOWLEDGE TABLE
===============================================================*/
DROP TABLE chatbot_knowledge PURGE;
CREATE TABLE chatbot_knowledge
(
doc_id NUMBER PRIMARY KEY,
question VARCHAR2(500),
answer VARCHAR2(2000),
embedding VECTOR(6)
);
/*===============================================================
INSERT KNOWLEDGE
===============================================================*/
INSERT INTO chatbot_knowledge VALUES
(
1,
'What is Oracle Database?',
'Oracle Database is an enterprise relational database system.',
VECTOR '[0.11,0.15,0.18,0.21,0.24,0.28]'
);
INSERT INTO chatbot_knowledge VALUES
(
2,
'What is Machine Learning?',
'Machine learning allows computers to learn patterns from data.',
VECTOR '[0.90,0.82,0.77,0.70,0.66,0.60]'
);
INSERT INTO chatbot_knowledge VALUES
(
3,
'What is Artificial Intelligence?',
'Artificial intelligence enables machines to perform intelligent tasks.',
VECTOR '[0.92,0.85,0.78,0.72,0.69,0.64]'
);
COMMIT;
/*===============================================================
SECTION 2
CHATBOT QUERY SIMILARITY SEARCH
===============================================================*/
SELECT
question,
answer
FROM chatbot_knowledge
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.91,0.80,0.75,0.71,0.65,0.60]',
COSINE
)
FETCH FIRST 1 ROW ONLY;
/*===============================================================
This query retrieves the best matching answer.
===============================================================*/
/*===============================================================
END OF PART 14
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/