int_prp.lst

oracle9i+j2ee开发aq oracle9i+j2ee开发aq oracle9i+j2ee开发aq

SQL> @@bz_int_pr_crdblink.sql
SQL> REM script name: bz_cr_dblink.sql
SQL> REM Creates a dblink "loopback" to demostrate propagation
SQL> REM
SQL> REM
SQL> REM version: 9i
SQL> REM
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> 
SQL> drop public database link loopback;

Database link dropped.

SQL> 
SQL> REM =======================================================
SQL> REM Create a loopback to the current database
SQL> REM Replace bgoyaldb by the service name of your database
SQL> REM =======================================================
SQL> 
SQL> CREATE public DATABASE LINK loopback CONNECT TO "bob" IDENTIFIED BY "welcome" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=http)(HOST=bgoyal-sun.us.oracle.com)(PORT=7777)))';

Database link created.

SQL> connect bz_adm/bz_adm
Connected.
SQL> @@bz_prp_cr_q.sql
SQL> REM script name: bz_pr_cr_q.sql
SQL> REM Creates queues - bzcardorders_prpq
SQL> REM Starts the queue
SQL> REM
SQL> REM version: 9i
SQL> REM
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> BEGIN
  2    dbms_aqadm.stop_queue(queue_name => 'bzcardorders_prpq');
  3    dbms_aqadm.drop_queue (
  4  	     queue_name 	     => 'bzcardorders_prpq');
  5  END;
  6  /
BEGIN
*
ERROR at line 1:
ORA-24010: QUEUE BZ_ADM.BZCARDORDERS_PRPQ does not exist 
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3125 
ORA-06512: at "SYS.DBMS_AQADM", line 237 
ORA-06512: at line 2 


SQL> 
SQL> REM =======================================================
SQL> REM Create a queue to show propagation
SQL> REM =======================================================
SQL> BEGIN
  2  dbms_aqadm.create_queue (
  3  	     queue_name 	     => 'bzcardorders_prpq',
  4  	     queue_table	     => 'bzcardorders_qt');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> REM =======================================================
SQL> REM start queue
SQL> REM =======================================================
SQL> BEGIN
  2    dbms_aqadm.start_queue (
  3  	     queue_name 	     => 'bzcardorders_prpq');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  dbms_aqadm.grant_queue_privilege(privilege => 'ALL',
  3  				     queue_name => 'bzcardorders_prpq',
  4  				     grantee => 'bz',
  5  				     grant_option => TRUE);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  dbms_aqadm.add_subscriber(queue_name => 'bzcardorders_prpq',
  3  			       subscriber => sys.aq$_agent('sub1', null, null));
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> @@bz_int_add_subscriber.sql
SQL> REM script name: bz_prp_add_subscriber.sql
SQL> REM This script adds a subscriber to bzcardorders_q to show
SQL> REM propagation
SQL> REM version: 9i
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> execute dbms_aqadm.remove_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent(null,'bzcardorders_prpq@loopback', null)-
> );
BEGIN dbms_aqadm.remove_subscriber (  	  queue_name => 'BZCARDORDERS_Q',  	  subscriber => sys.aq$_agent(null,'bzcardorders_prpq@loopback', null) ); END;

*
ERROR at line 1:
ORA-24035: AQ agent  is not a subscriber for queue  
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4475 
ORA-06512: at "SYS.DBMS_AQADM", line 387 
ORA-06512: at line 1 


SQL> 
SQL> execute dbms_aqadm.unschedule_propagation('bzcardorders_q', 'loopback');
BEGIN dbms_aqadm.unschedule_propagation('bzcardorders_q', 'loopback'); END;

*
ERROR at line 1:
ORA-24042: no propagation schedule exists for QUEUE BZCARDORDERS_Q and 
DESTINATION LOOPBACK 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 
ORA-06512: at "SYS.DBMS_AQADM", line 800 
ORA-06512: at line 1 


SQL> REM =======================================================
SQL> REM Add remote subscriber to BZCARDORDERS_Q
SQL> REM =======================================================
SQL> 
SQL> execute dbms_aqadm.add_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent(null,'bzcardorders_prpq@loopback',null));

PL/SQL procedure successfully completed.

SQL> 
SQL> execute dbms_aqadm.schedule_propagation('bzcardorders_q', 'loopback');

PL/SQL procedure successfully completed.

SQL> 
SQL>