Code for "Secure and serverless GenAI Apps on AWS with Oracle AI Database 26ai"

demo.sql

-- Create credential
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_GENAI_CRED',
    user_ocid       => 'ocid1.user.oc1..xxxxxxxx',
    tenancy_ocid    => 'ocid1.tenancy.oc1..xxxxxxxx',
    private_key     => '-----BEGIN RSA PRIVATE KEY-----
xxxxxxxxxxxxxxxxxxxxxxxx
-----END RSA PRIVATE KEY-----',
    fingerprint     => 'xx:xx:xx:xx:xx:xx'
  );
END;
/

-- START WEBINAR - DEMO 1
-- Create the Customers table
CREATE TABLE BANK_CUSTOMERS (
    customer_id   NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    full_name     VARCHAR2(100),
    email         VARCHAR2(100),
    country       VARCHAR2(50),
    account_type  VARCHAR2(20) CHECK (account_type IN ('Savings', 'Checking', 'Investment')),
    join_date     DATE
);

-- Create the Support Tickets table
-- Note: This links back to customers. The AI will handle this relationship.
CREATE TABLE SUPPORT_TICKETS (
    ticket_id     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    customer_id   NUMBER,
    issue_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    category      VARCHAR2(50),
    severity      VARCHAR2(20) CHECK (severity IN ('Low', 'Medium', 'High', 'Critical')),
    issue_text    VARCHAR2(4000), -- This is the unstructured text we will vectorise later
    status        VARCHAR2(20) DEFAULT 'Open',
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES BANK_CUSTOMERS(customer_id)
);

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name => 'AI_WITH_SCHEMA',
      attributes   =>
         '{"provider": "oci",
          "credential_name": "OCI_GENAI_CRED",
          "oci_compartment_id": "ocid1.compartment.oc1..xxxxxx",
          "region":"us-chicago-1", 
          "object_list": [
            {"owner": "ADMIN", "name": "BANK_CUSTOMERS"},
            {"owner": "ADMIN", "name": "SUPPORT_TICKETS"}
          ]
         }'
  );
END;
/

-- Enable the profile for your session
EXEC DBMS_CLOUD_AI.SET_PROFILE('AI_WITH_SCHEMA');

-- Simple Generate test
SELECT DBMS_CLOUD_AI.GENERATE(
  prompt => 'Generate a SQL query that returns the total number of customers.', -- will generate SQL
  profile_name => 'AI_WITH_SCHEMA'
)
FROM dual;

-- Test if it work with 1 simple customer (10 sec)
BEGIN
  DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA( -- <- here's the magic
    profile_name => 'AI_WITH_SCHEMA',
    object_name  => 'BANK_CUSTOMERS',
    record_count => 1,
    user_prompt  => 'Create 1 customer. Do NOT generate NULL values.'
  );
END;
/

SELECT * FROM BANK_CUSTOMERS; -- great! our first customer :)

TRUNCATE TABLE BANK_CUSTOMERS; -- start over

-- 1. Generate Customers first (so tickets have valid parents) (~1-2min)
BEGIN
    DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
        profile_name => 'AI_WITH_SCHEMA',
        object_name  => 'BANK_CUSTOMERS',
        record_count => 50,
        user_prompt  => 'Generate realistic banking customers residing in the UK and US. 
                         Vary the account types between Savings and Investment.'
    );
END;
/

SELECT COUNT(*) FROM BANK_CUSTOMERS; -- great! nailed it (or almost) 

SELECT * FROM BANK_CUSTOMERS; -- now 50 customers

-- 2. Generate Tickets (The rich text for RAG) (~3-5 min)
BEGIN
    DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
        profile_name => 'AI_WITH_SCHEMA',
        object_name  => 'SUPPORT_TICKETS',
        record_count => 100,
        user_prompt  => 'Generate support tickets focusing on common banking issues. 
                         Include 20% "Critical" severity tickets related to fraud or account lockouts. 
                         The "issue_text" should be detailed, natural language complaints 
                         about failed transactions, hidden fees, or mobile app crashes. 
                         Ensure the tone varies from polite to frustrated.'
    );
