From Data to Dialogue: How to Make Your Oracle Database Tables Talk with APEX and SelectAI
Build an APEX Chat Bot with Autonomous Database using SELECT AI and OpenAI to Prompt your Structured Data in your Oracle Database.
Last year, Oracle announced an exciting new feature to add a ChatGPT-like interface on your structured data: Select AI. With SelectAI, you can execute SQL by asking natural questions in human language.
To explore this new feature in a more realistic way, we’ll create a mock flight tracking table for flights taking off from various airports and run SelectAI queries by asking the table some questions. We will build a no-code APEX Chat Bot to Converse with your Oracle Autonomous Database Tables using SelectAI and OpenAI.
So let’s jump into it!
Pre-Requisities
- A paid OpenAI account, including a production API key
- An OCI Account with an Autonomous Database
- Approximately 1 hour of fun time!
Solution
[1] Create an OpenAI Account and add an API Key
[2] Login to your Autonomous Database on OCI and create a new user and grant it privileges
-- Run with admin user --
CREATE USER ADB_USER IDENTIFIED BY "<your-password>" QUOTA UNLIMITED ON DATA;
-- ADD ROLES --
GRANT CONNECT TO ADB_USER;
GRANT CONSOLE_DEVELOPER TO ADB_USER;
GRANT DWROLE TO ADB_USER;
GRANT RESOURCE TO ADB_USER;
GRANT EXECUTE ON DBMS_CLOUD_AI to ADB_USER;
-- Add the ACL for OpenAI--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/[3] Assign new user ‘ADB_USER’ web access privileges from admin user
Database Actions > Database Users
Enable ‘Web Access’
[4] Create the Flight Tracking Table using the new ADB_USER
-- Run with ADB_USER User--
-- Add the OpenAI API Key --
EXEC DBMS_CLOUD.CREATE_CREDENTIAL('OPENAI_CRED', 'OPENAI', '<your-api-key>');
-- Create Sample Table --
CREATE TABLE ADB_USER.flight_tracking (
flight_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
flight_number VARCHAR2(10),
airline VARCHAR2(50),
departure_airport_code VARCHAR2(5),
arrival_airport_code VARCHAR2(5),
departure_date DATE,
arrival_date DATE,
status VARCHAR2(20) CHECK (status IN ('On Time', 'Delayed', 'Cancelled', 'Landed', 'In Flight'))
);
-- Create PL/SQL Procedure to seed the sample table --
CREATE OR REPLACE PROCEDURE ADB_USER.generate_random_flights AS
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO ADB_USER.flight_tracking (
flight_number,
airline,
departure_airport_code,
arrival_airport_code,
departure_date,
arrival_date,
status
) VALUES (
'FL' || LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1000, 9999))), 4, '0'), -- Random flight number
CASE TRUNC(DBMS_RANDOM.VALUE(1, 4)) -- Random airline
WHEN 1 THEN 'Airline A'
WHEN 2 THEN 'Airline B'
WHEN 3 THEN 'Airline C'
ELSE 'Airline D'
END,
CASE TRUNC(DBMS_RANDOM.VALUE(1, 5)) -- Random departure airport
WHEN 1 THEN 'JFK'
WHEN 2 THEN 'LAX'
WHEN 3 THEN 'ORD'
WHEN 4 THEN 'DFW'
ELSE 'SEA'
END,
CASE TRUNC(DBMS_RANDOM.VALUE(1, 5)) -- Random arrival airport
WHEN 1 THEN 'ATL'
WHEN 2 THEN 'CDG'
WHEN 3 THEN 'HND'
WHEN 4 THEN 'LHR'
ELSE 'DXB'
END,
SYSDATE + DBMS_RANDOM.VALUE(-1, 1), -- Random departure date within +/- 1 day of now
SYSDATE + DBMS_RANDOM.VALUE(1, 2), -- Random arrival date within the next 1 to 2 days
CASE TRUNC(DBMS_RANDOM.VALUE(1, 6)) -- Random status
WHEN 1 THEN 'On Time'
WHEN 2 THEN 'Delayed'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'Landed'
WHEN 5 THEN 'In Flight'
ELSE 'Boarding'
END
);
END LOOP;
COMMIT;
END generate_random_flights;
/
-- Seed the Sample Table --
BEGIN
adb_user.generate_random_flights;
END;
/
--Check the sample records--
select * from adb_user.FLIGHT_TRACKING;[5] Add the AI Profile for your OpenAI credentials with ADB_USER
-- Create OpenAI profile--
BEGIN
DBMS_CLOUD_AI.create_profile(
'OPENAI',
'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "ADB_USER", "name": "flight_tracking"}]
}');
END;
/
-- To Drop the profile --
-- BEGIN
-- DBMS_CLOUD_AI.DROP_PROFILE(profile_name => 'OPENAI');
-- END;
-- /
[6] Now the Fun Part to Ask Questions to your Table :)
-- Set the Profile First--
EXEC DBMS_CLOUD_AI.set_profile('OPENAI');
-- Generate SQL Command--
-----------------------------------------------
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'Can you give me a list of all flights from LAX',
profile_name => 'OPENAI ',
action => 'showsql')
FROM dual;
-- Run SQL Directly--
-----------------------------------------------
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'Can you give me a list of all flights from LAX',
profile_name => 'OPENAI ',
action => 'runsql')
FROM dual;
--Get a Description by asking question in natural language--
-----------------------------------------------
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'Can you give me a list of all flights from LAX',
profile_name => 'OPENAI ',
action => 'narrate')
FROM dual;
-----------------------------------------------
--Chat with your data--
-----------------------------------------------
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'Can you give me a list of all flights from LAX',
profile_name => 'OPENAI ',
action => 'chat')
FROM dual;
-----------------------------------------------[7] Let’s take it one-step further and create an APEX App to front the new SelectAI capabilities
Note : The app is based on ChatDB app presented at Oracle Cloud World 23. For more on the ChatDB app, you can read this blog : https://blogs.oracle.com/apex/post/configuring-generative-ai-in-oracle-apex-using-select-ai
Create an APEX workspace for adb_user in ADB-S
Create workspace ‘adb_user’ on schema ’adb_user’
Sign-in to adb_user workspace and download the app from here : chat-db.sql and import it
Link: https://github.com/shadabshaukat/Oracle-Apex-Sample-Apps
In case there’s an issue creating the objects, you can use the below ddl to create the objects and then run the application:
-- drop table "OCW23_CONVERSATIONS"
CREATE TABLE "OCW23_CONVERSATIONS"
(
"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"SUMMARY" VARCHAR2(4000 CHAR),
"USERNAME" VARCHAR2(100 CHAR),
"STARTED_ON" TIMESTAMP (6),
CONSTRAINT "CONVERSATIONS_ID_PK" PRIMARY KEY ("ID")
USING INDEX ENABLE
);
-- drop table "OCW23_PROMPTS"
CREATE TABLE "OCW23_PROMPTS"
(
"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
MINVALUE 1
MAXVALUE 9999999999999999999999999999
INCREMENT BY 1
START WITH 1
CACHE 20
NOORDER
NOCYCLE
NOT NULL ENABLE,
"CONV_ID" NUMBER NOT NULL ENABLE,
"PROMPT" VARCHAR2(4000 CHAR),
"RESPONSE" CLOB,
"ASKED_ON" TIMESTAMP (6),
"SHOWSQL" VARCHAR2(4000),
CONSTRAINT "PROMPTS_ID_PK" PRIMARY KEY ("ID")
);
ALTER TABLE "OCW23_PROMPTS"
ADD CONSTRAINT "PROMPTS_CONV_ID_FK"
FOREIGN KEY ("CONV_ID")
REFERENCES "OCW23_CONVERSATIONS" ("ID")
ON DELETE CASCADE ENABLE;
CREATE INDEX "PROMPTS_I1" ON "OCW23_PROMPTS" ("CONV_ID");Testing SelectAI
Run the Application, login and select profile ‘OPENAI’
Select the profile “OPENAI” (or “COHERE” if you’re using Cohere model)
Tick “Ask Database” option and let’s ask some questions in natural language to the Autonomous DB on our flight tracking data
Question 1 : Can you give me the total count of all flights which have taken-off from LAX Airport this year
It gives us the count by running a SQL query against the database.
Click Explain to see the generated SQL and its explanation.
Our app diplays and explains the SQL query.
Now, let’s make it a little tricky.
Question 2 : How many flights take off daily from all airports, give me the total with a breakdown of days and also the name of the airline
Surprisingly accurate results, even though the question was very vague.
Let’s explain the SQL.
It generated very accurate SQL and executed it based on our prompt. You can continue asking questions to the database. Or, if you just want to chat to the GPT model without going to the database, uncheck the ‘Ask Database’ option.
We were able to build a no-code app to talk to our Autonomous Database in less than an hour.
In future blog posts, we’ll explore how to use SelectAI with Cohere command models and other models on the upcoming OCI GenAI Service
