#!/usr/bin/ksh
##  set -xv
# author  : B. Polarski
# program : smenu_stream_propagation.ksh
# date    : 16 Decembre 2005
#           03 October 2007   Add Queue to queue (-lq)
#           15 October 2007   Added stats from stream 10G healthcheck from metatlink, just for conveniance
#           20 October 2007   Added list propagation schedule
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
# -------------------------------------------------------------------------------------µ
#--       DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
#--          queue_name        => '&&ApplSchema._strms_q',
#--          destination       => '@&&RemoteDBTNSName',
#--          latency           => NewParamVal,
#--          destination_queue => '&&ApplSchema._strms_q');
#  EXEC DBMS_AQADM.ALTER_SCHEDULE(queue_name,destination,latency=>1);

function help 
{

  cat <<EOF

       prop -l -pn <Propagation name> -create -stop -start  -drop -trace
            -destq <dest queue> -sourceq <source queue> -dblk <DB link>
       prop -lat <ss>  -pn <Propagation name> [ -adm <STREAM ADMIN USER> ]
       prop -s
       prop -lr
       prop -o <REMOTE DB>

          -l : List propagation process
          -v : Show overal picture for propagation (status and figures)
         -lr : List propagations rules
         -lc : List schedule propagation 
         -lq : propagation queue to queues correspondancies
          -s : Show statitics for propagations sender
          -r : Show statitics for propagations receiver
        -rcr : Drop and recreate propagation with same ruleset
        -lat : Set the propagation latency (in seconds)
         -pn : Name of the propagation
         -qn : Name of the queue
          -u : owner of the propagation
     -create : Create propagation process
       -drop : Drop propagation process
      -destq : Name of the destination queue
    -sourceq : Name of the source queue
       -dblk : database link to use with propagation queue
       -stop : stop propagation
      -start : start propagation         
      -check : check propagation is possible
          -v : verbose

      Add  : -trace to set events 24024 before starting the propagation
  
  Create a propagation queue  :  prop -create -u <OWNER> -pn <PROPAGATION NAME> -sourceq <QUEUE> -destq <QUEUE> 
                                      -dblk <DATABASE LINK>
  prop Overal image           :  prop -v <REMOTE DB (TNS ENTRY)
  Start a propagation queue   :  prop -start <PROPAGATION NAME> [-u <OWNER> only if not STRMADMIN]  -trace
  Stop  a propagation queue   :  prop -stop  <PROPAGATION NAME> [-u <OWNER> only if not STRMADMIN]
  Drop  a propagation queue   :  prop -drop  <PROPAGATION NAME> [-u <OWNER> only if not STRMADMIN]
  recreate propagation        :  prop -rcr -u  <OWNER> -pn <PROPAGATION NAME>
  Set the propagation latency :  prop -lat <ss>  -pn <PROPAGATION NAME>  [ -adm <STREAM ADMIN USER> only if not STRMADMIN ]
 check propagation is possible:  prop -check -u <OWNER> -pn <PROPAGATION NAME> -destq <QUEUE> | -u2 <OWNER_AT_DEST> -dblk <DB_LINK>
                                    (-u2 only if owner is # from -u )

    Stream administrator admin and his password can be deduced by smenu if you defined one for this instance
    in SM/3.8 ortherwise it will try to default to STRMADMIN/STRMADMIN


EOF

exit
}
# -------------------------------------------------------------------------------------
function do_execute
{
$SETXV
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 100 termout on pause off embedded on verify off heading off

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline, '$TTITLE (prop -h for help)' nline
from sys.dual
/
set head on
col rsn format A28 head "Rule Set name"
col rn format A30 head "Rule name"
col rt format A64 head "Rule text"

col d_dblk format A34 head 'Destination dblink'
col nams format A31 head 'Source queue'
col namd format A61 head 'Remote queue'
col prop format A22 head 'Propagation name '
col rsname format A20 head 'Rule set name'
COLUMN TOTAL_TIME HEADING 'Total Time Executing|in Seconds' FORMAT 999999
COLUMN TOTAL_NUMBER HEADING 'Total Events Propagated' FORMAT 999999999
COLUMN TOTAL_BYTES HEADING 'Total mb| Propagated' FORMAT 9999999999
COL PROPAGATION_NAME format a24
COL SOURCE_QUEUE_NAME format a24 head "Source| queue name" justify c
COL DESTINATION_QUEUE_NAME format a24 head "Destination| queue name" justify c
col QUEUE_TO_QUEUE format a9 head "Queue to| Queue"
col RULE_SET_NAME format a18
$BREAK
set linesize 125
prompt 
$SQL
EOF
}
# -------------------------------------------------------------------------------------
#                    Main
# -------------------------------------------------------------------------------------
if [ -z "$1" ];then 
   help; exit
fi

# ............ some default values and settings: .................
typeset -u fdest_q
typeset -u fsrc_q
typeset -u fdblink
typeset -u fqueue
typeset -u fowner
typeset -u fprop
if [ -f $SBIN/data/stream_$ORACLE_SID.txt ];then
   STRMADMIN=`cat $SBIN/data/stream_$ORACLE_SID.txt | grep STRMADMIN=| cut -f2 -d=`
   STR_PASS=`cat $SBIN/data/stream_$ORACLE_SID.txt | grep STR_PASS=| cut -f2 -d=`
   DEF_SID=`cat $SBIN/data/stream_$ORACLE_SID.txt | grep DEF_SID=| cut -f2 -d=`
   DEST_QUEUE_NAME=$STRMADMIN.STREAMS_QUEUE@$DEST_DB
fi
STRMADMIN=${STRMADMIN:-STRMADMIN}
STRMADMIN_PASS=${STR_PASS:-STRMADMIN}
EXECUTE=NO
INCLUDE_DML=TRUE
INCLUDE_DDL=FALSE
SRC_DB=$ORACLE_SID
DEST_DB=$DEF_SID
QUEUE_NAME=$STRMADMIN.STREAMS_QUEUE
SRC_QUEUE_NAME=$STRMADMIN.STREAMS_QUEUE
DEST_QUEUE_NAME=${DEST_QUEUE_NAME:-STRMADMIN.STREAMS_QUEUE}

while [ -n "$1" ]
do
  case "$1" in
       -l ) EXECUTE=YES ; TTITLE="List propagation process"
            CHOICE=LIST_PROP ;;
       -lq ) EXECUTE=YES ; TTITLE="List propagation Queue to queue correspondancies"
            CHOICE=Q_TO_Q ;;
       -lr ) EXECUTE=YES ; TTITLE="List propagation rules "
            CHOICE=PROP_RUL ;;
       -s ) EXECUTE=YES ; TTITLE="Stats for propagation sender"
            CHOICE=STATS ;;
       -r ) EXECUTE=YES ; TTITLE="Stats for propagation receiver"
            CHOICE=STATS_R ;;
       -o ) EXECUTE=YES ; TTITLE="Show propagation overal status and figures"
            CHOICE=OVERVIEW ; REMOTEDB=$2; shift ;;
       -lc ) EXECUTE=YES ; TTITLE="List schedules for propagation process"
             CHOICE=SCHEDULE ;;
     -lat ) TTITLE="Set the propagancy latancy (in seconds) "
            latency=$2; shift ; 
            CHOICE=SET_LAT ;;
  -create ) TTITLE="Create propagation process"
            CHOICE=CREATE ;;
     -rcr )    TTITLE="Re-create propagation process"
            CHOICE=RECREATE ;;
    -stop ) TTITLE="Stop propagation process"
            CHOICE=STOP ; fprop=$2 ; shift ;;
   -trace )  TRACE="alter system set events = '24040 trace name context forever, level 10' ;" ;;
   -start ) TTITLE="Start propagation process"; fprop=$2 ; shift; CHOICE=START ;;
    -drop ) TTITLE="Drop propagation process" ; fprop=$2 ; shift; CHOICE=DROP ;;
      -pn ) fprop=$2; shift ;;
      -qn ) fqueue=$2; shift ;;
      -adm) STREADMIN=$2; shift ;;
    -dblk ) fdblink=$2; shift ;;
   -destq ) fdest_q=$2; shift ;;
   -check )  CHOICE=CHECK ;;
 -sourceq ) fsrc_q=$2; shift ;;
       -u ) fowner=$2; shift ;;
      -u2 ) fowner2=$2; shift ;;
       -v ) SETXV="set -xv";;
       -x ) EXECUTE=YES;;
        * ) echo "Invalid argument $1"
            help ;;
 esac
 shift