END;
/

SELECT count(*) FROM SUPPORT_TICKETS; -- nailed it!

SELECT * FROM SUPPORT_TICKETS; -- w/ referential integrity!

SELECT count(*) -- almost nailed it (~20%)
FROM BANK_CUSTOMERS c
JOIN SUPPORT_TICKETS t ON c.customer_id = t.customer_id
WHERE t.severity = 'Critical';

SELECT c.full_name, c.country, t.severity, t.issue_text
FROM BANK_CUSTOMERS c
JOIN SUPPORT_TICKETS t ON c.customer_id = t.customer_id
WHERE t.severity = 'Critical';

-- DEMO 2

-- Add a new column to store the vector embeddings
-- We use 384 dimensions because that is what the 'all-MiniLM-L12-v2' model produces.
ALTER TABLE SUPPORT_TICKETS ADD (issue_vector VECTOR(384, FLOAT32));

-- 1. Create directory
CREATE OR REPLACE DIRECTORY ONNX_IMPORT_DIR AS 'onnx_import_dir';

-- 2. Download the model (Public Oracle Bucket)
BEGIN
  DBMS_CLOUD.GET_OBJECT(
    credential_name => NULL,
    object_uri      => 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/all_MiniLM_L12_v2.onnx',
    directory_name  => 'ONNX_IMPORT_DIR',
    file_name       => 'all_MiniLM_L12_v2.onnx'
  );
  -- Use pretrained embeddings unless your business logic depends on linguistic nuance (10-20% of use cases).
  -- Most RAG systems fail because of poor retrieval — not because of the embedding model.
END;
/

-- List files in the ONNX_IMPORT_DIR directory
SELECT * FROM DBMS_CLOUD.LIST_FILES('ONNX_IMPORT_DIR'); -- it's there!!

-- 3. Register the model in the Database
BEGIN
   DBMS_VECTOR.LOAD_ONNX_MODEL(
     directory  => 'ONNX_IMPORT_DIR',
     file_name  => 'all_MiniLM_L12_v2.onnx',
     model_name => 'LOCAL_EMBEDDING_MODEL',
     metadata   => JSON('{"function": "embedding", "embeddingOutput": "embedding", "input": {"input": ["DATA"]}}')
     -- metadata acts as a configuration bridge between Oracle's vector functions and the generic ONNX model format
     -- so the DB knows exactly how to pass data in and extract vectors out
   );
END;
/

-- 4. Verify the model registration
SELECT model_name, algorithm, mining_function
FROM user_mining_models WHERE model_name = 'LOCAL_EMBEDDING_MODEL';

-- Update the table to generate embeddings for all 100 tickets
-- This runs entirely on your Exadata CPUs (Local Transformer)
-- Use 'AS DATA' to match the attribute name defined during model load
UPDATE SUPPORT_TICKETS 
SET issue_vector = VECTOR_EMBEDDING(LOCAL_EMBEDDING_MODEL USING issue_text AS DATA);

COMMIT;

-- Verify the embeddings are there
SELECT issue_text, issue_vector 
FROM SUPPORT_TICKETS 
WHERE issue_vector IS NOT NULL
FETCH FIRST 5 ROWS ONLY;

-- In order to keep the embeddings column up to date you need to set up a trigger like this
CREATE OR REPLACE TRIGGER trg_support_tickets_embedding
BEFORE INSERT OR UPDATE OF issue_text ON SUPPORT_TICKETS
FOR EACH ROW
BEGIN
    SELECT VECTOR_EMBEDDING(LOCAL_EMBEDDING_MODEL USING :NEW.issue_text AS DATA)
    INTO :NEW.issue_vector
    FROM DUAL;
END;
/

-- ======================================================================
-- PART D: Create Vector Index
-- ======================================================================

