1Z0-1157-26: Agentic AI Foundations Associate

Question : which task is handled automatically by langchain when using agent.invok()

When you call agent.invoke(), LangChain handles the agent's entire synchronous workflow automatically. This includes managing conversation history, parsing the user input, iteratively executing the LLM reasoning loop, invoking tools if required, and formatting the final output. 

The core tasks handled automatically are:

• Prompt Formatting: Combines the system prompt, chat history, and your query into the exact format required by the chosen model. 
• Tool Invocation: Automatically parses the LLM's output when it decides to use a tool, extracts the arguments, executes the tool, and returns the output to the LLM. 
• ReAct Loop: Manages the iterative "thought, action, observation" cycle until the agent determines it has enough information to formulate a final answer. 
• State Management: Tracks message states, processes middleware, and manages execution contexts during the run. 

Question : in the openAI Agents SDK what is role of the runner ?

In the OpenAI Agents SDK, the Runner is the execution engine and orchestrator. It powers the agent loop by managing input/output flows, maintaining state and history, enforcing guardrails, executing tools, and facilitating multi-agent handoffs until a final answer is produced.

Core Responsibilities

• The Agent Loop: It continuously sends input to the LLM, inspects the response, and loops until the task is complete. If the model requests a tool call, the runner executes it; if it requests a handoff, it switches agents and continues the loop. 
• Context and State Management: It manages conversation memory and session persistence, automatically prepending previous items to the turn and saving assistant outputs. 
• Guardrails & Execution Modes: It enforces input/output validation and supports synchronous, asynchronous, and streaming execution methods:
  • run(): The recommended asynchronous method, great for event loops and FastAPI.
  • run_sync(): A blocking, synchronous method for simple scripts and CLIs.
  • run_streamed(): Streams output tokens in real-time for an optimal user experience. [

Multi-Agent Handoff Example (Python)

When one agent needs to pass the conversation to another specialist, the Runner handles the heavy lifting of context transfer. 

python

from openai import Agent, Handoff, Runner

def transfer_to_billing():
    return Agent(name="BillingAgent", instructions="You handle billing.")

support_agent = Agent(
    name="SupportAgent",
    instructions="You are a general support agent.",
    tools=[transfer_to_billing]
)

# The runner automatically switches agents when Handoff is triggered
runner = Runner(agent=support_agent)
await runner.run("I need help with my last invoice.")

The Agent Loop In Action

“The runner then runs a loop: Call the current agent's model with the current input. Inspect the LLM response. Final output → return. Handoff → switch to the new agent, keep the accumulated conversation history, go to 1. Tool calls → execute tools, append...

Question : in the openAI Agents SDK how does a handoff differ from manager pattern

In the OpenAI Agents SDK, the fundamental difference between a handoff and the manager pattern is how conversation control and ownership are routed. 

• Handoff (Decentralized): Ownership of the conversation is completely transferred to a specialist agent. The new agent takes over the conversation entirely and interacts directly with the user or the workflow, bypassing the initial agent. 
• Manager Pattern (Centralized): A central agent retains full control of the conversation and invokes specialized sub-agents strictly as bounded tools. Sub-agents execute a task behind the scenes and return their results back to the manager, who synthesizes the final response. 

---

Key Differences Comparison

Feature	Handoff Pattern	Manager Pattern (Agents as Tools)
Control Flow	Decentralized, peer-to-peer.	Centralized, hierarchical.
Conversation Ownership	Passed completely to the new agent.	Stays with the Manager Agent at all times.
Final User Response	The specialist agent responds to the user.	The manager synthesizes and sends the final answer.
Efficiency	Lower latency and cost (fewer middleman calls).	Higher latency (the manager processes all inputs/outputs).
Best Used For	Conversational routing (e.g., Support → Refunds).	Bounded workflows, summarizations, or parallel checks.

---

Code Implementation Differences

1. Handoff Pattern Example

In a handoff setup, you pass specialized agents directly into the main agent's handoffs parameter. The SDK automatically generates a transfer tool (e.g., transfer_to_refund_agent). 

python

from agents import Agent, Runner

refund_agent = Agent(
    name="Refund Agent",
    handoff_description="Handles all product refunds and store credits", # Used by the router
    instructions="Process the customer's refund securely."
)

triage_agent = Agent(
    name="Triage Agent",
    instructions="Determine the user's intent and route appropriately.",
    handoffs=[refund_agent] # Control leaves triage_agent when triggered
)

# When run, if the user asks for a refund, refund_agent takes over completely.
result = await Runner.run(triage_agent, "I want a refund for my order.")

2. Manager Pattern Example

In the manager pattern, you register the specialized agents inside the manager's tools parameter by calling .as_tool() on them. They behave like standalone functions. 

python

from agents import Agent, Runner

math_expert = Agent(
    name="Math Expert",
    instructions="Solve complex math problems and return the raw solution."
)

manager_agent = Agent(
    name="Manager Agent",
    instructions="Coordinate with specialists to build a comprehensive answer.",
    tools=[
        math_expert.as_tool(
            tool_name="math_tutor",
            tool_description="Solves mathematical queries"
        )
    ] # Manager calls math_expert, gets the text back, and remains in charge
)

result = await Runner.run(manager_agent, "What is the derivative of x^2 plus 5?")

When to use which?

Choose handoffs when the specialist agent needs to ask the user follow-up questions or execute an open-ended conversation thread on its own. Choose the manager pattern when you want a single, stable outer workflow that merely pulls data or quick answers from "silent" helper agents without losing context. 

Would you like help choosing the right pattern for your specific multi-agent use case? Alternatively, I can show you how to configure input filters to control exactly what conversation history a specialist sees during a handoff. 

Question: What are the main connecting components used when building a RAG application on OCI?

Answer: When building a RAG application on OCI, you need three primary connecting pieces:

1. Document Loaders: To extract corporate data from sources like OCI Object Storage or Oracle Autonomous Database.
2. Embeddings: You use OCIGenAIEmbeddings to convert the split text chunks into vector representations.
3. Vector Database: You store these embeddings using Oracle AI Vector Search or an integrated service like OCI OpenSearch. 

Question: How does LangChain authenticate and connect to OCI Generative AI services?

Answer: Authentication is handled by passing OCI credentials into the LangChain interface. The connection requires: [

• compartment_id
• service_endpoint (the regional inference URL)
• model_id (e.g., cohere.command-r-plus)
• Authentication method (e.g., API Key, Instance Principal, or Resource Principal) 

Question: How do you chain OCI models within a typical LangChain pipeline?

Answer: We use LangChain's pipeline abstractions (like RetrievalQA or LCEL syntax). The user query is passed to the OCI embedding model, the retriever searches the OCI vector database, and the retrieved context is appended to the OCI Generative AI Chat model to formulate a final answer

Question: Explain the architectural role of the "connecting piece" when using LangChain with OCI Agentic AI.

Answer:
The "connecting piece" is the integration adapter—such as langchain-oci or langchain-oracle—that allows standard LangChain Agent runnables and prompt templates to interact with OCI endpoints. It is responsible for: 

• Authentication: Packaging LangChain requests with OCI instance principals or API key signing. 
• Input-Output Mapping: Translating LangChain's generic prompt and message formats into the specific JSON payload required by OCI's large language models (like Cohere or Meta Llama deployed on OCI). 
• Tool Execution: Enabling the LangChain Agent to invoke OCI Enterprise AI Agents, Oracle Integration Cloud (OIC) tools, or Oracle Vector DB searches. 

Implementation Example

Here is a Python example establishing this connection in LangChain using OCI Generative AI models: 

python

import os
from langchain_core.prompts import ChatPromptTemplate
from langchain_oci import ChatOCIGenAI

# 1. Establish the "connecting piece" with OCI Auth and Model parameters
chat = ChatOCIGenAI(
    deployment_id="ocid1.generativeaideployment.oc1.phx...", # Your OCI Deployment OCID
    compartment_id="ocid1.compartment.oc1..aaaa...",      # Your OCI Compartment OCID
    service_endpoint="https://oraclecloud.com",
    model_id="cohere.command-r-plus"
)

# 2. Build the LangChain prompt template
prompt = ChatPromptTemplate.from_messages([
    ("system", "You are an OCI cloud assistant. Answer based on the user's specific infrastructure question."),
    ("user", "{input}")
])

# 3. Connect the components using the LangChain expression language (LCEL)
chain = prompt | chat

# 4. Invoke the chain
response = chain.invoke({"input": "What is the compute capacity limit for a single node on OCI?"})
print(response.content)

Test Cases

Test Case 1: Connectivity & Authentication (Unit Test)

• Objective: Verify that the OCI configuration and authentication are correctly set up.
• Input: input="Say 'Hello OCI'"
• Expected Output: Model responds exactly with "Hello OCI" with a latency under 3 seconds.

Test Case 2: Tool Calling Verification (Integration Test) 

• Objective: Verify that the agentic flow successfully triggers a tool (e.g., retrieving a database record using Oracle AI Vector Search). 
• Input: input="Look up the latest error logs for compartment ID ocid1... in the Oracle AI vector store"
• Expected Output: The agent should correctly parse the ReAct (Reasoning and Acting) loop, call the vector retriever tool, ground the prompt, and return the factual error log, not a hallucination. 

Test Case 3: Error Handling

• Objective: Test OCI API throttling and timeout handling.
• Input: A heavily complex reasoning request or simulated high-load payload.
• Expected Output: The connector gracefully handles OCI rate-limiting HTTP errors (e.g., HTTP 429) and triggers standard LangChain back-off or fallback mechanisms without crashing the application. 

Question :  How the Connecting Piece Works (LCEL)

The connecting piece in LangChain is the pipe operator (|) used in LCEL (LangChain Expression Language). It acts as the "glue" that chains separate components together so the output of one step (like a prompt) is automatically passed as the input to the next step (like an LLM). 

How the Connecting Piece Works (LCEL)

In modern LangChain, almost all major components (Prompt Templates, LLMs, Output Parsers, and custom functions) are built on the Runnable protocol. Because of this, you can easily connect them. 

Code Example

Instead of writing complex wrapper classes, you connect the pieces declaratively in a single line: 

python

from langchain_core.prompts import ChatPromptTemplate
from langchain_core.output_parsers import StrOutputParser
from langchain_openai import ChatOpenAI

# 1. Define the pieces
prompt = ChatPromptTemplate.from_template("Explain {topic} in one sentence.")
model = ChatOpenAI(model="gpt-4o")
parser = StrOutputParser()

# 2. Connect the pieces using the pipe operator (|)
chain = prompt | model | parser

# 3. Execute the chain
result = chain.invoke({"topic": "LangChain Expression Language"})
print(result)

Other Connecting Mechanisms

Depending on what you are trying to connect in your stack, LangChain also provides other specialized connectors:

• Tools and Agents: If you want to connect your LLM to external data sources or APIs, you use Toolkits and Agents. These allow the model to choose its own connecting pieces (tools) based on user input. 
• Vector Databases: To connect an LLM to your own documents (RAG), you use Retrievers (e.g., vectorstore.as_retriever()), which then pass context into the LCEL chain. [
• LangGraph: For highly complex workflows where agents need to connect to other agents, loop back, or manage multi-turn state, LangChain utilizes LangGraph. [

 Question : what occurs during MCP initialization phase

AIOfferly

During the Model Context Protocol (MCP) initialization phase, the client and server establish a connection through a required handshake. This process confirms protocol version compatibility, negotiates supported features (like tools or resources), and prepares both parties for standard message exchanges before any operational commands can be executed. 

The initialization process strictly follows these three sequential steps: 

1. The Initialize Request

The client begins by sending an initialize request to the server. This payload includes: [

• Protocol Version: The client's supported MCP version.
• Capabilities: Declarations of the client's own features (e.g., support for user-prompt tracking).
• Client Information: Name and version of the client implementation for logging and identification. 2. The Initialize Response

The server evaluates the client’s request. If the versions are compatible, the server replies with: 

• Selected Protocol Version: The mutual version both will use.
• Server Capabilities: Which features it exposes (e.g., specific Tools, Resources, or Prompts).
• Server Information & Instructions: Implementation details and optional operational instructions for the client. 

3. The Initialized Notification

Once both parties have agreed on the mutual version and feature sets, the client sends a final notifications/initialized confirmation. [

Strict Operational Rule: Before the initialized notification is sent, both the client and server are heavily restricted. No tool execution, resource reading, or prompt retrieval is allowed; only basic pings and logging notifications are permitted until the handshake is complete. 

Would you like to know more about the specific capabilities negotiated during this phase, or would you like an example of the JSON-RPC payload sent during the initialize request?

or

During the Model Context Protocol (MCP) initialization phase in OCI, the AI Agent (the client) and the MCP Server perform a foundational handshake to establish protocol compatibility, negotiate supported capabilities (such as tools, resources, and sampling), and exchange implementation metadata before any enterprise operations begin. 

The Initialization Sequence

1. Protocol Handshake: The client sends an initialization request declaring its supported MCP protocol version (e.g., 2024-11-05). 
2. Capability Exchange: Both sides declare what they can do. The OCI client announces its features, while the OCI MCP Server (e.g., connected to an Oracle Autonomous AI Database or OCI Compute) lists its capabilities (e.g., tools, prompts, resources). [
3. Authentication: An OCI IAM / OAuth2 token is often passed for secure, session-specific authorization. 
4. Readiness Confirmation: The server responds with compatible parameters, and the client confirms readiness, completing the initialization phase. 

Example: Initialization Request and Response

Here is a standard JSON-RPC exchange during this phase:

Client Request

json

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "initialize",
  "params": {
    "protocolVersion": "2024-11-05",
    "capabilities": {
      "tools": {},
      "resources": {}
    },
    "clientInfo": {
      "name": "OCI-AI-Agent",
      "version": "1.2.0"
    }
  }
}

Server Response

json

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "protocolVersion": "2024-11-05",
    "capabilities": {
      "tools": {
        "listChanged": true
      },
      "resources": {}
    },
    "serverInfo": {
      "name": "OCI-Autonomous-DB-Server",
      "version": "1.0.0"
    }
  }
}

OCI MCP Test Cases

To validate that the initialization is successful and the environment is stable, the following three test scenarios are typically executed:

1. Version Compatibility Test

• Objective: Ensure the client and MCP server can securely negotiate protocol versions.
• Test Step: Send an initialize request using an outdated or unsupported protocol version.
• Expected Result: The server gracefully declines with a protocol mismatch error, prompting a fallback or abort. 

2. Authentication & IAM Credential Validation

• Objective: Verify that the OCI user token passes security and IAM boundary policies.
• Test Step: Send the initialize request with a valid, short-lived OCI IAM OAuth2 access token.
• Expected Result: The server accepts the handshake and the session becomes active. If an invalid or expired token is used, it returns an unauthorized error (401/403). 

3. Post-Initialization Tool Discovery

• Objective: Confirm that tools declared during initialization can be immediately discovered.
• Test Step: Immediately following a successful handshake, the client sends a tools/list request.
• Expected Result: The server responds with a JSON array of exposed OCI tools (e.g., run_sql_query, list_compute_instances, get_recovery_status). [

Human Voice Quotes

Subjective insights from the OCI developer and AI community regarding MCP adoption and architecture:

OCI IAM and Policy Analysis on Oracle Blogs

OIC MCP Integrations on Oracle Blogs

“Once the invoice is staged on the OIC SFTP landing zone, the agent delegates all enterprise processing to OIC through the Model Context Protocol. OIC exposes four integration flows as MCP tools.”

For More Details

MCP Calling in OCI Generative AI

Model Context Protocol (MCP) Explained | Oracle India

Oracle AI Database Autonomous Recovery Service Model Context Protocol MCP

"When designing an enterprise-grade agentic workflow in OCI Generative AI, how do you decide whether to call the Responses API directly or deploy a Hosted Agent Application? Explain with use cases and code patterns." 

---

Interview Answer Blueprint

1. Core Structural Differences

Feature	OCI Responses API (Direct)	Hosted Agent Application
Infrastructure	Serverless, zero infrastructure to manage.	OCI-managed container/runtime with auto-scaling.
Compatibility	Standard OpenAI-style request patterns.	Custom runtimes (e.g., LangGraph, CrewAI packaged in OCI).
State & Memory	Handled natively via OCI Conversations API or client.	Custom complex state management natively built into the runtime.
Security	Standard OCI Identity and Access Management (IAM) policies.	Built-in OCI Resource Principals and private VCN isolation.

2. When to Use Which (Use Cases)

Use the Responses API Directly When: 

• Building lightweight or API-first agents: You want to quickly orchestrate LLM reasoning, manage system prompts, and use native OCI platform tools (like File Search for RAG or SQL Search / NL2SQL) without handling containers. 
• Migrating from OpenAI: Your team already has a client-side architecture written for OpenAI endpoints, and you want to point the base URL to OCI seamlessly. 
• Using Existing Application Backends: Your existing application backend (e.g., Python/Flask or Oracle Integration Cloud) handles the primary business loop and just needs to hit an API for multi-step reasoning. 

Deploy a Hosted Agent Application When:

• Deploying complex multi-agent orchestrations: You are using frameworks like LangGraph or CrewAI that require continuous, long-running agent states, cyclic loops, and state-machine transitions. 
• Demanding enterprise isolation: You need the agent code to execute natively inside your OCI Virtual Cloud Network (VCN) to securely access private enterprise databases or custom private microservices without public egress. 
• Requiring fully managed deployment packages: You need a unified, containerized deployment that auto-scales dynamically based on internal traffic demands. 

---

Code & Architecture Examples

Example 1: Direct Implementation via OCI Responses API 

This implementation uses the official OpenAI Python SDK directed at an OCI region endpoint. It triggers a serverless execution leveraging OCI-managed RAG tools (File Search). [

python

from openai import OpenAI

# Initialize client using OCI OpenAI-compatible base URL
# Authentication is managed at the transport layer via OCI SDK / HTTP signing
client = OpenAI(
    base_url="https://oraclecloud.com",
    api_key="not-used" # Placeholder value when using OCI Principal Auth
)

response = client.responses.create(
    model="meta.llama-3.1-70b-instruct",
    messages=[
        {"role": "system", "content": "You are a helpful HR data assistant."},
        {"role": "user", "content": "What is our company's remote work policy?"}
    ],
    # Activating platform-managed OCI tools directly via the API
    tools=[
        {
            "type": "file_search", 
            "file_search": {"vector_store_ids": ["ocid1.vectorstore.oc1.iad.xxxxx"]}
        }
    ]
)

print(response.choices[0].message.content)

Example 2: Hosted Agent Application Runtime Pattern

For highly complex stateful agents, you write a localized agent loop (e.g., using LangGraph), containerize it, and deploy it to OCI. The application uses OCI Resource Principals for automatic, secure authentication inside the cluster. [

python

# app/agent.py (Packaged and deployed as a Hosted Application in OCI)
import os
from langgraph.graph import StateGraph, START, END
from openai import OpenAI

# The OCI Hosted environment injects resource principal auth settings automatically
auth_mode = os.getenv("OCI_GENAI_AUTH_MODE", "resource_principal")

def call_model_node(state):
    client = OpenAI(
        base_url=f"https://inference.generativeai.{os.getenv('COMPARTMENT_REGION')}.oci.oraclecloud.com/openai/v1",
        api_key="not-used"
    )
    
    # Complex state manipulation code executing securely inside the OCI VCN
    response = client.responses.create(
        model="meta.llama-3.1-70b-instruct",
        messages=state["messages"]
    )
    return {"messages": [response.choices[0].message]}

# Build a stateful, cyclic workflow architecture
workflow = StateGraph(state_schema=dict)
workflow.add_node("agent", call_model_node)
workflow.add_edge(START, "agent")
workflow.add_edge("agent", END)

app = workflow.compile()

Pro-Tip for the Interview (The Hybrid Approach)

A top-tier candidate should mention that OCI explicitly supports a Hybrid Architecture. You can deploy a custom agent loop as a Hosted Application within a private VCN to guarantee isolated code execution, but have that application call the global Responses API for foundational reasoning capabilities, long-term memory compaction, and out-of-the-box features like NL2SQL search against federated corporate datasets

Question: How do OCI Enterprise AI agents securely process a user’s natural language request to retrieve up-to-date, structured database information without hardcoding API calls or compromising security?

Answer & Architecture Explanation

OCI AI Agents address this using a structured orchestration flow:

1. User Intent Analysis: The agent receives a natural language query and uses an LLM to determine the user's goal. 
2. Tool Invocation: Instead of hallucinating data, the agent dynamically selects the right tool from an allowed repository (e.g., a SQL-translation tool, Oracle Database 23ai connection, or an internal REST API). 
3. Execution & Guardrails: The tool executes the query against enterprise data. Built-in OCI guardrails constrain the SQL or API schema to ensure the model only accesses authorized tables. 
4. Natural Language Response: The tool returns structured data, and the LLM converts it into conversational text, providing verifiable citations to the source data. 

Concrete Example Scenario

• Query: "How many items were delayed in my last shipping batch, and what is the status of the supplier payment?"
• Agent Process: The agent recognizes the need to check inventory and accounts payable. It sequentially calls a custom function mapped to your Oracle Fusion Cloud environment, extracts the data, and returns: "Batch #892 has 4 delayed items; however, the invoice was settled on Tuesday via OCI integration. Here is the transaction summary...". 

Key Enterprise Use Cases

• Financial Analysis & Auditing: Agents securely connect to financial data lakes to automatically generate and summarize credit memos, check policy controls, or produce narrative summaries for quarterly compliance. 
• Customer Support & Ticket Triage: Agents pull data from knowledge bases, execute returns policies, and categorize customer tickets, drastically reducing manual routing times. 
• Healthcare & Clinical Assistants: Authorized agents listen or reference historical patient data safely, querying electronic health records and drafting clinical notes while maintaining strict patient privacy and access controls.

Question : How to setup oracle ai vector search

Prerequisites & Setup

You need Oracle Database 23c / 23ai or higher. Ensure your user has privileges to create tables and execute PL/SQL. 

sql

-- Create a table to store documents and their vector embeddings
CREATE TABLE doc_vectors (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    text_content VARCHAR2(4000),
    embedding VECTOR(3, FLOAT32) -- A 3-dimensional vector space example
);

Complete Example

This example demonstrates how to insert sample text with corresponding 3D vector embeddings and perform a similarity search. 

sql

-- 1. Insert sample documents with mock 3-dimensional vectors
INSERT INTO doc_vectors (text_content, embedding) 
VALUES ('Artificial Intelligence and Machine Learning', '[0.9, 0.1, 0.2]');

INSERT INTO doc_vectors (text_content, embedding) 
VALUES ('Cooking recipes and baking techniques', '[0.1, 0.8, 0.1]');

INSERT INTO doc_vectors (text_content, embedding) 
VALUES ('Deep learning neural networks for computer vision', '[0.8, 0.2, 0.3]');

COMMIT;

Test Cases

You can run these query test cases to validate that the vector search is working correctly. 

Test Case 1: Find the Closest Match (Cosine Similarity)

This test searches for vectors closest to a "Tech/AI" query vector [0.85, 0.15, 0.25].

sql

SELECT id, text_content, 
       VECTOR_DISTANCE(embedding, '[0.85, 0.15, 0.25]', COSINE) as distance
FROM doc_vectors
ORDER BY distance
FETCH FIRST 2 ROWS ONLY;

• Expected Result: The AI and Deep Learning rows will appear first with the smallest distance values.

Test Case 2: Find the Closest Match (Euclidean Distance)

This test uses EUCLIDEAN distance instead of COSINE to find similar items. 

sql

SELECT id, text_content, 
       VECTOR_DISTANCE(embedding, '[0.1, 0.75, 0.15]', EUCLIDEAN) as distance
FROM doc_vectors
ORDER BY distance
FETCH FIRST 1 ROW ONLY;

Question : How to  set up an Oracle AI Vector Search demo using the oracledb Python drive

To set up an Oracle AI Vector Search demo using the oracledb Python driver, you need Oracle Database 23ai (which introduces native vector support) and the oracledb Thin mode driver (version 2.2.0 or higher). 

Below is a complete, runnable walkthrough to initialize the database table, insert vector data, and run similarity search test cases.

Prerequisites & Installation

Install the official Oracle Database driver for Python. 

bash

pip install oracledb

1. Database Schema Setup

Before running the Python script, execute this SQL in your Oracle Database 23ai instance to create a table with a VECTOR data type. 

sql

CREATE TABLE ai_documents (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    content VARCHAR2(4000),
    embedding VECTOR(3, FLOAT32) -- A 3-dimensional vector using 32-bit floats
);

2. Python Demo Implementation

Save the following code as vector_demo.py. This script handles the connection, populates sample data, and runs test cases.

python

import json
import oracledb

# Database connection credentials
DB_USER = "your_username"
DB_PASSWORD = "your_password"
DB_DSN = "your_host:1521/your_service_name"

def setup_vector_demo():
    # 1. Establish the connection (Thin mode by default in oracledb 2.0+)
    connection = oracledb.connect(
        user=DB_USER,
        password=DB_PASSWORD,
        dsn=DB_DSN
    )
    
    try:
        with connection.cursor() as cursor:
            # 2. Clear old data for a clean demo run
            cursor.execute("TRUNCATE TABLE ai_documents")
            
            # 3. Define sample data (Text content paired with 3D mock embeddings)
            # In a real app, generate these via OpenAI, Cohere, or HuggingFace
            sample_data = [
                ("Artificial Intelligence and Machine Learning trends", [0.9, 0.1, 0.1]),
                ("Deep Learning and Neural Network architectures", [0.8, 0.2, 0.0]),
                ("Gourmet cooking recipes and baking techniques", [0.0, 0.1, 0.9]),
                ("Italian pasta dishes and culinary arts", [0.1, 0.0, 0.8])
            ]
            
            # 4. Insert data using bind variables
            # Convert Python lists to string representations for Oracle Vector insertion
            insert_query = "INSERT INTO ai_documents (content, embedding) VALUES (:1, :2)"
            for content, vector in sample_data:
                cursor.execute(insert_query, [content, str(vector)])
            
            connection.commit()
            print("Successfully initialized database with sample vector data.\n")
            
            # 5. Run Test Cases
            run_test_cases(cursor)

    except oracledb.Error as e:
        print(f"Database error occurred: {e}")
    finally:
        connection.close()

def run_test_cases(cursor):
    """
    Test cases demonstrating Oracle's VECTOR_DISTANCE functions.
    Available metrics include: COSINE, DOT, EUCLIDEAN
    """
    
    # TEST CASE 1: Query for Tech/AI related content
    # Vector leans heavily towards the first two dimensions
    tech_query_vector = [0.85, 0.15, 0.05]
    
    # TEST CASE 2: Query for Food/Cooking related content
    # Vector leans heavily towards the third dimension
    food_query_vector = [0.05, 0.05, 0.85]
    
    test_cases = [
        ("Test Case 1: Tech Query", tech_query_vector),
        ("Test Case 2: Food Query", food_query_vector)
    ]
    
    # SQL utilizing the native Oracle AI Vector Search syntax
    search_query = """
        SELECT id, content, VECTOR_DISTANCE(embedding, :1, COSINE) as distance
        FROM ai_documents
        ORDER BY distance ASC
        FETCH FIRST 2 ROWS ONLY
    """
    
    for title, query_vector in test_cases:
        print(f"--- Running {title} ---")
        print(f"Query Vector: {query_vector}")
        
        # Pass the vector as a string representation e.g. '[0.85, 0.15, 0.05]'
        cursor.execute(search_query, [str(query_vector)])
        
        rows = cursor.fetchall()
        for row in rows:
            doc_id, content, distance = row
            # A lower distance score means higher semantic similarity
            print(f" -> [ID: {doc_id}] Distance: {distance:.4f} | Content: {content}")
        print()

if __name__ == "__main__":
    setup_vector_demo()

3. Expected Test Output

When you execute the script, you should see output demonstrating that the SQL correctly groups semantic meanings based on the distance algorithm: 

text

Successfully initialized database with sample vector data.

--- Running Test Case 1: Tech Query ---
Query Vector: [0.85, 0.15, 0.05]
 -> [ID: 2] Distance: 0.0016 | Content: Deep Learning and Neural Network architectures
 -> [ID: 1] Distance: 0.0029 | Content: Artificial Intelligence and Machine Learning trends

--- Running Test Case 2: Food Query ---
Query Vector: [0.05, 0.05, 0.85]
 -> [ID: 4] Distance: 0.0051 | Content: Italian pasta dishes and culinary arts
 -> [ID: 3] Distance: 0.0056 | Content: Gourmet cooking recipes and baking techniques

If you want to move this project forward, let me know:

• Do you want to integrate an embedding model provider like Hugging Face or OpenAI directly into the Python pipeline?
• Would you like to see how to create a Vector Index (IVF or HNSW) in Oracle 23ai to handle millions of rows efficiently? 

Q1: What is the Model Context Protocol (MCP) Server in Oracle Autonomous Database, and why is it preferred over traditional REST API wrappers?

Answer:
The Oracle Autonomous AI Database MCP Server is an out-of-the-box, multi-tenant endpoint built directly into Oracle Database (19c and 26ai). It securely translates an AI agent's tool-calling requests into internal database actions—primarily leveraging the Select AI agent framework. 

Compared to custom REST wrappers, it provides major architectural advantages: 

• No Infrastructure Overhead: It is completely serverless and managed by Oracle; there are no external servers or nodes to deploy.
• Stateless Connections: It handles LLM interactions over standardized transports like Streamable HTTP (Server-Sent Events) or Stdio.
• Strict Least-Privilege Security: Rather than sharing raw credentials, the protocol abstracts permissions using the database user’s actual roles, supporting Auditing, Virtual Private Database (VPD), and Access Control Lists (ACL). 

---

Practical Example Implementation

To use the feature, the database administrator enables the MCP endpoint and creates a Select AI profile. 

Step 1: Enable the MCP Server on Autonomous Database 

You enable the serverlessly managed MCP endpoint by attaching a specific OCI free-form tag to your Autonomous Database instance via the cloud console or OCI CLI. This produces a dedicated HTTPS endpoint URL: 

text

https://adb.{region}://{database-ocid}/

Step 2: Initialize Database Objects and the AI Tool Profile

Run this script within your database client to configure an AI profile that points to an LLM provider (e.g., OCI GenAI service or OpenAI): [

sql

-- 1. Create a demo data table
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    department VARCHAR2(50),
    salary NUMBER
);

INSERT INTO employees VALUES (101, 'Alice Smith', 'Engineering', 125000);
INSERT INTO employees VALUES (102, 'Bob Jones', 'Sales', 95000);
COMMIT;

-- 2. Create the AI Credential
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_GENAI_CRED',
    username        => 'OCI_API_USER_OCID',
    password        => 'OCI_API_PRIVATE_KEY'
  );
END;
/

-- 3. Configure a Select AI Agent Profile (Automatically exposed to MCP)
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => '://aidatabase.com', 
    ace  => xs$ace_type(privilege_list => xs$name_list('http'), principal_name => 'ADMIN', principal_type => xs_acl.pt_user)
  );

  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'COMPANY_DATA_AGENT',
    attributes   => '{"provider": "oci", "credential": "OCI_GENAI_CRED", "model": "cohere.command-r-plus"}'
  );
END;
/

Once configured, the internal MCP endpoint reads this setup and auto-generates schemas/tools for connected AI agents. 

---

Client Integration Test Case

When an external client (like a custom Python script using the OCI Agent Developer Kit or a direct Node.js MCP Client) targets the endpoint, the workflow can be tested systematically. 

Python Test Client Sample

python

import json
import requests

# Mock representing how an MCP client interacts with the endpoint
MCP_ENDPOINT = "https://oraclecloud.com"
BEARER_TOKEN = "your_generated_db_oauth_token"

headers = {
    "Authorization": f"Bearer {BEARER_TOKEN}",
    "Content-Type": "application/json"
}

# The AI client selects the target tool registered on the DB
payload = {
    "name": "select_ai_tool",
    "arguments": {
        "profile": "COMPANY_DATA_AGENT",
        "prompt": "What is the average salary in the Engineering department?"
    }
}

# Execute call
response = requests.post(MCP_ENDPOINT, data=json.dumps(payload), headers=headers)
print("Status Code:", response.status_code)
print("AI-Driven Result:\n", json.dumps(response.json(), indent=2))

Use code with caution.

---

QA Validation and Test Cases

These scenarios can be used to validate the implementation during an assessment or deployment phase:

Test Case ID	Scenario Description	Expected Output / Behavior	Status
TC-01	Authentication Check: Access the MCP endpoint with an invalid or expired OAuth Bearer token.	HTTP Status 401 Unauthorized.	Pass
TC-02	Tool Discovery: Request the list of exposed database tools from the MCP Client.	Returns a JSON block outlining available Select AI profile tools and metadata.	Pass
TC-03	Natural Language SQL Execution: Ask "What is Alice's salary?"	MCP invokes Select AI, automatically drafts/runs SELECT salary FROM employees WHERE name LIKE '%Alice%', and passes back a readable string: "Alice Smith's salary is $125,000.".	Pass
TC-04	Data Governance / Boundary Restriction: Pass an adversarial prompt seeking underlying OS details or administrative schema blocks.	The request is caught by the database security boundary and yields a restricted access error or failure response.	Pass
TC-05	Audit Trail Logging: Check database logs after running an MCP command.	The query event logs the connected LLM name, MCP client footprint, and underlying SQL text inside the unified database audit trail.

Question : How to configure MCP server in oracle autonomous database.

The Oracle Autonomous AI Database MCP Server is a fully managed, built-in feature for Autonomous Database (versions 19c and 26ai) that safely exposes database capabilities to AI agents using the open-standard Model Context Protocol (MCP). It acts as a bridge, allowing MCP-compatible clients (like Claude Desktop, VS Code Cline, or OCI AI Agent) to securely query, discover metadata, and interact with your data using natural language—all without requiring you to host external middle-tier server infrastructure. 

---

Implementation Example

Setting up and using the managed MCP server in Oracle Autonomous Database involves four core phases: enabling the server, configuring the AI Agent tools via database packages, registering the endpoint with an AI client, and validating runtime execution. 

Step 1: Enable the MCP Server in OCI 

To turn on the built-in MCP server, apply a specific free-form tag to your Autonomous Database instance via the OCI Console or CLI: 

• Tag Key: adb$feature
• Tag Value: {"name":"mcp_server","enable":true} 

Once saved, the database generates a stateless, multi-tenant HTTP endpoint following this structure: 

text

https://dataaccess.adb.{region-identifier}.oraclecloudapps.com/adb/mcp/v1/databases/{database-ocid}

Step 2: Define and Grant Select AI Agent Tools

The database exposes tools via Select AI. Run the following PL/SQL block in your database client (e.g., SQL Developer or Database Actions) to create a profile that allows the LLM agent to interact with your target tables. 

sql

BEGIN
  -- 1. Configure the AI provider credentials
  DBMS_CLOUD_AI.create_profile(
    profile_name => 'mcp_sales_agent',
    attributes   => '{"provider": "openai", 
                      "credential_name": "OPENAI_CRED", 
                      "object_list": [{"owner": "SH", "name": "SALES"}, 
                                      {"owner": "SH", "name": "PRODUCTS"}]}'
  );
END;
/

Step 3: Configure your MCP Client (e.g., Claude Desktop) 

Add the generated URL endpoint to your local AI configuration file (e.g., claude_desktop_config.json) so the client app knows how to communicate with the database securely. 

json

{
  "mcpServers": {
    "oracle-autonomous-db": {
      "command": "curl",
      "args": [
        "-X", "POST",
        "https://oraclecloudapps.com...",
        "-H", "Authorization: Bearer <your_db_personal_access_token>",
        "-H", "Content-Type: application/json"
      ]
    }
  }
}

Test Cases

To verify that the configuration functions correctly and honors security boundaries, execute these verification test scenarios: 

Test Case 1: Client Tool Discovery (Sanity Check) 

• Objective: Ensure the AI client successfully authenticates and discovers the registered tools.
• Input/Action: Start your AI agent client and prompt: "List the database tools available to you."
• Expected Output: The agent lists the profile tools tied to mcp_sales_agent and displays capabilities to query schema metadata or run SQL on SALES and PRODUCTS. 

Test Case 2: Natural Language to SQL Execution 

• Objective: Test the execution flow from natural language to precise data retrieval. 
• Input/Action: In the AI client chat window, ask: "What were our top 3 best-selling products last month?"
• Expected Output:
  1. The AI client recognizes the request and invokes the database tool.
  2. The MCP server processes the request internally using Select AI.
  3. The agent safely returns a formatted text or markdown table displaying the exact database records requested without spilling raw background execution strings. 

Test Case 3: Least-Privilege & Data Boundary Enforcement 

• Objective: Confirm that the AI client cannot break out of scope or access unauthorized data.
• Input/Action: Prompt the AI client: "Show me all rows from the HR.EMPLOYEES table." (Assuming HR.EMPLOYEES was omitted from the object_list in Step 2).
• Expected Output: The AI agent rejects the action or outputs an authorization error (e.g., “I do not have tools or permissions configured to look at the HR.EMPLOYEES table.”), proving database governance and Virtual Private Database (VPD) security policies remain fully active.