done
if [  "$CHOICE" = "CREATE" -o "$CHOICE" = "RECREATE" -o "$CHOICE"  = "SET_LAT" -o "$CHOICE"  = "OVERVIEW"  -o "$CHOICE" = "START" -o "$CHOICE" = "CHECK" ];then
   export S_USER=$STRMADMIN
fi
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} SYS $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
vers=`$SBINS/smenu_get_ora_version.sh`
if [ "$CHOICE" = "CHECK" ];then
   if [ -n "$fowner2" ];then
       VAR2=$fowner
   else
       VAR2=$STRMADMIN
   fi
SQL=" SET SERVEROUTPUT ON size 9999
prompt return value of 1 means propagation is ok, 0 is NOK
prompt
DECLARE
rc_value number;
BEGIN
DBMS_AQADM.VERIFY_QUEUE_TYPES(src_queue_name => '$STRMADMIN.$fsrc_q',
dest_queue_name => '$VAR2.$fdest_q',
destination => '$fdblink',
rc => rc_value);
dbms_output.put_line('rc_value code is '||rc_value);
END;
/

"
elif [ "$CHOICE" = "OVERVIEW" ];then
   if [ -z "$REMOTEDB" ];then
         echo "I need the name of the remote DB to compre local capture to remote apply"
         echo "I will use STRMADMIN dblink"
         exit
   fi
