AgentMail for Oracle

PL/SQL package to talk to the AgentMail API from Oracle Database: list inboxes, list/fetch messages, send and reply, and download attachments into local tables.

Get your AgentMail API key from the AgentMail Console; all calls use Bearer token authentication.

---

Requirements

• Oracle Database 12c or later (uses JSON_TABLE, UTL_HTTP, JSON_OBJECT_T / JSON_ARRAY_T)
• Oracle APEX installed in the database (for apex_web_service, apex_util, apex_debug)
• Network ACL that allows outbound HTTPS to AgentMail hosts (see Network ACL)

---

Installation

Run everything from the schema that will own agentmail (unless noted as DBA-only).

1. (DBA) Configure network ACL As a DBA (or privileged user) run run_as_admin.sql in the CDB/PDB that will host AgentMail: This grants the owning schema HTTP access to:

• api.agentmail.to
• storage.agentmail.to
• cdn.agentmail.to

2. Configure basic settings (optional) Edit agentmail_config.sql (or create your own secrets.sql) to set values like: For production, prefer putting real values in a local, uncommitted secrets.sql that overrides agentmail_config.
3. Install all AgentMail objects Connect as the target schema and run: This will:

• Run agentmail_config.sql
• Create utility helpers (drop_object, does_table_exist, add_primary_key, etc.) via agentmail_utils.sql
• Create tables and type via agentmail_schema.sql:
  • agentmail_api_token
  • agentmail_messages
  • agentmail_recipients
  • agentmail_attachments
  • agentmail_message_detail
  • agentmail_attachment_content
  • agentmail_inboxes
  • label_array type
• Compile the agentmail package spec/body
• Compile the agentmail_test package (smoke tests)
• Optionally run secrets.sql at the end if the file exists

4. Store your API key After install, update the default token row in agentmail_api_token (see Storing your API key).

---

Storing your API key

The package uses the **agentmail_api_token** table and the **agentmail.get_default_token** function for authentication.

1. Find or create the default token row The install scripts create agentmail_api_token with primary key token_name. You can insert or update a row named 'DEFAULT':
2. How authentication works

• agentmail.get_default_token reads token_value from the 'DEFAULT' row.
• Each API call sets the header Authorization: Bearer <get_default_token()>.
• You do not need to set any package-level api_key variable; the table is the single source of truth.

1. Keeping secrets out of git

• Never commit real API keys into SQL scripts.
• Use a secrets.sql file that is not committed and is already included in .gitignore.
• In your deployment pipeline you can run an update similar to the merge above from a secure location.

---

Package agentmail — API overview

Public members (simplified):

Member	Type	Description
**last_inbox_id**	variable	Last created inbox ID from create_inbox.
**last_response_json**	variable	Last HTTP response body (CLOB).
**last_status_code**	variable	Last HTTP status code.
**last_response_message**	variable	Short message / summary for the last call.
**get_default_token**	function	Returns the token_value for 'DEFAULT' from agentmail_api_token.
**list_messages**	procedure	GET /v0/inboxes/{inbox_id}/messages → upserts into agentmail_messages, agentmail_recipients, agentmail_attachments.
**get_message**	procedure	GET /v0/inboxes/{inbox_id}/messages/{message_id} → upserts into agentmail_message_detail and related tables.
**get_attachment**	procedure	GET attachment metadata, then download binary via download_url → upserts into agentmail_attachment_content.
**list_inboxes**	procedure	GET /v0/inboxes → upserts into agentmail_inboxes.
**create_inbox**	procedure	POST /v0/inboxes → creates a new inbox and merges it into agentmail_inboxes.
**send_message**	procedure	POST /v0/inboxes/{inbox_id}/messages/send to send an email.
**reply_to_message**	procedure	POST /v0/inboxes/{inbox_id}/messages/{message_id}/reply to reply to a message.
**parse_list_messages_response**	procedure	Internal helper used by list_messages.
**parse_list_inboxes_response**	procedure	Internal helper used by list_inboxes.

Main tables

• **agentmail_messages** – Inbox message metadata (inbox_id, thread_id, message_id, labels JSON string, subject, preview, created/updated timestamps, etc.).
• **agentmail_message_detail** – Full message payload from get_message (bodies, headers, recipients JSON, attachments JSON, etc.).
• **agentmail_recipients** – Normalized TO/CC/BCC recipients per message (one row per recipient).
• **agentmail_attachments** – Attachment metadata per message (filename, content type, content_id, size, etc.).
• **agentmail_attachment_content** – Downloaded attachment binary and metadata from get_attachment (BLOB content).
• **agentmail_inboxes** – Inboxes returned by list_inboxes and create_inbox.
• **agentmail_api_token** – API tokens keyed by token_name (usually just 'DEFAULT').
• **label_array** – Nested table type used for label and address arrays.

