Oracle BAM : Collect and populate real-time data from AQ JMS

by toy

I have been assigned an assignment which is using oracle bam with AQ JMS. For one reason why I have to do this because we don’t have BPEL for designing flow of data. So, I have to use Enterprise Link instead.

Objectives:
1. Create queue, queue table and user-defined object type in Oracle XE.
2. Configure BAM to collect and populate real-time data into ADC.

Create user and grant privilege
1. In order to use aq in Oracle Database, we have to grant privilege to user.

SQL Script
connect sys/welcome1@db10g as sysdba
grant SELECT_CATALOG_ROLE TO scott;
GRANT EXECUTE ON DBMS_APPLY_ADM TO scott;
GRANT EXECUTE ON DBMS_AQ TO scott;
GRANT EXECUTE ON DBMS_AQADM TO scott;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO scott;
GRANT EXECUTE ON DBMS_FLASHBACK TO scott;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO scott;
execute dbms_aqadm.grant_system_privilege(‘ENQUEUE_ANY’, ‘scott’, TRUE);
grant aq_administrator_role to scott;
grant execute on dbms_lock to scott;
grant execute on sys.dbms_aqin to scott;
grant execute on sys.dbms_aqjms to scott;
exit;



Note: If the user is locked. run this script

SQL Script
ALTER USER scott ACCOUNT UNLOCK;



2. Connect to database as scott user to create AQ

SQL Script
connect scott/tiger@db10g
execute dbms_aqadm.create_queue_table( queue_table => 'bam_ttq_tab',queue_payload_type => 'sys.aq$_jms_text_message', multiple_consumers => true );
execute dbms_aqadm.create_queue( queue_name => 'bam_ttq', queue_table =>'bam_ttq_tab' );
execute dbms_aqadm.start_queue( queue_name => 'bam_ttq' );