ORACLE QUEUE

edit tts.frm_formcomplaint s where S.FORMNUMBER='4992014304816'

CREATE OR REPLACE TRIGGER TTS.FRM_FORMCOMPLAINT_q_t
AFTER INSERT OR UPDATE OR DELETE
ON TTS.FRM_FORMCOMPLAINT
FOR EACH ROW
DECLARE
json VARCHAR2 (4000);
BEGIN
IF INSERTING OR UPDATING THEN
json:='{"ORACLE_ID":'||:new.ID;
json:=json||',"FORMNUMBER":"'||:new.FORMNUMBER||'"';
if :new.COMPLAINTID IS NULL THEN
json:=json||',"COMPLAINTID":null';
ELSE json:=json||',"COMPLAINTID":'||:new.COMPLAINTID;END IF;
if :new.ISDELETED IS NULL THEN
json:=json||',"ISDELETED":null';
ELSE json:=json||',"ISDELETED":'||:new.ISDELETED;END IF;json := json || '}';
ELSE
json:='{"ORACLE_ID":'||:old.ID||',"dml_type":"D"}';
END IF;
enqueue_message ('TTS.FRM_FORMCOMPLAINT_Q',json);
END;
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'TTS.FRM_FORMCOMPLAINT_Q_TB'
,QUEUE_PAYLOAD_TYPE => 'TTS.JSONDATA_T'
,COMPATIBLE => '10.0.0'
,STORAGE_CLAUSE => 'NOCOMPRESS
TABLESPACE TTS_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)'
,SORT_LIST => 'ENQ_TIME'
,MULTIPLE_CONSUMERS => FALSE
,MESSAGE_GROUPING => 0
,SECURE => FALSE
);
End;
BEGIN
DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'TTS.FRM_FORMCOMPLAINT_Q'
,QUEUE_TABLE => 'TTS.FRM_FORMCOMPLAINT_Q_TB'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 5
,RETRY_DELAY => 0
,RETENTION_TIME => 0
);
END;
BEGIN
DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'TTS.FRM_FORMCOMPLAINT_Q'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
edit FRM_FORMCOMPLAINT_Q_TB