A Sample Scripts

This appendix provides the following sample SQL scripts:

• Appendix A, "Create_user.sql"

• Appendix A, "Create_queue_tables.sql"

A.1 Create_user.sql

Use the following script to create the user. The script prompts for the password.

create user mobileaq identified by &&mobileaq_pwd;
 
grant connect, resource to mobileaq;
 
grant aq_user_role to mobileaq;
 
Grant execute ON sys.dbms_aqadm TO mobileaq;
 
Grant execute ON sys.dbms_aq TO mobileaq;
 
Grant execute ON sys.dbms_aqin TO mobileaq;
 
Grant execute ON sys.dbms_aqjms TO mobileaq;

A.2 Create_queue_tables.sql

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'TRIP_REPORT_QT',
purge_condition => NULL,
purge_options => po);
END;
 
BEGIN
 
  dbms_aqadm.drop_queue_table('TRIP_REPORT_QT', TRUE, FALSE);
 
  dbms_aqadm.create_queue_table(
     queue_table=>'TRIP_REPORT_QT',
     queue_payload_type=>'sys.aq$_jms_text_message');
 
DBMS_AQADM.CREATE_QUEUE (
  queue_name => 'trip_report_queue'
, queue_table => 'mobileaq.trip_report_qt'
, queue_type => DBMS_AQADM.NORMAL_QUEUE
, max_retries => 5
, retry_delay => 0
, retention_time => 1800
, dependency_tracking => FALSE
, comment => 'queue for mobile trip report'
, auto_commit => FALSE);
 
DBMS_AQADM.START_QUEUE('trip_report_queue');
 
END;
 
 
CREATE TABLE "TRIP_REPORT_TRACKING"
  (
    "TRACKING_ID"     NUMBER(10,0),
    "USER_ID"        VARCHAR2(80 BYTE),
    "TRIP_REPORT_ID" VARCHAR2(80 BYTE),
    "TEMPLATE_ID"    VARCHAR2(80 BYTE),
    "CTMS_TRIP_REPORT_QRY_REQUEST" CLOB,
    "QUERIED_CANONICAL_TRIP_REPORT" CLOB,
    "CTMS_TRIP_REPORT_UPD_REQUEST" CLOB,
    "TO_UPD_CANONICAL_TRIP_REPORT" CLOB,
    "STATUS"     VARCHAR2(20 BYTE),
    "ERROR_CODE" VARCHAR2(80 BYTE),
    "ERROR_TEXT" VARCHAR2(2000 BYTE),
    "CREATED_BY" VARCHAR2(80 BYTE),
    "CREATION_TS" DATE,
    "MODIFIED_BY" VARCHAR2(80 BYTE),
    "MODIFICATION_TS" DATE,
    "LAST_REQ_OPERATION" VARCHAR2(15 BYTE),
    PRIMARY KEY ("TRACKING_ID")
  );
 
create sequence TRIP_REPORT_TRACKING_SEQ start with 1 increment by 1;