In my previous post, I used AI Vector Search in my Oracle AI Database (26ai) to augment what my Agent and associated LLMs knew about personal training with my own workout history and fitness stats.

My MCP Server allows the Agent to search my database for knowledge it can use to assist with it’s answers and task planning to execute the orders I’m giving it.

It came out quite nicely. But it’s obviously also a very personal subject matter and example.

But Jeff, I don’t need a fitness trainer, I need a AI assistant that can help me with my business or technical tasks!

Instead of vectorizing something personal like your 5k runtimes, what if you instead took a valuable collection of unstructured data – like your Sharepoint or Confluence pages, PDF documents, or other instructional guides, and made that knowledge available to your AI tools?

Awhile back I took an export of the Comments folks have left me on this blog, and used that to build a dashboard, using AI. I did that to demonstrate how one can get nice reporting consoles or applications connected to your database, using our MCP Server, SQLcl. And yeah, that came out nicely, and it did help me find stuff via the Vector Search vs a raw keyword or pattern search.

But, I left the true value of this technology just sitting there, pregnant with possibilities…

Search alone is basic, but RAG Pipelining search into your AI Agents for better answers is game-changing

Inside those 20,000+ comments, are the conversations and back and forth Q&A amassed over many, many years.

• How do I do that, in SQL Developer?
• What does this error mean?
• How come we can’t use SYS for ORDS?

The LLMs have been trained on our Docs and maybe some blogs, but I know there’s good stuff in those comments.

So let’s make that data available to our Agent, and make it easy to find with AI Vector Search SQL queries!

Step 1: Get your data sorted.

I already did that. I had a JSON export of my WP_COMMENTS table downloaded, and my Agent easily used my existing Oracle Database AI Agent Skills to move that into the database. We agreed to put those comments into a CLOB.

Next, we need to Vectorize those comments, so they can be searched.

UPDATE thatjeffsmith_comments
SET comment_vectorized = VECTOR_EMBEDDING(
      ALL_MINILM_L12_V2
      USING comment_content AS data
    )
WHERE comment_content IS NOT NULL
  AND comment_approved  = '1'
  AND comment_type      = 'comment'

UPDATE thatjeffsmith_comments
SET comment_vectorized = VECTOR_EMBEDDING(
      ALL_MINILM_L12_V2
      USING comment_content AS data
    )
WHERE comment_content IS NOT NULL
  AND comment_approved  = '1'
  AND comment_type      = 'comment'

FAIL.

The LLM ran into it’s first problem. It couldn’t vectorize the CLOB directly, as the size of the data exceeded the BUFFER limit for text data (char).

So, the Agent resubmitted the job, and this time did a subst() on the CLOB to restrict the input size to 4000 characters. That failed too, we have multibyte characters in there (stupid emojis and and the occasional Chinese, Korean, and Japanese conversations), so it needed to try again, this time just grabbing the first 2,000 characters. The Agent sorted these issues on its own, no intervention needed from me, other than my approvals.

The Agent was also smart enough for these attempts to run the queries, asynchronously, a native feature of our MCP Server. This means the Agent won’t timeout waiting for the response (default timeout is 60 seconds). Instead, it gets a job ID # it can use to check for the status of our SQL execution.

Note I didn’t have to instruct the agent to do this, it ‘knew’ that would be necessary and our MCP Server tool descriptions made it very easy to pick ‘the right path.’

Now, this took a bit longer to run than last time..almost an hour.

But, it finished!

And then our Agent was ready to give it a go!

My Agent did a really nice job of constructing the skill as well.

Let’s try it out!

If I want to build an ords rest api and have case sensitive or camel case json property names, how would i do that?

The Agent’s Answer, note the LLM employeed is Sonnet 4.6 Extended.

To summarize, it got it completely wrong, it said you have two choices:

• alias the query, e.g. select FIRST_NAME “firstName” from employees OR
• create a view, and rename the column as desired

The RAG Enhanced answer however was much better –

use JSON_OBJECT to build the JSON yourself and mark the column with the {} sentinel so ORDS knows not to touch it:

sql

SELECT
    JSON_OBJECT(
        'employeeId'  VALUE employee_id,
        'firstName'   VALUE first_name,
        'lastName'    VALUE last_name,
        'hireDate'    VALUE hire_date
        RETURNING CLOB
    ) AS "{}row"
FROM employees

SELECT
    JSON_OBJECT(
        'employeeId'  VALUE employee_id,
        'firstName'   VALUE first_name,
        'lastName'    VALUE last_name,
        'hireDate'    VALUE hire_date
        RETURNING CLOB
    ) AS "{}row"
FROM employees

The {} prefix on the alias is the signal to ORDS: this column is already JSON, pass it through as-is. Since you’re constructing the JSON yourself with JSON_OBJECT, you have full control over key casing — and ORDS won’t interfere.

The agent was able to come up with this suggestion because it was told about it from the conversations I had with Claudiu and Herman in 2020 and 2021, respectively.

Resulting Skill – here’s what my agent put together for when I want help with our tools