---

Quick examples

List inboxes

BEGIN
   agentmail.list_inboxes(p_limit => 50);
END;
/ 

List recent messages for an inbox

DECLARE
   l_inbox_id VARCHAR2(100) := agentmail_config.default_inbox_id;
BEGIN
   agentmail.list_messages(
      p_inbox_id   => l_inbox_id,
      p_limit      => 20,
      p_after      => SYSTIMESTAMP - INTERVAL '1' DAY
   );
END;
/ 

Fetch a single message and its detail

DECLARE
   l_inbox_id   VARCHAR2(100) := agentmail_config.default_inbox_id;
   l_message_id VARCHAR2(100) := 'msg_xxx';
BEGIN
   agentmail.get_message(
      p_inbox_id   => l_inbox_id,
      p_message_id => l_message_id
   );
END;
/ 

Send a message

DECLARE
   l_inbox_id VARCHAR2(100) := agentmail_config.default_inbox_id;
BEGIN
   agentmail.send_message(
      p_inbox_id  => l_inbox_id,
      p_email     => l_inbox_id,
      p_subject   => 'Test from PL/SQL',
      p_body      => 'Hello from PL/SQL',
      p_to_email  => 'someone@example.com',
      p_labels    => label_array('outbound', 'test')
   );
END;
/ 

Reply to a message

DECLARE
   l_inbox_id   VARCHAR2(100) := agentmail_config.default_inbox_id;
   l_message_id VARCHAR2(100);
BEGIN
   -- Example: pick a recent 'received' message from AGENTMAIL_MESSAGES
   SELECT MAX(message_id) KEEP (DENSE_RANK LAST ORDER BY NVL(timestamp_, created_at) NULLS LAST)
     INTO l_message_id
     FROM agentmail_messages
    WHERE labels IS NOT NULL
      AND (',' || REPLACE(REPLACE(REPLACE(REPLACE(labels, '[', ''), ']', ''), '"', ''), ' ', '') || ','
           LIKE '%,received,%');

   agentmail.reply_to_message(
      p_inbox_id   => l_inbox_id,
      p_message_id => l_message_id,
      p_text       => 'Reply from Oracle PL/SQL',
      p_reply_all  => 'Y',
      p_labels     => label_array('reply', 'test')
   );
END;
/ 

---

Testing

The agentmail_test package provides a small integration test harness.

Run all smoke tests

BEGIN
   agentmail_test.run_all(
      p_inbox_id => agentmail_config.test_inbox_id
   );
END;
/ 

This will:

• List inboxes via agentmail.list_inboxes
• List messages for p_inbox_id
• Fetch the latest message
• Send a reply to that message
• Download the latest attachment (if present)

Individual procedures are also available:

• smoke_list_messages(p_inbox_id)
• smoke_get_latest_message(p_inbox_id)
• smoke_reply_latest_message(p_inbox_id, p_text)
• smoke_download_latest_attachment(p_inbox_id)
• smoke_list_inboxes(p_limit)

There is also a agentmail_test_suite_beta procedure that exercises send_message against a hard-coded test inbox for ad‑hoc testing.

---

Network ACL (DBA only)

The run_as_admin.sql script must be run by a DBA (or equivalent). It:

1. Creates or reuses an ACL agentmail_http_acl.xml.
2. Grants connect and resolve privileges to the AgentMail-owning schema (&principal).
3. Assigns the ACL to api.agentmail.to.
4. Adds host ACEs so UTL_HTTP can call:

• api.agentmail.to:443
• storage.agentmail.to:443
• cdn.agentmail.to:443

You can verify ACL grants with the final SELECT in run_as_admin.sql (querying dba_host_aces).

Without this step, REST calls and attachment downloads will fail with UTL_HTTP / network ACL errors.

---

Uninstall

To drop all AgentMail objects (but not the network ACL), connect as the owning schema and run:

@agentmail_drop.sql

This drops:

• agentmail package (spec and body)
• agentmail_test and agentmail_test_suite_beta
• agentmail_config
• All AgentMail tables and type (agentmail_*, label_array)
• Utility helpers (drop_table, add_primary_key, does_*)

If you also want to remove the network ACL itself, that must be done manually as a DBA.

---

References

• AgentMail – email for agents and APIs
• AgentMail API / Console – API key and docs
• API base URL: https://api.agentmail.to/v0