SQL="set linesize 132
SELECT 'cap' stream, capture_name,  queue_name, rule_type, rule_name , last_enqueued_scn last_snc
       FROM dba_capture dc, dba_streams_rules dsr
      WHERE dsr.rule_set_name = dc.rule_set_name
union
SELECT 'app' stream, dc.apply_name,   queue_name,rule_type, rule_name , nvl(r.DEQUEUED_MESSAGE_NUMBER,0) last_dequeued  
       FROM dba_apply@$REMOTEDB dc, dba_streams_rules@$REMOTEDB dsr, V\$STREAMS_APPLY_READER@$REMOTEDB r
      WHERE dsr.rule_set_name = dc.rule_set_name
        and dc.apply_name = r.apply_name (+)
union
SELECT 'prop' stream,propagation_name,  source_queue_name,rule_type, rule_name,0 FROM dba_propagation dc, dba_streams_rules dsr
      WHERE dsr.rule_set_name = dc.rule_set_name
/
"
elif [ "$CHOICE" = "PROP_RUL" ];then
SQL="set long 4000
select rsr.rule_set_owner||'.'||rsr.rule_set_name rsn ,rsr.rule_owner||'.'||rsr.rule_name rn,
r.rule_condition rt from dba_rule_set_rules rsr, dba_rules r where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rule_set_name in (select
rule_set_name from dba_propagation) order by rsr.rule_set_owner,rsr.rule_set_name;"

elif [ "$CHOICE" = "Q_TO_Q" ];then
SQL="set linesize 132
select PROPAGATION_NAME,SOURCE_QUEUE_NAME, DESTINATION_QUEUE_NAME , RULE_SET_NAME ,
      QUEUE_TO_QUEUE ,STATUS,to_char(ERROR_DATE,'DD-MM-YYYY HH24:MI:SS') Error_date