-- Create an HNSW index for fast similarity search.
-- This is essential for performance when scaling beyond a few hundred rows.
CREATE VECTOR INDEX TICKET_VECTOR_IDX ON SUPPORT_TICKETS (issue_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE;
-- Vector indexes are later maintained automatically, just like regular B-tree or bitmap indexes.

-- ======================================================================
-- PART E: Semantic Search Test & Index Verification
-- ======================================================================

-- 1. VERIFY VECTOR INDEX (Standard Oracle 26ai Method)
-- 'VECTOR' is the official index_type for AI Vector Search indexes.
SELECT index_name, table_name, status, index_type 
FROM user_indexes 
WHERE index_type = 'VECTOR';

-- 2. Test the "Knowledge Retrieval" capability locally, we pass the inout data to the embedding for matching
SELECT issue_text, severity, status
FROM SUPPORT_TICKETS
ORDER BY VECTOR_DISTANCE(issue_vector, 
                         VECTOR_EMBEDDING(LOCAL_EMBEDDING_MODEL USING 'My bank account has a suspicious transaction from another country' AS DATA), 
                         COSINE)
FETCH FIRST 3 ROWS ONLY;

-- More efficient version: make the query embedding persistent to avoid recomputing the query vector per row.
WITH q AS (
  SELECT VECTOR_EMBEDDING(
           LOCAL_EMBEDDING_MODEL
           USING 'My bank account has a suspicious transaction from another country'
           AS DATA
         ) v
  FROM dual
)
SELECT issue_text, severity, status
FROM SUPPORT_TICKETS, q
ORDER BY VECTOR_DISTANCE(issue_vector, q.v, COSINE)
FETCH FIRST 3 ROWS ONLY;

-- Hybrid filter: standard constrain + vector constrain
SELECT issue_text, severity, status
FROM SUPPORT_TICKETS
WHERE severity = 'Critical'
ORDER BY VECTOR_DISTANCE(
  issue_vector,
  VECTOR_EMBEDDING(
    LOCAL_EMBEDDING_MODEL USING
    'suspicious foreign transaction'
    AS DATA),
  COSINE
)
FETCH FIRST 3 ROWS ONLY;

-- Let's wrap this up in a function
CREATE OR REPLACE FUNCTION search_tickets(
    p_query     VARCHAR2,
    p_top_n     NUMBER DEFAULT 5,
    p_severity  VARCHAR2 DEFAULT NULL
) RETURN SYS_REFCURSOR
IS
    v_result SYS_REFCURSOR;
BEGIN
    OPEN v_result FOR
        SELECT issue_text, severity, status
        FROM SUPPORT_TICKETS
        WHERE (p_severity IS NULL OR severity = p_severity)
        ORDER BY VECTOR_DISTANCE(
            issue_vector,
            VECTOR_EMBEDDING(LOCAL_EMBEDDING_MODEL USING p_query AS DATA),
            COSINE)
        FETCH FIRST p_top_n ROWS ONLY;
    RETURN v_result;
END;
/

SELECT search_tickets('suspicious foreign transaction', 3, 'Critical') FROM DUAL;

-- DEMO 3

-- Chat only profile, no object_list
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'AI_CHAT_ONLY',
    attributes   => '{
      "provider": "oci",
      "credential_name": "OCI_GENAI_CRED",
      "region": "us-chicago-1",
      "oci_compartment_id": "ocid1.compartment.oc1..xxxxxxxx"
    }'
  );
END;
/

-- Define query, build context explicitely and use it
SELECT DBMS_CLOUD_AI.GENERATE(
  prompt =>
    'You are a banking support analyst.' || CHR(10) ||
    'Based ONLY on the following customer support tickets, explain in plain English why customers are complaining.' || CHR(10) ||
    'Tickets:' || CHR(10) || -- schema is not passed as context but we're passing the relevant tickets here and that's how the LLM gets context
    LISTAGG('- ' || issue_text, CHR(10))
      WITHIN GROUP (ORDER BY 1),
  profile_name => 'AI_CHAT_ONLY',
  action       => 'chat'
)
FROM (
  SELECT issue_text
  FROM SUPPORT_TICKETS
  ORDER BY VECTOR_DISTANCE(
    issue_vector,
    VECTOR_EMBEDDING(
      LOCAL_EMBEDDING_MODEL
      USING 'suspicious transactions or unrecognized transactions'
      AS DATA
    ),
    COSINE
  )
  FETCH FIRST 5 ROWS ONLY
);