last note from me, before I dump in the markdown from the skill – my agent put together an amazing plan for utilizing this pipeline, see in particular the Query it put together and how to prioritize the content

I hope you enjoyed this post, until next time, – just Jeff

Jeff Smith’s personal RAG-enhanced advisor for SQL Developer, SQLcl, and ORDS questions. Use this skill whenever Jeff — or anyone — asks a question about Oracle SQL Developer, SQLcl, or ORDS (Oracle REST Data Services). This includes feature questions, how-to questions, bug workarounds, configuration, tips, comparisons, and troubleshooting. Trigger phrases include: “SQL Developer”, “SQLcl”, “ORDS”, “AutoREST”, “REST enable”, “SQL worksheet”, “connection type”, “export data”, “REST API Oracle”, “PL/SQL gateway”, “thatjeffsmith”, “your blog”, “have you written about”, “what do your readers ask”. Jeff has 15+ years of blog comments representing thousands of real customer questions and his own answers — always retrieve from this vector store before responding. Trigger aggressively: if the topic touches SQL Developer, SQLcl, or ORDS in any way, use it.

Jeff’s Oracle Comments RAG — SQL Developer, SQLcl & ORDS

Jeff Smith is Oracle’s product manager for SQL Developer, SQLcl, and ORDS. His blog (thatjeffsmith.com) has accumulated 15+ years of comments — over 22,000 — representing real customer questions, bug reports, workarounds, and Jeff’s own authoritative answers.

This skill powers a RAG pipeline where Claude supplements its own training with semantically relevant comment threads retrieved from Oracle AI Vector Search.

Always Follow This Two-Part Answer Format

Every response must have two sections:

1. Generic Answer — Written BEFORE querying the database. Based purely on Claude’s training knowledge. What any competent Oracle expert would say.
2. RAG-Enhanced Answer — Written AFTER the vector search results are in context. Ground the answer in real questions and answers from Jeff’s blog community. Surface patterns, recurring issues, specific workarounds, and Jeff’s own phrasing where relevant.

⚠️ Never let the database results influence the generic answer. Write generic first, then query, then write the enhanced section.

Database Connection

Connection name: Project Raptor 🦖 Tool: SQLcl MCP server (sqlcl:connect → sqlcl:run-sql) ONNX Model: ALL_MINILM_L12_V2 (owned by HR schema) Table: THATJEFFSMITH_COMMENTS Vector column: COMMENT_VECTORIZED

Connect at the start of every session:

sqlcl:connect("Project Raptor 🦖")

The RAG Query

Use this template for all Oracle tool questions. Tune the USING clause to match the question. Always use DBMS_LOB.SUBSTR when referencing COMMENT_CONTENT — it’s a CLOB.

sql

SELECT
    c.comment_id,
    c.comment_date,
    c.comment_author,
    c.comment_post_id,
    DBMS_LOB.SUBSTR(c.comment_content, 2000, 1) AS comment_text,
    VECTOR_DISTANCE(
        c.comment_vectorized,
        VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING '<search phrase>' AS data),
        COSINE
    ) AS distance
FROM thatjeffsmith_comments c
WHERE c.comment_vectorized IS NOT NULL
  [ADD FILTERS AS NEEDED — see Filter Reference below]
ORDER BY distance ASC
FETCH FIRST 8 ROWS ONLY

Tuning the Search Phrase

Match the phrase to the intent of the question:

Filter Reference

Combine filters to narrow results:

Key Facts About This Dataset

• 22,728 total comments, 22,638 vectorized (approved comments + Jeff replies only)
• Date range: 2010 through present — covers SQL Developer 1.x through 24.x, SQLcl, ORDS
• Comment authors: Mix of Jeff’s own replies (JeffS) and reader questions/reports
• Topics: Heavily weighted toward SQL Developer UI, ORDS configuration, SQLcl CLI, connection setup, export/import, REST API design, authentication, and plugin questions
• Content type: Real support interactions — bug reports, workarounds, config questions, feature requests — not polished documentation

Interpreting Results

When surfacing results:

• Prioritize Jeff’s own replies — they are authoritative answers, not just questions
• Quote or paraphrase the actual comment text — the real wording is the value
• Note recurring themes — if 6 of 8 results mention the same issue, that’s a signal
• Surface workarounds explicitly — these are gold for users hitting the same issue
• Note the date — older comments may reference UI or behavior that has since changed

Example: What the RAG Answer Should Add

Generic answer: “To REST-enable a table in ORDS, use ORDS.ENABLE_OBJECT or check AutoREST in SQL Developer’s REST Development pane.”

RAG-enhanced: “Jeff’s readers have hit several recurring snags here: the most common is that AutoREST silently fails when the table owner doesn’t have the ORDS_METADATA privilege. One 2019 comment thread shows Jeff confirming that a manual COMMIT is required after ORDS.ENABLE_OBJECT in older ORDS versions. Several commenters also note confusion between enabling at the schema level vs. the object level — Jeff’s standard reply is to check SELECT * FROM USER_ORDS_ENABLED_OBJECTS first.”