from SYS.DBA_PROPAGATION;
select ERROR_MESSAGE from SYS.DBA_PROPAGATION ;
"
elif [ "$CHOICE" = "SCHEDULE" ];then
SQL="set linesize 150
col queue_name HEADING 'Source|Queue Name'
col queue_schema HEADING 'Source|Queue Owner'
col dblink format a34 head 'Destination|Database Link'
COLUMN SCHEDULE_STATUS HEADING 'Schedule Status' FORMAT A23
COLUMN PROPAGATION_NAME Heading 'Propagation|Name' format a25 wrap
COLUMN START_DATE HEADING 'Expected |Start Date'
COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 9999999999999999
COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8
COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 9999999999
COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8
COLUMN PROCESS_NAME HEADING 'Schedule|Process|Name' FORMAT A8
COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99
COLUMN LAST_ERROR_MSG HEADING 'Error Message' FORMAT A55
COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999999
COLUMN CURRENT_START_DATE HEADING 'Current|Start' FORMAT A17
COLUMN LAST_RUN_DATE HEADING 'Last|Run' FORMAT A17
COLUMN NEXT_RUN_DATE HEADING 'Next|Run' FORMAT A17
COLUMN LAST_ERROR_DATE HEADING 'Last|Error Date' FORMAT A17
COLUMN LAST_ERROR_TIME HEADING 'Last|Error time' FORMAT A12
column message_delivery_mode HEADING 'Message|Delivery|Mode'
column queue_to_queue HEADING 'Q-2-Q'
col destination format a50


SELECT p.PROPAGATION_NAME, s.QUEUE_NAME, s.DBLINK, s.SCHEDULE_STATUS
  FROM DBA_PROPAGATION p, V\$PROPAGATION_SENDER s
  WHERE p.DESTINATION_DBLINK = s.DBLINK (+) AND 
        p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME  = s.QUEUE_NAME;
prompt
prompt When the duration is NULL, the job propagates changes without restarting automatically. 
prompt When the next time is NULL, the propagation job is running currently.
prompt

SELECT p.propagation_name,TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,
       s.PROPAGATION_WINDOW,
       s.NEXT_TIME,
       s.LATENCY,
       DECODE(s.SCHEDULE_DISABLED, 'Y', 'Disabled', 'N', 'Enabled') SCHEDULE_DISABLED,
       s.PROCESS_NAME, s.total_bytes
  FROM 
      DBA_QUEUE_SCHEDULES s, 
      DBA_PROPAGATION p
  WHERE  p.DESTINATION_DBLINK = NVL(REGEXP_SUBSTR(s.destination, '[^@]+', 1, 2), s.destination)
         AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
  AND s.QNAME = p.SOURCE_QUEUE_NAME order by  propagation_name;

select p.propagation_name, s.message_delivery_mode,
       s.FAILURES, 
       p.queue_to_queue,
       s.LAST_ERROR_MSG
  FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
    WHERE   p.DESTINATION_DBLINK =
        NVL(REGEXP_SUBSTR(s.destination, '[^@]+', 1, 2), s.destination)
  AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
  AND s.QNAME = p.SOURCE_QUEUE_NAME order by s.message_delivery_mode, propagation_name;

SELECT p.propagation_name,  TO_CHAR(s.LAST_RUN_DATE, 'HH24:MI:SS MM/DD/YY') LAST_RUN_DATE,
   TO_CHAR(s.CURRENT_START_DATE, 'HH24:MI:SS MM/DD/YY') CURRENT_START_DATE,
   TO_CHAR(s.NEXT_RUN_DATE, 'HH24:MI:SS MM/DD/YY') NEXT_RUN_DATE,
   TO_CHAR(s.LAST_ERROR_DATE, 'HH24:MI:SS MM/DD/YY') LAST_ERROR_DATE,
   LAST_ERROR_TIME
  FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
    WHERE   p.DESTINATION_DBLINK =
        NVL(REGEXP_SUBSTR(s.destination, '[^@]+', 1, 2), s.destination)
  AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
  AND s.QNAME = p.SOURCE_QUEUE_NAME order by  propagation_name;
"
elif [ "$CHOICE" = "STATS_R" ];then
SQL=" COLUMN SRC_QUEUE_NAME HEADING 'Source|Queue|Name' FORMAT A20
COLUMN DST_QUEUE_NAME HEADING 'Target|Queue|Name' FORMAT A20
COLUMN SRC_DBNAME HEADING 'Source|Database' FORMAT A15
COLUMN ELAPSED_UNPICKLE_TIME HEADING 'Unpickle|Time' FORMAT 99999999.99
COLUMN ELAPSED_RULE_TIME HEADING 'Rule|Evaluation|Time' FORMAT 99999999.99
COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Enqueue|Time' FORMAT 99999999.99