-- Let's put everything into a nice procedure
--The SELECT_AI_RAG function is well designed:
--Embeds the prompt locally (your ONNX model)
--Retrieves relevant tickets via your HNSW index
--Enriches context with customer data (country, severity)
--Passes context + prompt to OCI GenAI via SelectAI
CREATE OR REPLACE FUNCTION SELECT_AI_RAG (
  p_prompt IN VARCHAR2,
  p_action IN VARCHAR2 DEFAULT 'chat'
) RETURN CLOB
AS
  v_context        CLOB := '';
  v_final_prompt   CLOB;
  v_action         VARCHAR2(20);
BEGIN
  /* Normalize action */
  v_action := LOWER(TRIM(p_action));

  IF v_action NOT IN (
       'chat',
       'narrate',
       'showsql',
       'explainsql',
       'runsql'
     )
  THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'Invalid action. Allowed values: chat, narrate, showsql, explainsql, runsql'
    );
  END IF;

  /* 1. Retrieve semantic context (RAG) */
  FOR r IN (
    SELECT
        'Country: ' || bc.country ||
        ' | Severity: ' || st.severity ||
        ' | Issue: ' || st.issue_text AS context_line
    FROM support_tickets st
    JOIN bank_customers bc
        ON bc.customer_id = st.customer_id
    ORDER BY vector_distance(
        st.issue_vector,
        vector_embedding(
        local_embedding_model
        USING p_prompt AS DATA
        ),
        cosine
    )
    FETCH FIRST 10 ROWS ONLY
  ) LOOP
  v_context := v_context || '- ' || r.context_line || CHR(10);
  END LOOP;

  /* 2. Build prompt dynamically based on action */
  v_final_prompt :=
      'You are a banking support intelligence assistant.' || CHR(10) ||
      'Use ONLY the support tickets below as factual context.' || CHR(10);

  IF v_action IN ('runsql', 'showsql', 'explainsql') THEN
    v_final_prompt := v_final_prompt ||
      'Generate SQL only if required by the action.' || CHR(10) ||
      'Use the ADMIN.SUPPORT_TICKETS and ADMIN.BANK_CUSTOMERS tables only.' || CHR(10);
  END IF;

  v_final_prompt := v_final_prompt || CHR(10) ||
      'Support tickets:' || CHR(10) ||
      v_context || CHR(10) ||
      'User request:' || CHR(10) ||
      p_prompt;

  /* 3. Delegate to Select AI */
--  RETURN DBMS_CLOUD_AI.GENERATE(
--    prompt       => v_final_prompt,
--    profile_name => 'AI_WITH_SCHEMA',
--    action       => v_action
-- );
  RETURN DBMS_CLOUD_AI.GENERATE(
  prompt       => v_final_prompt,
  profile_name => CASE 
                    WHEN v_action = 'chat' THEN 'AI_CHAT_ONLY'
                    ELSE 'AI_WITH_SCHEMA'
                  END,
  action       => v_action
  );

END;
/

SELECT SELECT_AI_RAG(
  'Why are customers complaining about suspicious transactions?',
  'chat'
) FROM dual;

SELECT SELECT_AI_RAG(
  'What are the top 3 most critical issues reported by customers in the UK?',
  'narrate'
) FROM dual;

SELECT SELECT_AI_RAG(
  'Find 3 tickets related to suspicious activity.',
  'showsql'
) FROM dual;

SELECT SELECT_AI_RAG(
  'Find fraud-related tickets and explain how the query works.',
  'explainsql'
) FROM dual;

SELECT SELECT_AI_RAG(
  'List customers affected by unauthorized transactions.', -- it knows that it needs to look at the tickets of the customer for this!
  'runsql'
) FROM dual;