AI for Aviation - Create Realtime Passenger Dashboard with Transactional Event Queues, Advanced Queuing, Scheduler Jobs, Queue Management & GenAI
Introduction
AI for Aviation. In this article, we will see how Oracle Advanced Queuing and Scheduling Jobs with Oracle Scheduler can help in process automation and smooth run aviation passenger traffic into the airport till passengers board a flight. There are multiple queues such as Immigration check & passport stamping, baggage check-in, security check, etc., and many priorities such as regular passengers, VIP and VVIP passengers, and airport lounge users. Multiple loosely coupled application systems which work on publish & subscribe messaging systems and a complex set of business rules governing the Aviation industry. Festivals and Holiday seasons will just add to managing this complexity.
We will create a real-time passenger dashboard that monitors each of these queues and helps smooth the passenger journey. We will also leverage the power of Oracle Generative AI to help passengers know about their destination and places to visit once they reach their destination.
Transactional Event Queues and Advanced Queuing are robust and feature-rich message queuing systems integrated with Oracle database. When web, mobile, IoT, and other data-driven and event-driven applications stream events, or communicate with each other as part of a workflow, producer applications enqueue messages and consumer applications dequeue messages.
Please check the more information section of this article to read details about various technologies, such as Transactional Event Queues, Advanced Queueing, Scheduler Jobs, and Generative AI.
Table of Contents:
Single Consumer Queue
A single-consumer queue is created based on a single consumer queue table. Messages enqueued in a single-consumer queue can be dequeued by only a single consumer.
Multi Consumer Queue
A multiple-consumer queue is based on a multiple-consumer queue table. This queue supports queue subscribers and message recipients.
01. Aviation - Boarding a flight Use case
Use Case: On any given day or night, we see passengers entering the airport; the first thing that happens when we enter the airport is to stand in the "Airport security check" queue. We need to present our reservation details and ID card. After the security check, we are inside the airport.
Once we are inside the airport, we need to deposit our baggage and collect our boarding pass. This will also have a different queue.
Once we have our boarding pass, we need to have our Immigration and passports verified and stamped.
Then, there is a final security check, and the person will wait to board the plane. Those passengers with special privileges or priority passes will be in a VIP Lounge.
At a suitable time, we must stand again in the boarding flight queue just before entering the flight. Most people need to stand, while a few senior citizens or people with health issues might get a wheelchair with shorter queues.
As we can see, there are many people and many queues, and every verification is essential to move to the next level.
To make this use case a little more complex, all passengers need not be part of the same queue. The passengers are classified broadly into the following areas: Lounge Access, Meet and Greet Services, Priority Security Lines, Fast Track Services, or Private Transportation.
So, a person with VVIP privileges might get a shorter queue than a regular passenger. This may vary from country to country. Also, during a festival or holiday season, there are simply more passengers in and out of the system (from airport entry to boarding flight).
Most often, many agencies and applications handle this entire process in a publish and subscribe messaging platform, depending on a single system, which creates a technical bottleneck and is non-practical to manage.
Let's see how we can build a system around it and simulate real-time data.
Airport Checkin
ID Verification: Upon reaching the airport, you need to show your unique identification documents, such as your passport, driving license, Aaadhar card in India, or any such document as per your country of boarding flight. Officials at the counter will check the documents.
Queue (Airport_checkin_queue): This is an example of a single consumer queue
Get Boarding Pass and Baggage Service
Queue (boarding_pass_and_baggage_service): This is an example of a multi-consumer queue, as this is one of the few other things which are required to onboard an international or domestic flight
Immigration, Visa and Customs Check
Queue (immigration_service): This is an example of a multi-consumer queue, as this is one of the few other things which are required to onboard an international or domestic flight
Security Check Queue
Queue (security_service): This is an example of a multi-consumer queue, as this is one of the few other things which are required to onboard an international or domestic flight
Departures Queue
Wait for Boarding: After arriving at the final gate, wait in the boarding area. You board the plane once the final check of your boarding pass is done. Settle in, put on your seat belt and listen carefully to the instructions.
Queue (departures_queue): This is an example of a single-consumer queue, where no activity is required to run in parallel.
02. Grant the required privileges and create DB Type
Let us see how do we technical create and simulate this system using various technologies that are available
Log in to Oracle Autonomous Database > Database Actions > SQL as an ADMIN user and run the following SQL commands. I am assuming that you are running Oracle APEX as DOCUSER, but this can be any other non-ADMIN user that you have already created.
--login as ADMIN user
GRANT EXECUTE ON DBMS_AQADM TO DOCUSER;
GRANT EXECUTE ON DBMS_AQ TO DOCUSER;
GRANT AQ_ADMINISTRATOR_ROLE TO DOCUSER;
EXECUTE dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','DOCUSER',FALSE);
EXECUTE dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','DOCUSER',FALSE);
If you come across an insufficient privileges message, please check this document
The CREATE TYPE statement specifies the name of the type and its attributes, methods, and other properties.
To start with we need to create a Database Type , we will name it as passenger_q_payload, replace <payload-type-name> with any name of your choice.
You can use Oracle APEX SQL Workshop to run these or directly use SQL under Oracle Database Actions of Oracle Autonomous Database Instance
CREATE TYPE <payload-type-name> AS OBJECT ( message VARCHAR2(4000) );
For example - we will be using passenger_q_payload in rest of this article
CREATE TYPE passenger_q_payload AS OBJECT ( message VARCHAR2(4000) );
03. Create a Single-Consumer Queue
Let us create a Queue by name "Airport_checkin_queue" and a corresponding table by name "Airport_checkin_queue_table" and let this be a single Single Consumer Queue. That is to say message in the Queue will disappear once it has been Dequeued or consumed by any consumer.
General PL/SQL for Creating a Queue Table, Queue and Start the Queue is shown below
DECLARE
v_qn varchar2(100) := 'Airport_checkin_queue';
v_qtab varchar2(100) := 'Airport_checkin_queue_table';
v_qcomm varchar2(1000) := 'Queue for Passenger Airport Checkin';
v_qtype_bool boolean := false;
BEGIN
-- Create Queue Table --------------------------
DBMS_AQADM.create_queue_table (
queue_table => v_qtab,
queue_payload_type => 'passenger_q_payload',
multiple_consumers => v_qtype_bool,
comment => v_qcomm,
secure => false);
-- Create Queue -----------------------------------
DBMS_AQADM.create_queue (queue_name => v_qn,
queue_table => v_qtab);
-- Start the Queue ----------------------------------
DBMS_AQADM.start_queue (queue_name => v_qn);
END;
Prioritised Messages
Priority and ordering of messages in enqueuing: It is possible to specify the priority of the enqueued message. An enqueued message can also have its exact position in the queue specified. This means that users have three options to specify the order in which messages are consumed:
(a) a sort order specifies which properties are used to order all message in a queue;
(b) a priority can be assigned to each message;
(c) a sequence deviation allows you to position a message in relation to other messages.
Further, if several consumers act on the same queue, a consumer will get the first message that is available for immediate consumption. A message that is in the process of being consumed by another consumer will be skipped.
Messages can be Prioritised by adding the sort list option as shown below by adding sort_list, rather than everything appearing in a random order.
DECLARE
v_qn varchar2(100) := :P1_QUEUE_NAME;
v_qtab varchar2(100) := :P1_QT;
v_qcomm varchar2(1000) := :P1_COMMENT;
v_qtype varchar2(100) := :P1_TYPE;
v_qtype_bool boolean := false;
BEGIN
if v_qtype = 'Multi' then
v_qtype_bool := true;
end if;
DBMS_AQADM.create_queue_table (
queue_table => v_qtab,
queue_payload_type => 'passenger_q_payload',
sort_list => 'PRIORITY,ENQ_TIME',
multiple_consumers => v_qtype_bool,
comment => v_qcomm,
secure => false);
DBMS_AQADM.create_queue (queue_name => v_qn,
queue_table => v_qtab);
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => v_qn);
END;
You could login to SQL under Database Actions menu as DOCUSER (Our Database User) and run the SQL or you could open SQL Commands under Oracle APEX and Run the above SQL as shown below
As a best practice, create a re-usable user interface to create and manage queues, as shown in the Oracle APEX application page. This helps us develop more queues and better manage the messaging system. However this is Optional.
And the Dynamic Action PL/SQL server side code is as shown below
DECLARE
v_qn varchar2(100) := :P1_QUEUE_NAME;
v_qtab varchar2(100) := :P1_QT;
v_qcomm varchar2(1000) := :P1_COMMENT;
v_qtype varchar2(100) := :P1_TYPE;
v_qtype_bool boolean := false;
BEGIN
if v_qtype = 'Multi' then
v_qtype_bool := true;
end if;
DBMS_AQADM.create_queue_table (
queue_table => v_qtab,
queue_payload_type => 'passenger_q_payload',
multiple_consumers => v_qtype_bool,
comment => v_qcomm,
secure => false);
DBMS_AQADM.create_queue (queue_name => v_qn,
queue_table => v_qtab);
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => v_qn);
END;
Let us now create one more Queue (departures_queue) using APEX user interface. Alternatively you can also use SQL Worksheet to run the PL/SQL and create this queue
Reality check - if the Queue has been created
SELECT * FROM USER_QUEUES where QUEUE_TYPE='NORMAL_QUEUE'
-- or use the below SQL ---
SELECT NAME, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED, RECIPIENTS FROM USER_QUEUES where QUEUE_TYPE='NORMAL_QUEUE'
04. Create a Multi-Consumer Queue
We will now create a Multi Consumer Queue named "Passenger_OnBoarding_Queue"
This will use "passenger_q_payload" as our custom payload for Queue messages.
DECLARE
v_qn varchar2(100) := 'Passenger_OnBoarding_Queue';
v_qtab varchar2(100) := 'Passenger_OnBoarding_Queue_Table';
v_qcomm varchar2(1000) := 'Passenger OnBoarding Queue';
v_qtype_bool boolean := true;
BEGIN
-- Create Queue Table --------------------------
DBMS_AQADM.create_queue_table (
queue_table => v_qtab,
queue_payload_type => 'passenger_q_payload',
multiple_consumers => v_qtype_bool,
comment => v_qcomm,
secure => false);
-- Create Queue -----------------------------------
DBMS_AQADM.create_queue (queue_name => v_qn,
queue_table => v_qtab);
-- Start the Queue ----------------------------------
DBMS_AQADM.start_queue (queue_name => v_qn);
END;
If we plan to do this using Oracle APEX front End
Reality Check -
SELECT NAME, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED, RECIPIENTS FROM USER_QUEUES where QUEUE_TYPE='NORMAL_QUEUE'
05. Create Subscribers for Multi-Consumer Queue
We will now create few subscribers to our "Passenger_OnBoarding_Queue" Queue, they are
This will use "passenger_q_payload" as our custom payload for Queue messages.
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER
(queue_name => 'Passenger_OnBoarding_Service',
subscriber => SYS.AQ$_AGENT('Boarding_Pass_and_Baggage_Service', 'Passenger OnBoarding Service', NULL));
END;
If we plan to create an Oracle APEX user interface, create one by selecting a list of Queues (In Oracle APEX, Select List Page Item > Type SQL query)
SELECT NAME as d, NAME as v FROM USER_QUEUES
where QUEUE_TYPE='NORMAL_QUEUE' AND RECIPIENTS = 'MULTIPLE'
The button click will have a dynamic action
DECLARE
v_subs_name varchar2(100) := :P1_SUBS_NAME;
v_q_name varchar2(100) := :P1_MULTIPLE_R2;
BEGIN
--P1_SUBS_NAME,P1_MULTIPLE_R2
DBMS_AQADM.ADD_SUBSCRIBER
(queue_name => v_q_name,
subscriber => SYS.AQ$_AGENT(v_subs_name, v_q_name,NULL));
END;
So the Oracle APEX Page to create subscriber looks as shown.
Similarly we will create few more Subscribers "Immigration_Service", "Security_Service" subscribed to our "Passenger_Onboarding_Queue"
06. List Subscribers for Multi-Consumer Queue
List the Queue Names
SELECT CONCAT(name, ' (', recipients , ')') AS d, NAME as v FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE' order by recipients
--- or --
SELECT NAME, RECIPIENTS FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE' order by RECIPIENTS
and Display the List of Consumers
The Interactive Grid on the right side works on Select List Change Listener Event, and the corresponding Query is
-- P1_Q_NAME is the Selected Queue Name
select distinct CONSUMER_NAME from USER_QUEUE_SUBSCRIBERS where queue_name = :P1_Q_NAME
07. Send Message to a Queue
let's see how to send a message to a single consumer queue. we will take an example of customer entering airport initial security check with passport number and a verification id card.
DECLARE
l_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
l_message_handle RAW (16);
l_queue_msg passenger_q_payload;
v_dbusername varchar2(100) := V('DB_USERNAME') ;
l_msg varchar2(400) := 'Passport: M1234567 Name: Anthony Xavier';
----- l_msg varchar2(400) := :P1_MESSAGE;
l_q_name varchar2(400) := 'DOCUSER.Airport_Checkin_Queue';
---- l_q_name varchar2(400) := v_dbusername||'.'||:P1_Q_NAME;
BEGIN
l_queue_msg := passenger_q_payload (l_msg);
DBMS_AQ.ENQUEUE (queue_name => l_q_name,
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN DBMS_OUTPUT.put_line ( SQLERRM || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
The corresponding Oracle APEX UI would look like as shown below. Here we are manually simulating placing an order in Queue.
08. View Messages in a Queue
View the Message pipeline in a Queue, replace <Queue-Table-Name>
SELECT QUEUE, MSG_ID, MSG_STATE, ENQ_TIMESTAMP, DEQ_TIMESTAMP FROM AQ$<Queue-Table-Name>
-- For example to Message pipeline in ORDERS QUEUE TABLE
SELECT QUEUE, MSG_ID, MSG_STATE, ENQ_TIMESTAMP, DEQ_TIMESTAMP FROM AQ$AIRPORT_CHECKIN_QUEUE_TABLE
View in Oracle APEX UI
09. Receive Message in a Single-Consumer Queue (Dequeue)
A Message consumer needs to Dequeue a message to read it. Once it has been read or dequeued, it will no longer be available in the Queue.
In the Dequeue operation, pass the Queue Name and this will response back with Message in that Queue as shown below. this message can be assigned to a variable (:P1_SINGLE_R_MSG) to display on the Oracle APEX UI.
DECLARE
r_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle RAW(16);
o_payload passenger_q_payload;
v_dbusername varchar2(100) := V('DB_USERNAME') ;
v_qname varchar2(100) := v_dbusername||'.'||:P1_SINGLE_R;
BEGIN
DBMS_AQ.DEQUEUE(
queue_name => v_qname,
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
:P1_SINGLE_R_MSG := '*** DEQUEUE message => ' || o_payload.message || ' ';
COMMIT;
END;
10. Send Message to a Multi-Consumer Queue
Sending message to Multi-Consumer Queue is same as sending message to Single Consumer Queue, Only DeQueue differs and the message will stay in Queue till all the subscribers have Dequeued (or read the message)
You can view the Queue messages with the below query, replace Queue table name if you have given a different name
11. View Messages in a Queue
-- For Multi Consumer Queue
SELECT QUEUE, MSG_ID, MSG_STATE, ENQ_TIMESTAMP, DEQ_TIMESTAMP, CONSUMER_NAME FROM AQ$<Queue-Table-Name> order by MSG_ID
-- For Single Consumer Queue
SELECT QUEUE, MSG_ID, MSG_STATE, ENQ_TIMESTAMP, DEQ_TIMESTAMP FROM AQ$<Queue-Table-Name> order by MSG_ID
-- for example
SELECT QUEUE, MSG_ID, MSG_STATE, ENQ_TIMESTAMP, DEQ_TIMESTAMP, CONSUMER_NAME FROM AQ$PASSENGER_ONBOARDING_QUEUE_TABLE order by MSG_ID
View Queue and Consumer names in Oracle APEX
12. Receive (Dequeue) Message in a Multi-Consumer Queue
To DeQueue a Multi-Consumer Queue, we would need to Pass the consumer name and also same Consumer cannot Dequeue same message multiple times.
Corresponding PL/SQL in Oracle APEX (Dynamic Action) is:
-- P1_SUBS_NAME is the name of Queue Subscriber
-- P1_MULTIPLE_R2 is the name of Queue
DECLARE
v_subs_name varchar2(100) := :P1_SUBS_NAME;
v_q_name varchar2(100) := :P1_MULTIPLE_R2;
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER
(queue_name => v_q_name,
subscriber => SYS.AQ$_AGENT(v_subs_name, v_q_name,NULL));
END;
Since this passenger Jane Smith, has gone through all the 3 required services, you can see message state as "PROCESSED", even if any one of the service has not yet been triggered we can see the status as "READY"
13. Clear Queue
To View number of messages in a Queue
select count(*) from <Queue-Table-Name>
-- for example
select count(*) from PASSENGER_ONBOARDING_QUEUE_TABLE
To Clear the Queue
-- Replace <Queue-Table> with your Queue Table name
DECLARE
purge_opt dbms_aqadm.aq$_purge_options_t;
q_table varchar2(100) := :<Queue-Table>;
BEGIN
dbms_aqadm.purge_queue_table(q_table, NULL, purge_opt);
END;
14. Delete Queue
To purge table and delete a Queue
DECLARE
purge_opt dbms_aqadm.aq$_purge_options_t;
q_table varchar2(100) := :P1_SELECTED_Q;
BEGIN
-- Purge table ----
dbms_aqadm.PURGE_QUEUE_TABLE(q_table, NULL, purge_opt);
-- drop q table
DBMS_AQADM.DROP_QUEUE_TABLE( queue_table => q_table, force => TRUE );
END;
15. Scheduling Jobs with Oracle Scheduler
Sometimes, you can generate random data to test various scenarios, for example, passengers entering the airport and going to multiple destinations.
Let us see how we can create a simple scheduler job that runs at a specific time interval.
You must have the CREATE JOB privilege to create a job in your own schema, and the CREATE ANY JOB privilege to create a job in any schema except SYS.
Create a Test Table and a Procedure to Insert a sample record
create table TEST_TBL1 (
id number generated by default on null as identity
constraint test_tbl1_id_pk primary key,
update_date date
);
create or replace procedure TEST_TBL1_PROC is
begin
INSERT INTO TEST_TBL1 (update_date) values (sysdate);
commit;
end;
Create a Scheduler Job that runs at a frequency of every 2 seconds
begin
dbms_scheduler.create_job (
job_name => 'My_Test_Table_Job',
job_type => 'STORED_PROCEDURE',
job_action => 'TEST_TBL1_PROC',
start_date => systimestamp,
repeat_interval => 'freq=secondly; interval = 2;',
enabled => true,
comments => 'My new job'
);
end;
Run the Job and periodically check the Job status
begin
DBMS_SCHEDULER.RUN_JOB('My_Test_Table_Job');
end;
select count(*) from TEST_TBL1;
Disable the Job and Drop the table
Begin
dbms_scheduler.disable('My_Test_Table_Job',TRUE );
End;
Begin
dbms_scheduler.drop_job(job_name => 'My_Test_Table_Job');
End;
drop table TEST_TBL1;
Alternatively, Use Job Scheduler user interface in Oracle Database Studio
16. AI for Aviation Real Time Passenger Dashboard
Now that we have gone through how to create a queue and Jobs, in this section, we will see how to generate a random passenger list and move the passengers in the security check queue. We will also exit the passengers based on priority and clear the Queue, as per the use case that we listed earlier in the article.
Create Passenger Table
create table PASSENGER_LIST (
id number generated by default on null as identity
constraint passenger_list_id_pk primary key,
PASSENGER_NAME varchar2(240 char),
PASSPORT_NUMBER varchar2(240 char),
SOURCE_AIRPORT_CODE varchar2(240 char),
DESTINATION_AIRPORT_CODE varchar2(240 char),
FLIGHT_ID varchar2(240 char),
DESTINATION varchar2(240 char),
MSG_ID varchar2(240 char),
ENTER_DATE date,
EXIT_DATE date,
-- Any other columns as required --
BOOKING_ID varchar2(240 char)
);
Create a PL/SQL Procedure to Generate Random passenger list
create or replace PROCEDURE GeneratePassengerDetails
AS
v_passenger_name varchar2(200);
v_passport_number varchar2(20);
v_destination varchar2(100);
v_priority varchar2(50);
v_NAME_EN UNESCO_SITES.NAME_EN%TYPE;
v_STATES_NAME_EN UNESCO_SITES.STATES_NAME_EN%TYPE;
v_clob clob;
l_q_msg varchar2(200);
v_priority_int number;
ret number;
BEGIN
select name into v_passenger_name from (select name from RBANK_CUSTOMERS where country_id = 'US' ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum < 2;
v_passport_number := dbms_random.string('x',10);
select NAME_EN, STATES_NAME_EN into v_NAME_EN, v_STATES_NAME_EN
from (select NAME_EN, STATES_NAME_EN from UNESCO_SITES where STATES_NAME_EN = 'United States of America' ORDER BY DBMS_RANDOM.RANDOM)
WHERE rownum < 2;
SELECT
CASE round(dbms_random.value(1,4))
WHEN 1 THEN 'Priority Security Lines'
WHEN 2 THEN 'Fast Track Services'
WHEN 3 THEN 'Lounge Access'
WHEN 4 THEN 'Meet and Greet Services'
WHEN 5 THEN 'Private Transportation' END
AS priority into v_priority FROM dual;
v_destination := v_NAME_EN||','||v_states_name_en;
Insert into passenger_list (PASSENGER_NAME, PASSPORT_NUMBER , DESTINATION, PRIORITY ) values (v_passenger_name, v_passport_number, v_destination, v_priority );
l_q_msg := 'Name: '||v_passenger_name ||' Passport No: '||v_passport_number;
if v_priority = 'Priority Security Lines' then
v_priority_int := 1;
elsif v_priority = 'Fast Track Services' then
v_priority_int := 2;
elsif v_priority = 'Lounge Access' then
v_priority_int := 3;
elsif v_priority = 'Meet and Greet Services' then
v_priority_int := 4;
elsif v_priority = 'Private Transportation' then
v_priority_int := 5;
end if;
-- Send msg and priority to Queue
ret := msgtoqueue (l_q_msg, v_priority_int);
END;
Create a Job that runs every 5 seconds
Simulation of passengers entering airport every five seconds and getting into Airport security queue.
begin
dbms_scheduler.create_job (
job_name => 'Generate_Passenger_Details_Job',
job_type => 'STORED_PROCEDURE',
job_action => 'GeneratePassengerDetails',
start_date => systimestamp,
repeat_interval => 'freq=SECONDLY; interval = 5;',
enabled => true,
comments => 'Generate Passenger Details'
);
end;
Create a PL/SQL Procedure that writes message to Queue
Everytime a new passenger enters Airport security checkin area we want to move that passenger to queue (EnQueue operation) once they clear we want to DeQueue - so that our Queue is cleared.
Real time dashboard will keep track of passengers in and out of security check queue
create or replace function msgtoqueue ( l_msg IN varchar2, l_priority IN number ) RETURN number
IS
l_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
l_message_handle RAW (16);
l_queue_msg passenger_q_payload;
l_q_name varchar2(400) := 'DEMOUSER.Airport_Checkin_Queue';
BEGIN
l_queue_msg := passenger_q_payload (l_msg);
l_message_properties.priority := l_priority;
DBMS_AQ.ENQUEUE (queue_name => l_q_name,
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN DBMS_OUTPUT.put_line ( SQLERRM || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/
Usually, passengers enter one of these queue areas, such as Lounge Access, Meet and Greet Services, Priority Security Lines, Fast Track Services, or Private Transportation. This may vary from country to country.
We can monitor the passengers in a dashboard chart as shown below.
Chart Query
-- Passengers who entered Airport security check area
select count(*) as Passenger_In, 'A_Entered_Security_Check' from Passenger_list
-- Passengers who are still in line waiting
select count(*) as Passenger_In, 'B_Pending_Sec_Check' from AQ$AIRPORT_CHECKIN_QUEUE_TABLE
-- Passengers who have cleared the security check
select ((select count(*) from passenger_list) - (select count(*) from AQ$AIRPORT_CHECKIN_QUEUE_TABLE)) as Pending, 'Cleared_Sec_Check' from dual
17. Know about the destination places using Generative AI
Generative AI will take the input as Place name and provide the related information and places to visit around it, please check the PL/SQL to generate AI response
What is the distance between Carlsbad Caverns National Park and statue of liberty by plane and time taken
AI Response: The distance between Carlsbad Caverns National Park and the Statue of Liberty is approximately 1,190 miles if traveled by air.
A direct flight from Carlsbad Caverns National Park to the Statue of Liberty would likely take around 3 hours and 30 minutes to 4 hours, depending on weather conditions and flight routes. However, it's important to note that there are no direct flights between these two locations. You would need to take a connecting flight, which could extend the travel time significantly.
Keep in mind that this estimate assumes that the Statue of Liberty is accessible by plane, which it is not. This estimate is solely based on the distance between the two landmarks and does not account for the actual feasibility of the trip.
About Statue of Liberty and places to visit nearby
AI Response: The Statue of Liberty, officially titled "Statue of Liberty Enlightening the World", is a well-known monument located in New York City, symbolizing freedom and democracy. This iconic statue, a gift from France to the United States, stands on Liberty Island in the middle of Upper New York Bay, offering stunning views to visitors.
Some places to visit near the Statue of Liberty:
Liberty Island and Ellis Island: Liberty Island is home to the Statue of Liberty, and nearby Ellis Island was the nation's busiest immigration station from 1892 to 1954. Visitors can explore the museums on both islands and learn about their rich history.
Manhattan: When visiting the Statue of Liberty, you'll likely pass through Manhattan, home to iconic landmarks like Times Square, Central Park, Rockefeller Center, and Broadway. Take a walk through the bustling city streets, enjoy a show, or indulge in the famous cuisine.
Brooklyn Bridge and Brooklyn Heights: Across the East River from Manhattan, the iconic Brooklyn Bridge offers a breathtaking view. Visit the Brooklyn Heights Promenade for a scenic outlook of the bridge and the Manhattan skyline. The nearby Brooklyn Museum and Brooklyn Botanic Garden are also worth exploring.
One World Trade Center: Located in the Financial District of Manhattan, this 104-story skyscraper is a significant landmark. The observatory on the 100th floor, called One World Observatory, provides panoramic views of the city.
The High Line: This former elevated freight railroad track is now a public park in Manhattan, offering a unique perspective on the city. The High Line is known for its interesting art installations and beautiful greenery.
Central Park Zoo: Located within Central Park, this zoo is a fun attraction for families and animal lovers. It's a great place to visit if you want to see a variety of wildlife up close.
SoHo and Tribeca: These popular Manhattan neighborhoods are known for their art galleries, trendy shops, and restaurants. Take a stroll through their cobblestone streets and explore the art scene.
Empire State Building: The famous Empire State Building offers breathtaking views from its observation deck. Visiting this iconic skyscraper is an essential New York experience.
The Metropolitan Museum of Art: Also known as the Met, this world-renowned art museum in Manhattan houses a vast collection of art pieces from around the world. It's a must-visit destination
DECLARE
l_genai_rest_url VARCHAR2(4000) := 'https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat';
l_web_cred CONSTANT VARCHAR2(50) := 'Ind_OCI_WebCred';
l_input varchar2(4000) := :P2_INPUT;
l_response_json CLOB;
l_text varchar2(4000);
l_ocigabody varchar2(32000) := '
{
"compartmentId": "ocid1.compartment.oc1..aaaaaaaaud6tkdn6n23cbvc4hexs6n4hggetkwo4viqyneyroixcmj54u32q",
"servingMode": {
"servingType": "ON_DEMAND",
"modelId": "cohere.command-r-16k"
},
"chatRequest": {
"message": "'||l_input||'",
"maxTokens": 500,
"isStream": false,
"apiFormat": "COHERE",
"temperature": 0.75,
"frequencyPenalty": 1,
"presencePenalty": 0,
"topP": 0.7,
"topK": 1
}
}
';
CURSOR C1 IS
SELECT jt.*
FROM JSON_TABLE(l_response_json, '$' COLUMNS (text VARCHAR2(32000) PATH '$.chatResponse[0].text' )) jt;
BEGIN
l_input := 'About '||l_input||' and What are the places to visit around '||l_input;
if l_input is not null then
apex_web_service.g_request_headers.DELETE;
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
l_response_json := apex_web_service.make_rest_request
(p_url => l_genai_rest_url,
p_http_method => 'POST',
p_body => l_ocigabody,
p_credential_static_id => l_web_cred);
For row_1 In C1 Loop
l_text := row_1.text;
:P2_OUTPUT := l_text;
End Loop;
end if;
END;
To know more about working with Oracle Generative - please check these articles
18. Demo Video
Downloads
Bon Voyage :-)
Thanks for reading, liking and sharing
Regards, Madhusudhan Rao
More information section:
What Is Queuing?
Transactional Event Queue and Advanced Queuing stores user messages in abstract storage units called queues. When web, mobile, IoT, and other data-driven and event-driven applications stream events or communicate with each other as part of a workflow, producer applications enqueue messages and consumer applications dequeue messages.
At the most basic level of queuing, one producer enqueues one or more messages into one queue. Each message is dequeued and processed once by one of the consumers. A message stays in the queue until a consumer dequeues it or the message expires. A producer can stipulate a delay before the message is available to be consumed, and a time after which the message expires. Likewise, a consumer can wait when trying to dequeue a message if no message were available. An agent program or application could act as both a producer and a consumer.
Producers can enqueue messages in any sequence. Messages are not necessarily dequeued in the order in which they are enqueued. Messages can be enqueued without being dequeued.
At a slightly higher level of complexity, many producers enqueue messages into a queue, all of which are processed by one consumer. Or many producers enqueue messages, each message being processed by a different consumer depending on type and correlation identifier.
Enqueued messages are said to be propagated when they are reproduced on another queue, which can be in the same database or in a remote database.
Principal Oracle CX/APEX/AI Consultant, Co-founder of the FACES Digital Experience Platform, and Fujitsu Distinguished Engineer
4 天前This is a great example of the power of the Oracle Database ?? great post Madhusudhan Rao. Would love to see DB queue functionality added to Oracle APEX natively as a low code declarative option