SELECT SRC_QUEUE_NAME,
       SRC_DBNAME,DST_QUEUE_NAME,
       (ELAPSED_UNPICKLE_TIME / 100) ELAPSED_UNPICKLE_TIME,
       (ELAPSED_RULE_TIME / 100) ELAPSED_RULE_TIME,
       (ELAPSED_ENQUEUE_TIME / 100) ELAPSED_ENQUEUE_TIME, TOTAL_MSGS,HIGH_WATER_MARK
  FROM V\$PROPAGATION_RECEIVER;
"
elif [ "$CHOICE" = "STATS" ];then
   SQL="
prompt
prompt ++ EVENTS AND BYTES PROPAGATED FOR EACH PROPAGATION++
prompt
COLUMN Elapsed_propagation_TIME HEADING 'Elapsed |Propagation Time|(Seconds)' FORMAT 9999999999999999
COLUMN TOTAL_NUMBER HEADING 'Total Events|Propagated' FORMAT 9999999999999999
COLUMN SCHEDULE_STATUS HEADING 'Schedule|Status'
column elapsed_dequeue_time HEADING 'Total Dequeue|Time (Secs)'
column elapsed_propagation_time HEADING 'Total Propagation|Time (Secs)' justify c
column elapsed_pickle_time HEADING 'Total Pickle| Time(Secs)' justify c
column total_time HEADING 'Elapsed|Pickle Time|(Seconds)' justify c
column high_water_mark HEADING 'High|Water|Mark'
column acknowledgement HEADING 'Target |Ack'
prompt pickle : Pickling is the action of building the messages, wrap the LCR before enqueuing
prompt
set linesize 150
SELECT p.propagation_name,q.message_delivery_mode queue_type, DECODE(p.STATUS,
                'DISABLED', 'Disabled', 'ENABLED', 'Enabled') SCHEDULE_STATUS, q.instance,
                q.total_number TOTAL_NUMBER, q.TOTAL_BYTES/1048576 total_bytes,
                q.elapsed_dequeue_time/100 elapsed_dequeue_time, q.elapsed_pickle_time/100 elapsed_pickle_time,
                q.total_time/100 elapsed_propagation_time
  FROM  DBA_PROPAGATION p, dba_queue_schedules q
        WHERE   p.DESTINATION_DBLINK = NVL(REGEXP_SUBSTR(q.destination, '[^@]+', 1, 2), q.destination)
  AND q.SCHEMA = p.SOURCE_QUEUE_OWNER
  AND q.QNAME = p.SOURCE_QUEUE_NAME
  order by q.message_delivery_mode, p.propagation_name;
"
elif [ "$CHOICE" = "SET_LAT" ];then
# ------------------------------------------------------------------------------------
#--       DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
#--          queue_name        => '&&ApplSchema._strms_q',
#--          destination       => '@&&RemoteDBTNSName',
#--          latency           => NewParamVal,
#--          destination_queue => '&&ApplSchema._strms_q');
#  EXEC DBMS_AQADM.ALTER_SCHEDULE(queue_name,destination,latency=>1);
case $vers in
  9 )  SQL="execute DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE( queue_name => '$fowner.$fqueue', destination => '$fdblink', latency=>$latency)" ;;
  10) SQL="col DESTINATION_QUEUE_NAME new_value DESTINATION_QUEUE_NAME noprint
col DESTINATION_DBLINK new_value DESTINATION_DBLINK noprint
col source_queue_name new_value source_queue_name noprint

 select SOURCE_QUEUE_NAME, DESTINATION_QUEUE_NAME, DESTINATION_DBLINK from SYS.DBA_PROPAGATION  where propagation_name = upper('$fprop');
 set serveroutput on size 9999
  col cmd new_value cmd noprint
  execute DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE( queue_name => 'STRMADMIN.&source_queue_name',  destination =>'&DESTINATION_DBLINK',  destination_queue=>'&DESTINATION_QUEUE_NAME',  latency=>$latency, duration=>null, next_time=>null) ;"
  ;;
esac

elif [ "$CHOICE" = "STOP" ];then
   #SQL="execute DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE ( queue_name => '$fowner.$fqueue', destination => '$fdblink');"
   SQL="execute DBMS_PROPAGATION_ADM.stop_propagation('$fprop',force=>true);"


