Oracle Database Chatbot - AI Python Solutions

Overview

The oracledb_chatbot.py script creates a chatbot that answers questions about a library database hosted on an Oracle Autonomous Database. It uses a locally installed LLaMA 3.2 model via Ollama to convert natural language queries into Oracle SQL queries, executes them, and displays results in a command-line chat interface. The database schema includes Authors, Books, and Borrowers tables.

Features

Queries an Oracle Autonomous Database using natural language.
Uses LLaMA 3.2 for SQL generation.
Checks if the Ollama server is running and provides setup instructions.
Handles invalid queries gracefully.
User-friendly command-line interface.

Requirements

Python 3.8 or higher
Libraries: oracledb, ollama
Oracle Instant Client (19c or higher)
Ollama (latest version, e.g., 0.3.12+)
LLaMA 3.2 model (installed via ollama pull llama3.2)
Oracle Autonomous Database instance

Installation

# Install Python 3.8+ (if not already installed)
# Linux
sudo apt-get install python3
# macOS
brew install python3

# Install required libraries
pip install oracledb ollama

# Install Oracle Instant Client
# Download from https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html
# Example for macOS
unzip instantclient-basic-macos.x64-19.8.0.0.0dbru.zip -d ~/instantclient_19_8
export ORACLE_HOME=~/instantclient_19_8
export PATH=$ORACLE_HOME:$PATH

# Install Ollama
# Linux/macOS
curl -fsSL https://ollama.com/install.sh | sh
# Windows: Download from https://ollama.com/download

# Pull LLaMA 3.2 model
ollama pull llama3.2

# Start Ollama server (run in a separate terminal)
ollama serve

Setup

Create an Oracle Autonomous Database in the Oracle Cloud Console. Download the wallet and unzip it to a directory. Configure the TNS_ADMIN environment variable and create the database schema using the provided SQL file.

# Set TNS_ADMIN
export TNS_ADMIN=/path/to/wallet_directory

# Connect to your Oracle database
sqlplus -s /@your_service_name <

            Usage
            
                Create an oracle_connection.txt file with your database credentials. Run the script to start the chatbot.
            
            # Example oracle_connection.txt
username="ADMIN"
password="your_password"
dsn="your_service_name"
wallet_path="/path/to/wallet_directory"
wallet_password="your_wallet_password"

# Run the script
python3 oracledb_chatbot.py
            
            
                Type your question in the chat interface, or 'exit' to quit. Example questions:
            
            
                List all books by George Orwell.
                Who borrowed books in May 2025?
                How many books are in the Fantasy genre?
            

            Sample Files
            
                Download the schema SQL file to set up the database:
            
            
                library_schema.sql: SQL script to create Authors, Books, and Borrowers tables with sample data.
            

            Download
            
                Get the script and start building your Oracle chatbot:
            
            Download oracledb_chatbot.py

            Example Output
            === Oracle Database Chatbot ===
Hello! I'm ready to answer questions about the library database.
Ask about books, authors, or borrowers. Type 'exit' to quit.
================================

You: List all books by George Orwell
Generated SQL: SELECT b.title
FROM Books b
JOIN Authors a ON b.author_id = a.author_id
WHERE a.last_name = 'Orwell'
Bot: Results:
['TITLE']
('1984',)
('Animal Farm',)

You: exit
Goodbye!