Scenario: Query IoT Data with Select AI in APEX

• Before You Begin
• Upload, Customize, and Run the Select AI Setup Script in APEX
• Query Examples
• Best Practices
• FAQ
• What's Next

You need these permissions to read IoT data and to use the Generative AI resource family.

Allow group <your-group-name> to read <iot-domain> in compartment <compartment-name>

allow dynamic-group <your-group-name> to manage generative-ai-family in compartment <compartment-name>

Administrators: If you're new to policies, to learn more about setting up policies, see How IAM Polices Work for Oracle Cloud Infrastructure. For specific examples, see Policy Details for the Internet of Things (IoT) Platform.

Confirm that you have:

• Configured access to the APEX workspace for the IoT database user.
• Required IAM policies so that you have permissions to query the target database or data source.

1. Copy this example SQL script and save the code snippet as a file to upload, for example: select_ai_apex_setup.sql
   The script builds an object_list for the IoT schema objects, including RAW_DATA, HISTORIZED_DATA, SNAPSHOT_DATA, DIGITAL_TWIN_MODELS, DIGITAL_TWIN_INSTANCES, and DIGITAL_TWIN_ADAPTERS.

   Example SQL script

   -- APEX SQL Commands setup script.
   -- Run this code block first in APEX SQL Commands or SQL Scripts.
   
   alter session set nls_language = 'ENGLISH';
   
   declare
     c_domain_short_id       constant varchar2(128)  := '<domain short id in upper case>';
     c_genai_user_ocid       constant varchar2(4000) := 'ocid1.user.oc1..unique';
     c_genai_tenancy_ocid    constant varchar2(4000) := 'ocid1.tenancy.oc1..unique';
     c_genai_fingerprint     constant varchar2(4000) := 'unique';
     c_genai_region          constant varchar2(128)  := 'region';
     c_genai_model           constant varchar2(4000) := 'xai.grok-4.20-reasoning';
     c_genai_credential_name constant varchar2(128)  := 'GENAI_CRED';
     c_genai_profile_name    constant varchar2(128)  := 'GENAI_XAI_GROK_420';
   
     c_private_key constant clob := q'~-----BEGIN PRIVATE KEY-----
   <private key>
   -----END PRIVATE KEY-----~';
   
     type t_name_list is table of varchar2(128);
     l_objects t_name_list := t_name_list(
       'RAW_DATA',
       'HISTORIZED_DATA',
       'REJECTED_DATA',
       'SNAPSHOT_DATA',
       'RAW_COMMAND_DATA',
       'DIGITAL_TWIN_MODEL_SPECS',
       'DIGITAL_TWIN_MODELS',
       'DIGITAL_TWIN_INSTANCES',
       'DIGITAL_TWIN_RELATIONSHIPS',
       'DIGITAL_TWIN_ADAPTERS'
     );
   
     l_iot_owner   varchar2(130);
     l_attributes  clob;
     l_object_list clob := '[';
   begin
     l_iot_owner := upper(trim(c_domain_short_id)) || '__IOT';
   
     begin
       dbms_cloud_ai.drop_profile(c_genai_profile_name);
     exception
       when others then
         null;
     end;
   
     begin
       dbms_cloud.drop_credential(c_genai_credential_name);
     exception
       when others then
         null;
     end;
   
     dbms_cloud.create_credential(
       credential_name => c_genai_credential_name,
       user_ocid       => c_genai_user_ocid,
       tenancy_ocid    => c_genai_tenancy_ocid,
       private_key     => c_private_key,
       fingerprint     => c_genai_fingerprint
     );
   
     for i in 1 .. l_objects.count loop
       if i > 1 then
         l_object_list := l_object_list || ',';
       end if;
       l_object_list := l_object_list ||
         '{"owner":"' || l_iot_owner || '","name":"' || l_objects(i) || '"}';
     end loop;
     l_object_list := l_object_list || ']';
   
     l_attributes := '{' ||
       '"provider":"oci",' ||
       '"credential_name":"' || c_genai_credential_name || '",' ||
       '"region":"' || c_genai_region || '",' ||
       '"oci_compartment_id":"' || c_genai_tenancy_ocid || '",' ||
       '"model":"' || c_genai_model || '",' ||
       '"annotations":false,' ||
       '"comments":true,' ||
       '"object_list":' || l_object_list ||
     '}';
   
     dbms_cloud_ai.create_profile(
       profile_name => c_genai_profile_name,
       attributes   => l_attributes
     );
   end;
   /

