Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- BEGIN
- SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
- (
- QUEUE_TABLE => 'QT_NEW_CASE'
- ,QUEUE_PAYLOAD_TYPE => 'FILE_ACTION'
- ,COMPATIBLE => '8.1'
- ,STORAGE_CLAUSE => '
- TABLESPACE USERS
- PCTUSED 0
- PCTFREE 10
- INITRANS 1
- MAXTRANS 255
- STORAGE (
- INITIAL 64K
- NEXT 1M
- MINEXTENTS 1
- MAXEXTENTS UNLIMITED
- PCTINCREASE 0
- BUFFER_POOL DEFAULT
- )'
- ,SORT_LIST => 'ENQ_TIME'
- ,MULTIPLE_CONSUMERS => FALSE
- ,MESSAGE_GROUPING => 0
- ,SECURE => FALSE
- );
- End;
- CREATE OR REPLACE TYPE FILE_ACTION AS OBJECT
- ( ACTION VARCHAR2(20),
- CASE_ID NUMBER(10),
- OTHER VARCHAR2(20)
- );
- queue_util.add_file ('CLOSE', v_case_id,:NEW.ID);
- PROCEDURE add_file_to_queue (action_in IN VARCHAR2,
- d_case_id_in IN NUMBER,
- d_other_in IN VARCHAR2:= NULL)
- IS
- /******************************************************************************
- PURPOSE: when there is a change to a file (create, closed or reopen) add the change to the queue of changes
- ******************************************************************************/
- queue_options SYS.DBMS_AQ.enqueue_options_t;
- message_properties SYS.DBMS_AQ.message_properties_t;
- message_id RAW (16);
- my_message file_action;
- err_text VARCHAR2 (2000);
- PRAGMA AUTONOMOUS_TRANSACTION;
- BEGIN
- my_message := file_action (action_in, d_case_id_in, d_other_in);
- DBMS_AQ.enqueue (queue_name => 'NEW_CASE_QUEUE',
- enqueue_options => queue_options,
- message_properties => message_properties,
- payload => my_message,
- msgid => message_id);
- IF g_debugging
- THEN
- ;
- --insert debugging info if g_debugging is true
- END IF;
- COMMIT;
- EXCEPTION
- WHEN NO_DATA_FOUND
- THEN
- err_text := SQLERRM;
- --logging error to another table
- WHEN OTHERS
- THEN
- err_text := SQLERRM;
- --more logging
- END add_file_to_queue;
- PROCEDURE send_from queue (case_id_in IN NUMBER := NULL)
- IS
- /******************************************************************************
- PURPOSE:get the list of file changes and send them out
- *****************************************************************************/
- queue_options DBMS_AQ.dequeue_options_t;
- message_properties DBMS_AQ.message_properties_t;
- message_id file_action;
- v_file VARCHAR2 (20);
- v_case_id NUMBER (10);
- v_filename VARCHAR2 (500);
- v_action VARCHAR2 (20);
- v_other VARCHAR2 (20);
- v_err_id INTEGER;
- bad_data_ex EXCEPTION;
- v_err_text VARCHAR2 (50);
- TYPE cases_cur IS REF CURSOR;
- new_cases cases_cur;
- BEGIN
- IF case_id_in IS NULL
- THEN
- OPEN new_cases FOR
- SELECT qt.msgid
- FROM cqt_new_case qt
- ORDER BY qt.enq_time;
- ELSE
- OPEN new_cases FOR
- SELECT qt.msgid
- FROM qt_new_case qt
- WHERE qt.user_data.case_id = case_id_in
- ORDER BY qt.enq_time;
- END IF;
- --should have added a check here to make sure
- --the other database is up and running
- LOOP
- BEGIN
- FETCH new_cases INTO message_id;
- --reinitialize values to null
- v_case_id := NULL;
- v_filename := NULL;
- v_file := NULL;
- v_action := NULL;
- v_other := NULL;
- --to try and clear all locks
- COMMIT;
- EXIT WHEN new_cases%NOTFOUND;
- IF case_id_in IS NOT NULL
- THEN
- queue_options.deq_condition :=
- 'tab.user_data.case_id = ' || case_id_in;
- END IF;
- DBMS_AQ.dequeue (queue_name => 'NEW_CASE_QUEUE',
- dequeue_options => queue_options,
- message_properties => message_properties,
- payload => my_message,
- msgid => message_id);
- v_case_id := my_message.case_id;
- v_action := my_message.action;
- v_other := my_message.other;
- IF v_case_id IS NOT NULL AND v_case_id > 0
- THEN
- IF g_debugging
- THEN
- ;
- --insert your debugging information
- END IF;
- --continues on with lengthy data transforms
- --for actions like NEW, CLOSE, REOPEN
- END SEND_FROM_QUEUE;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement