Home » Server Options » Streams & AQ » Help with Sync capture
Help with Sync capture [message #480852] Wed, 27 October 2010 11:17 Go to next message
lamikam
Messages: 7
Registered: April 2009
Junior Member
Hello,

I am trying to setup a simple prototype for a Sync capture, but I am getting stuck with some of the details from reading the documentation. What I want to do is setup a sync capture, send the LCR to a persistant AQ queue, then
have a message handler get the LCR messages off the queue and do some logging. From looking at the 11gR1 docs, I get as far as creating the AQ queue. Then I am confused as to which package/function to call to setup the capture and register a handler proc. Hany help is appreciated!

CREATE USER strmadmin IDENTIFIED BY password
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => TRUE);
END;
/


Re: Help with Sync capture [message #480861 is a reply to message #480852] Wed, 27 October 2010 19:07 Go to previous message
lamikam
Messages: 7
Registered: April 2009
Junior Member
CREATE USER strmadmin IDENTIFIED BY password
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => TRUE);
END;
/


BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.streams_queue_table',
queue_name => 'strmadmin.streams_queue',
queue_user => 'el_owner');
END;
/



CREATE OR REPLACE PROCEDURE emp_dml_handler(in_any IN ANYDATA) IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
command VARCHAR2(30);
old_values SYS.LCR$_ROW_LIST;
BEGIN

-- Access the LCR
rc := in_any.GETOBJECT(lcr);
-- Get the object command type
command := lcr.GET_COMMAND_TYPE();
commit;

end emp_dml_handler;


BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'el_owner.account_full',
object_type => 'TABLE',
operation_name => 'DEFAULT',
error_handler => FALSE,
user_procedure => 'el_owner.emp_dml_handler',
apply_database_link => NULL,
apply_name => NULL);
END;
/




BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'el_owner.account_full',
streams_type => 'sync_capture',
streams_name => 'sync_capture',
queue_name => 'strmadmin.streams_queue');
END;
/




Pages: 1 Back to Thread List

Legend
Guru : 2500 - 1000000 pts
Expert : 1000 - 2499 pts
Pro : 500 - 999 pts
Journeyman : 200 - 499 pts
Newbie : 0 - 199 pts
Oracle ACE Director
Oracle ACE Member
Oracle Employee ACE
Helpful Answer (5 pts)
Correct Answer (10 pts)


Previous Topic: Messaging Gateway Question
Next Topic: Error creating Streams example - ORA-01729: database link name expected
Goto Forum:
  


Current Time: Thu Mar 28 03:31:29 CDT 2024