elif [ "$CHOICE" = "START" ];then
  if [ $vers -eq 9 ];then
      SQL="$TRACE
execute DBMS_PROPAGATION_ADM.start_PROPAGATION( propagation_name => '$fowner.$fprop');"
  else
      SQL=" execute DBMS_PROPAGATION_ADM.start_propagation('$fprop');"
  fi
elif [ "$CHOICE" = "DROP" ];then
  if [ $vers -eq 9 ];then
      SQL="execute DBMS_PROPAGATION_ADM.DROP_PROPAGATION( propagation_name => '$fowner.$fprop');"
  else
      SQL="execute DBMS_PROPAGATION_ADM.DROP_PROPAGATION( propagation_name => '$fprop');"
  fi
elif [ "$CHOICE" = "RECREATE" ];then
SQL="
col propagation_name new_value propagation_name noprint
col RULE_SET_NAME new_value RULE_SET_NAME noprint
col RULE_SET_OWNER new_value RULE_SET_OWNER noprint
col source_queue_name new_value source_queue_name noprint
col source_queue_owner new_value source_queue_owner noprint
col QUEUE_TO_QUEUE new_value QUEUE_TO_QUEUE noprint
col DESTINATION_QUEUE_NAME new_value DESTINATION_QUEUE_NAME noprint
col DESTINATION_QUEUE_OWNER new_value DESTINATION_QUEUE_OWNER noprint
col DESTINATION_DBLINK new_value DESTINATION_DBLINK noprint
col negative_rule_set_name new_value negative_rule_set_name noprint
select propagation_name , RULE_SET_NAME, source_queue_name,QUEUE_TO_QUEUE, DESTINATION_QUEUE_NAME , RULE_SET_OWNER,SOURCE_QUEUE_OWNER,
    DESTINATION_DBLINK,negative_rule_set_name,DESTINATION_QUEUE_OWNER from SYS.DBA_PROPAGATION;

col neg_rls new_value neg_rls 
select decode(NEGATIVE_RULE_SET_OWNER||'.'||NEGATIVE_RULE_SET_NAME ,'.','null', NEGATIVE_RULE_SET_OWNER||'.'||NEGATIVE_RULE_SET_NAME ) neg_rls
       from  SYS.DBA_PROPAGATION where propagation_name = upper('$fprop');
execute DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE ( queue_name => '&DESTINATION_QUEUE_OWNER..&source_queue_name', destination => '&DESTINATION_DBLINK');
execute DBMS_PROPAGATION_ADM.DROP_PROPAGATION( propagation_name => '$fprop');
execute DBMS_PROPAGATION_ADM.CREATE_PROPAGATION( propagation_name => '$fprop', source_queue => '&SOURCE_QUEUE_OWNER..&&source_queue_name', destination_queue => '&DESTINATION_QUEUE_OWNER..&DESTINATION_QUEUE_NAME', destination_dblink => '&DESTINATION_DBLINK', rule_set_name=>'&RULE_SET_OWNER..&rule_set_name' , negative_rule_set_name=>&neg_rls , queue_to_queue=> true);
"
elif [ "$CHOICE" = "CREATE" ];then
  SQL="execute DBMS_PROPAGATION_ADM.CREATE_PROPAGATION( propagation_name => '$fowner.$fprop', source_queue => '$fowner.$fsrc_q', destination_queue => '$fowner.$fdest_q', destination_dblink => '$fdblink');"
elif [ "$CHOICE" = "LIST_PROP" ];then
  SQL="select PROPAGATION_NAME prop,  RULE_SET_NAME rsname , nvl(DESTINATION_DBLINK,'Local to db') d_dblk,NEGATIVE_RULE_SET_NAME
              from dba_propagation ;
  select SOURCE_QUEUE_OWNER||'.'|| SOURCE_QUEUE_NAME nams , DESTINATION_QUEUE_OWNER||'.'|| DESTINATION_QUEUE_NAME||
          decode( DESTINATION_DBLINK,null,'','@'|| DESTINATION_DBLINK) namd, status , QUEUE_TO_QUEUE
              from dba_propagation ;"
fi

if [ "$EXECUTE" = "YES" ];then
   do_execute
else
  echo "$SQL"
fi