Some of the chapters in the Oracle SOA Suite 12c Handbook include code examples that use the Database Adapter and require some preparation. This article helps you perform the necessary steps.
To make things easy, we will create all database objects in a single database schema – even though logically the SAIBOT AIRPORT database is more complex than that. Note that an Oracle XE database will suffice for the samples in the book – and is therefore probably preferable given its small footprint.
Using a database account with DBA privileges – such as SYSTEM OR SYS when using a local database – create a new user with a number of privileges using the following statements:
create user saibot identified by saibot default tablespace users temporary tablespace temp; alter user saibot quota unlimited on users ; grant connect, create table, create procedure, create sequence, create type to saibot;
Chapter 3 requires the creation of a number of tables – for AIRPORTS and COUNTRIES – with a minimum of prepopulated records; these tables can be created using this script.
Slightly more complex is the creation of a PL/SQL package FUT_SLOT_API and a number of types and tables. These objects are logically part of the Future data domain with details about slots and flights that are planned to take place. The package and its supporting types and tables can be created using this script.
In this chapter, Advanced Queuing is used. The AQ configuration can be done in the saibot schema introduced overhead or in a new schema. This new schema can be created using the following statements, by a user with DBA privileges – such as SYSTEM OR SYS when using a local database:
create user saibot_finance identified by saibot_finance default tablespace users temporary tablespace temp; alter user saibot_finance quota unlimited on users; grant create session to saibot_finance; grant create type to saibot_finance; grant aq_administrator_role to saibot_finance; EXECUTE dbms_aqadm.grant_type_access('saibot_finance');
Alternatively, additional privileges can be granted to the user saibot:
grant aq_administrator_role to saibot; EXECUTE dbms_aqadm.grant_type_access('saibot');
The statements to create type aircraft_movement_report_t and configure queue aircraft_movements_queue on top of queue table saibot_finance.aircraft_movements_qt can be found in this script that is on Github in the source code repository for this book.