Build an agent with a search tool
By the end of this lesson, you can register a search tool and an agent, run the agent as an ADM user, and read the tool call and the tool result in the trace.
Identify the tool, the profile and the agent
Section titled “Identify the tool, the profile and the agent”This lesson continues in the schema of lesson 1. An agent needs three things. A tool is a PL/SQL function that the model can call. A prompt profile holds the system prompt, the model and the rules for the tools.
An agent points at a profile, and you run it with uc_ai_agents_api.execute_agent. The UC AI tutorial
Build an agent explains these objects. This lesson shows how they connect to ADM.
The install of lesson 1 already created the tool package, the profile and the agent, so that the scripts of the course run. The lesson shows the parts that matter.
Wrap the collection search in a tool
Section titled “Wrap the collection search in a tool”A tool takes one JSON text and returns one JSON text. It never raises an error, because an error inside a tool call can end the whole agent run. With OpenAI, the provider of this course, it does.
Every answer carries a status, so that the model and your own tests can tell the cases apart:
| Status | Meaning |
|---|---|
ok |
The search found passages that the current user may see. |
no_passages |
The search ran and found nothing above the score threshold. |
no_identity |
No ADM user is signed in on the database session, so nothing can be searched. |
error |
Anything else. The details go to the APEX debug log, not into the answer. |
The function reads the user, then searches through adm_ai_rag_api.prepare_sources. That call goes through search_chunks, which checks every chunk with adm_access_control_api.user_is_allowed_to_view_document_personal for the current user. “Personal” means without the administrator bypass: the user sees a chunk only from a document that they own or that is shared with them, also when they have the role ADMIN:
l_user := sys_context(adm_context_api.c_ctx_namespace, adm_context_api.c_ctx_username);if l_user is null then return status_json('no_identity', p_collection_name , 'No user is signed in on this connection, so no documents can be searched. ' || 'This is a system fault, not an empty result. Tell the user that the search is unavailable.');end if;
select json_arrayagg( json_object('document' value document_name, 'score' value round(avg_score, 3) , 'matched_chunks' value chunk_count, 'text' value source_text returning clob) order by avg_score desc returning clob) into l_passages from table(adm_ai_rag_api.prepare_sources( p_rag_collection_id => l_collection , p_query => p_query , p_max_sources => p_max_sources));The check for the user comes first on purpose. With no ADM user, prepare_sources returns no rows and no error. The agent would then say “nothing found”, and a reader would take that for a permission result.
Register the tool, the profile and the agent
Section titled “Register the tool, the profile and the agent”08_aic_register.sql registers one tool, one prompt profile and one agent. The tool description is what the model reads to decide when to call the tool:
l_tool_id := uc_ai_tools_api.merge_tool_from_schema( p_tool_code => 'AIC_SEARCH_CONTRACTS', p_description => 'Search the contracts of the company (supply, maintenance, framework and distribution ' || 'agreements and their amendments). Returns the best matching passages with the ' || 'name of the document. Use short queries made of the key words, for example ' || '"Alpenwerk notice period termination".', p_function_call => 'return aic_rag_tools_api.search_contracts(:parameters);', p_json_schema => json_object_t('{ "type": "object", "properties": { "query": { "type": "string", "description": "What to look for. Key words, not a whole sentence." }, "max_sources": { "type": "number", "description": "How many documents at most. Default 5." } }, "required": ["query"] }'), p_tags => apex_t_varchar2('aic_demo'));The profile selects its tools by tag. The tag aic_demo is the only link between the profile and the tool, which lesson 3 uses. The system prompt has six rules:
1. Search before you answer. Call the search tools with short queries made of the key words (names, terms), not with the whole question. If the first search finds nothing useful, try again with other words.2. Answer only from the passages the tools return. Never answer from memory, and never fill a gap with a guess.3. Citation rule: every fact in your answer comes with the quoted clause or sentence in quotation marks, followed by the name of the document it comes from in brackets.4. If the documents give different answers (for example two versions of a policy, or a contract and its amendment), say so, name both documents, and say which one is newer when the documents tell you.5. If a tool returns the status no_passages, say that you found nothing in the documents you can see. If it returns no_identity or error, say that the search is not available right now. Do not try to answer in that case.6. Keep the answer short.Rule 5 ties the prompt to the tool statuses. The profile also carries its own provider and model, and its model configuration holds the tool tag and the web credential ("g_tool_tags": ["aic_demo"], "g_apex_web_credential": "OPENAI").
The ADM settings AI_AGENT_PROVIDER and AI_AGENT_MODEL only reach the two profiles that the AI Pack ships. A profile of your own is yours to configure.
Run the agent as an ADM user
Section titled “Run the agent as an ADM user”An agent run needs an ADM user, because the tool reads the user from the ADM context. The obvious way from SQL is to set the user and call execute_agent.
Open a new SQLcl session and run @09_aic_identity_wipe.sql, which does exactly that. The trap only appears on the first execute_agent call of a database session, so this script needs a session of its own:
adm_context_api.system_user_login('AIC_LEGAL');dbms_output.put_line('context before execute_agent: ' || sys_context('ADM_CONTEXT', 'ADM_USERNAME'));
l_result := uc_ai_agents_api.execute_agent( p_agent_code => 'aic_assistant', p_input_parameters => json_object_t('{"question":"What notice period applies to the Alpenwerk maintenance contract?"}'));
dbms_output.put_line('context after execute_agent: ' || nvl(sys_context('ADM_CONTEXT', 'ADM_USERNAME'), '(empty)'));context before execute_agent: AIC_LEGALcontext after execute_agent: (empty)
Tool result the model saw: {"status":"no_identity","collection":"aic_contracts","message":"No user is signed in on this connection, so no documents can be searched. This is a system fault, not an empty result. Tell the user that the search is unavailable."}
Answer: The document search is unavailable right now, so I can’t verify the notice period for the Alpenwerk maintenance contract.Tokens: {"prompt_tokens":906,"completion_tokens":60,"reasoning_tokens":0,"total_tokens":966}The user is gone after the call, and the tool answered no_identity. Without the status check in the tool, the answer would have been “nothing found”.
When no APEX session exists, execute_agent creates one for the run, in an application that it picks from the applications that the schema can see, except those of the workspace INTERNAL. The pick has no fixed order, so it can be any of them. If the initialization code of that application establishes the ADM context from the session, as the code of the ADM application does,
the context is cleared. The first call of a database session creates the APEX session, and later calls in the same session reuse it, so the problem depends on the order of the calls. Session Init Code, which appears in the next box, is a block of PL/SQL that the chat plug-in of lesson 4 runs before the agent.
Run @10_aic_first_run.sql in any session. run_as creates the APEX session itself, so the later scripts of the course need no fresh session. The script runs the agent as AIC_LEGAL and prints the trace of the run:
Answer:The notice period is **30 days to the end of a calendar month**, and notice must be in writing: “Either party may terminate this Contract with a notice period of 30 days to the end of a calendar month. The notice must be in writing.” (contract_alpenwerk_maintenance.pdf)
Tool calls: 1Tokens: {"prompt_tokens":1112,"completion_tokens":98,"reasoning_tokens":0,"total_tokens":1210}Seconds: 6Context after the run: (empty)Execution id: 542 session id: 5E81421E36B538D7E0630201590ADF42
What the model asked for and what the tool answered (the trace):
SEQ ROLE TOOL_NAME WHAT______ ______________ _______________________ ___________________________________________________________________________________________________________________________________________________ 1 user What notice period applies to the Alpenwerk maintenance contract? 2 tool_call AIC_SEARCH_CONTRACTS {"max_sources":5,"query":"Alpenwerk maintenance notice period termination"} 3 tool_result AIC_SEARCH_CONTRACTS {"status":"ok","collection":"aic_contracts","searched_as":"AIC_LEGAL","passages":[{"document":"contract_alpenwerk_maintenance.pdf","score":0 ... 4 assistant The notice period is **30 days to the end of a calendar month**, and notice must be in writing: “Either party may terminate this Contract wiRead the tool call and the tool result
Section titled “Read the tool call and the tool result”The trace has four rows: the question, the tool call with the words the model chose, the tool result and the answer.
The tool result shows "searched_as":"AIC_LEGAL": the tool read the user from the ADM context while the model was running.
This is the check that the identity arrived. A tool that puts the user name into the text that the model reads is a teaching aid, so log it instead in an application.
The answer quotes the clause and names the document, as rule 3 of the prompt asks. The run used one tool call, and the token counts in the recording show the cost of one question.