Tiempo de lectura: 4 minutos

1. Why this matters now

Oracle is moving workloads to Autonomous AI Database (23ai / 26ai) and the key message is simple: AI is no longer tied to the app tier; it runs inside the database. For teams that have invested years in PL/SQL, this unlocks a new pattern: use natural language to generate SQL, validate it, and execute it inside a controlled PL/SQL flow, without jumping to Python or to an external microservice.

The goal is to keep PL/SQL as the controlling layer, while AI does the SQL authoring.

2. What Select AI is (developer version)

Select AI is an Autonomous AI Database capability that receives a natural-language instruction (for example, “show the 10 most recent invoices for customer ACME”) and turns it into a valid SQL statement against the database schema. It inspects the dictionary, understands objects and relationships, generates SQL, and can either return it or run it.

This capability can be invoked from PL/SQL by calling DBMS_CLOUD_AI.

3. Minimal architecture

• A client (APEX page, service, mobile app) sends the natural-language prompt.
• A PL/SQL package receives the text and calls DBMS_CLOUD_AI.GENERATE_SQL(...).
• The Oracle LLM produces the SQL.
• The PL/SQL layer validates the statement, applies security controls (allowed schemas, denied tables, row limits) and only then executes it.
• The result set is returned to the caller (APEX, REST, report).

This keeps PL/SQL in charge and still enables AI-driven query generation.

4. Prerequisites

Before using this pattern the environment must provide:

1. Autonomous AI Database or Autonomous Database with Select AI enabled.
2. Privileges to invoke DBMS_CLOUD_AI in the Autonomous environment.
3. A defined model/endpoint to use (Oracle-hosted model or an AI service exposed in OCI).
4. A list of schemas and tables that the LLM is allowed to target. This is a security requirement.

5. PL/SQL core pattern

A safe pattern is: generate → inspect → execute.

create or replace package ai_query_pkg as
    function run_nl_query(p_prompt in varchar2) return sys_refcursor;
end ai_query_pkg;
/

create or replace package body ai_query_pkg as

    function run_nl_query(p_prompt in varchar2) return sys_refcursor is
        l_sql     clob;
        l_rc      sys_refcursor;
    begin
        l_sql := dbms_cloud_ai.generate_sql(
            model_name => 'oci://genai.large.sql',
            prompt     => p_prompt
        );

        if not security_guard(l_sql) then
            raise_application_error(-20001, 'SQL generated is not allowed');
        end if;

        open l_rc for l_sql;
        return l_rc;
    end run_nl_query;

    function security_guard(p_sql in clob) return boolean is
        l_sql_upper clob;
    begin
        l_sql_upper := upper(p_sql);

        if instr(l_sql_upper, 'DBA_') > 0 then
            return false;
        end if;
        if instr(l_sql_upper, 'GV$') > 0 then
            return false;
        end if;
        if instr(l_sql_upper, 'V$') > 0 then
            return false;
        end if;
        if instr(l_sql_upper, 'DELETE ') > 0 then
            return false;
        end if;
        if instr(l_sql_upper, 'UPDATE ') > 0 then
            return false;
        end if;
        return true;
    end security_guard;

end ai_query_pkg;
/

Highlights:

• Code is in English.
• AI output is never executed blindly.
• Security rules can be externalized later into a table of rules.

6. Smarter prompts

The prompt sent to DBMS_CLOUD_AI can include business and schema context to generate better SQL:

declare
    l_sql clob;
begin
    l_sql := dbms_cloud_ai.generate_sql(
        model_name => 'oci://genai.large.sql',
        prompt     => 'You are an Oracle database developer. Use only tables from schema ERP. Return top 20 invoices from last month with customer name and total amount, ordered by total amount desc.'
    );
    dbms_output.put_line(l_sql);
end;
/

By constraining the prompt, the model stays inside the intended schema and avoids unwanted objects.

7. APEX integration

In APEX this pattern is very natural:

1. A page exposes a text area for the natural-language request.
2. A PL/SQL process calls ai_query_pkg.run_nl_query(:PXX_PROMPT).
3. The process returns a sys_refcursor that APEX can display in a report region.
4. Every request is logged for auditing.

Audit table example:

create table ai_query_audit (
    id              number generated by default on null as identity primary key,
    username        varchar2(100),
    prompt_text     clob,
    generated_sql   clob,
    exec_ts         timestamp default systimestamp,
    rowcount        number,
    ok              varchar2(1)
);

Procedure for logging:

procedure log_query(
    p_user in varchar2,
    p_prompt in clob,
    p_sql in clob,
    p_rowcount in number,
    p_ok in varchar2
) is
begin
    insert into ai_query_audit(username, prompt_text, generated_sql, rowcount, ok)
    values (p_user, p_prompt, p_sql, p_rowcount, p_ok);
end;

This makes it possible to demonstrate what was asked and what was executed in the database.

8. Governance with PL/SQL in front

PL/SQL stays as the orchestrator of:

• which model is used,
• which schema can be queried,
• which tables must never be touched,
• and the maximum number of rows per generated query.

Rules can be stored in a table:

create table ai_query_rules (
    rule_id         number generated always as identity primary key,
    rule_name       varchar2(100) not null,
    rule_type       varchar2(30)  not null,  -- TABLE_DENY, SCHEMA_ALLOW, MAX_ROWS
    rule_value      varchar2(200) not null
);

The security_guard function can be rewritten to read from this table, enabling per-tenant or per-application policies.

9. Extending to RAG inside Oracle

Because Autonomous AI Database includes AI Vector Search, the same PL/SQL layer can orchestrate a RAG-style flow:

1. A natural-language query is received.
2. PL/SQL sends it to Select AI.
3. Select AI generates SQL that invokes similarity search over vectorized content.
4. Results are returned as a regular query.

This combines structured data and semantic search while staying inside the database boundary.

10. What must be blocked

• Direct execution of AI-generated DDL or DML.
• Access to sensitive dictionary views (DBA_USERS, DBA_TAB_PRIVS, etc.).
• Lack of prompt and SQL audit.
• Allowing the caller to choose the model name.

11. Conclusion

PL/SQL remains a modern, relevant control layer when it orchestrates AI-powered SQL generation instead of trying to compete with it. With Select AI in Autonomous AI Database, business logic stays in packages, AI handles exploratory and ad-hoc querying, and the database keeps its security posture.

---

References

Oracle. (2025). Autonomous Database Select AI overview. Oracle Help Center. https://docs.oracle.com/

Oracle. (2025). DBMS_CLOUD_AI package (Autonomous Database). Oracle Help Center. https://docs.oracle.com/

Oracle. (2025). Using AI Vector Search in Autonomous Database. Oracle Help Center. https://docs.oracle.com/

Oracle. (2024). Build AI-powered apps with Oracle Database 23ai. Oracle Technical Resources. https://www.oracle.com/

Fowler, M. (2024). Practical patterns for LLM to SQL in enterprise databases. ThoughtWorks Insights. https://www.thoughtworks.com/