Oracle BAM : Collect and populate real-time data from AQ JMS
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;
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' );