2. In APEX, select SQL Workshop, then select SQL Scripts.

3. Select Upload to add the select_ai_apex_setup.sql file to APEX.
4. Select Edit to update the script with variables for your environment.
   Replace these with the values for your environment:

   c_domain_short_id       constant varchar2(128)  := '<DOMAIN_SHORT_ID>';
   c_genai_region          constant varchar2(128)  := '<region>';
   c_genai_model           constant varchar2(4000) := 'xai.grok-4.20-reasoning';
   c_genai_profile_name    constant varchar2(128)  := 'GENAI_XAI_GROK_420';

   This script uses the GenAI service which uses these constants and "provider": "oci", if you want to use a different model, then you can update these values in the script, see Configure Select AI to Use Supported AI Providers.
5. Replace the select and prompt text with the question you want the AI to answer.

   Replace the profile_name with the AI profile you want to execute against.

   select 'GENAI_XAI_GROK_420' as profile_name,
          upper(trim('IN2AHR4KWKIRC')) || '__IOT' as iot_owner,
          'Setup complete' as status
   from dual;
   
   
   select 'which digital twin instances with the model spec uri dtmi:com:oracle:iot:sample:hvac:iot-domain-short-id' as prompt_text,
          dbms_lob.substr(
            replace(
              replace(
                dbms_cloud_ai.generate(
                  prompt       => 'which model spec uri has exactly two digital twin instances and both in active state',
                  profile_name => 'GENAI_XAI_GROK_420',

6. Select Run, and then select Run Now.
7. Select View, and then select Go scroll down to review the generated SQL based on the IoT question in the prompt.
8. Copy the generated SQL, select SQL Workshop, and then select SQL Commands.
9. Paste the generated SQL, and select Run to run the generated SQL.
10. For this prompt, the Results show the digital twin instance OCID, its display name, the temperature, and the humidity values.

Use these examples as starting prompts in APEX SQL Commands or SQLcl. In APEX, use the text inside the quotes as the prompt value.

Find the latest telemetry for a device

'Show the most recent telemetry readings for device <device-id>';

Find the hottest HVAC instance

'Which HVAC instance and model has the highest temperature in the last 1 hour?';

Find recent device errors

'Which devices reported errors in the last 24 hours?';

Summarize humidity by device

'Show average humidity by device for the last 7 days';

Find devices with no telemetry today, identifies devices that might be offline or misconfigured

'List devices that have not sent telemetry today';

Summarize abnormal factory readings

'Summarize abnormal readings for factory floor sensors this week';

Count historized data records

'How many historized data records are in the IoT domain?';

Count historized digital twin instances

'How many digital twin instances are in historized data?';

Find the busiest raw data instance

'What instance sent most of the raw data last day?';

Count adapters in the domain

'How many adapters are in the domain?';

Count instances in the domain

'How many instances are in the domain?';

Average instances per model

'What is the average number of instances that implement one model?';

Check for rejected data

'Is there rejected data in the domain?';

Raw data ingestion rate by hour

'What is the average raw data ingestion rate per hour last day, each hour?';

Raw record payload size

'What is the average and max raw record payload size?';

Historized record payload size

'What is the average and max historized record payload size?';

Temperature trend by hour

'Show the average temperature by hour for the last day';

Group device errors by type

'Show devices with error events in the last 7 days grouped by error type';

• Use showsql first so that you can review the generated SQL before it runs.
• Ask one IoT question at a time.
• Include the device type, metric name, model spec URI, or time range when it matters.
• Use separate profile names for separate models, for example OpenAI, GOOGLE_GEMINI, or CLAUDE.

• Configure a direct connection to the IoT domain database.
• Review the IoT domain database schema.
• Set up a connection to the IoT MCP server.