Working Table

Following list holds information to be taken into consideration when creating the database table that a Database collection agent collects from, or a Database forwarding agent distributes to.

• The table must have a Transaction ID column, dedicated to the Database agent's internal use. The column could be named arbitrary however it must be numeric with at least twelve digits. It must also not allow NULL.

• Reading from or writing to columns defined as BLOB will have a negative impact on performance for both Database agents.

• It has been proved inefficient to put an index on the Transaction ID column.

• Entries with Transaction ID column set to -1 (Mark as Collected) or -2 (Cancel Batch) must be attended to manually at regular intervals.

The following example shows a working table with a Transaction ID column named txn_id.
 

CREATE TABLE my_tab (
txn_id   NUMBER(12)   NOT NULL,
a_num     VARCHAR2(25) NOT NULL,
b_num     VARCHAR2(25) NOT NULL,
duration NUMBER(10)        
);

After Collection Stored Procedure

If a Database collection agent has been configured to call a stored procedure after collection, it will be called when each batch has been successfully collected and inserted into the workflow.

The procedure is expected to take one (1) parameter. The parameter must be declared as a NUMBER and the agent assigns the current Transaction ID to the parameter. The procedure must ensure that the rows with the supplied transaction ID are removed from the table, or their Transaction ID column is set to -1.

The following example shows such a procedure that moves the rows to another table.

CREATE or REPLACE PROCEDURE my_move_sp 
  (txn IN NUMBER) IS 
 BEGIN 
    --copy collected rows to another table 
    INSERT INTO my_collected_data_tab (txn_id, a_num, b_num, 
    duration) SELECT txn_id, a_num, b_num, duration FROM 
    my_tab WHERE txn_id = txn; 
    --now delete the rows 
    DELETE FROM my_tab WHERE txn_id = txn; 
 END;

Database Forwarding Target Stored Procedure

If a Database forwarding agent has been configured to use a stored procedure as the Access Type the agent will call this procedure for each UDR that is to be distributed. The stored procedure must be defined to take the parameters needed, often including a parameter for the Transaction ID. In the dialog these parameters are assigned their values. When the procedure is called, the agent will populate each parameter with the assigned value.

The following example shows a stored procedure that selects the number of calls made by the a_number subscriber from another table, calls_tab, and uses that value to populate the target table.

CREATE OR REPLACE PROCEDURE my_insert_sp
(a_num IN CHAR, b_num IN CHAR, txn IN NUMBER) IS
BEGIN
DECLARE
  cnt_calls NUMERIC(5);
  BEGIN
    SELECT COUNT(*) INTO cnt_calls FROM calls_tab 
       WHERE anumber=a_num;

    INSERT INTO my_tab (from_num, to_num, txn_id, num_calls)
      VALUES (a_num, b_num, txn, cnt_calls);
  END;
END;
/ 

Cleanup Stored Procedure

If a Database forwarding agent uses a stored procedure to populate the target table, a cleanup stored procedure must be defined, that will remove all inserted entries in case of a Cancel Batch in the workflow. The procedure is expected to take one parameter. The parameter must be declared as a NUMBER and the agent will assign the current Transaction ID to the parameter.

The following example shows such a procedure that removes all the entries with the current Transaction ID.

CREATE OR REPLACE PROCEDURE my_clean_sp
  (txn IN NUMBER) IS
BEGIN
  DELETE FROM my_tab WHERE txn_id = txn;
END;
/  

After Forwarding Stored Procedure

The following example shows a stored procedure that marks the row as safe to read by another system.

CREATE TABLE billing_data (
    customer_id         varchar2(100) NULL,
    number_of_calls     number(5) NULL,
    money_to_pay     number(9) NULL,
    txn_id        number(12) NULL,
    txn_safe_indicator    varchar2(10)  DEFAULT 'UNSAFE' NOT NULL
);

CREATE or REPLACE PROCEDURE mark_billing_data_as_safe
(txn IN number) IS
BEGIN
   LOOP
      --updates 5000 rows at the time to spare rollback segments     
      update billing_data set txn_safe_indicator = 'SAFE'
      where txn_id = txn and rownum <= 5000;
     
      EXIT WHEN SQL%ROWCOUNT < 5000;
      COMMIT;
   END LOOP;
   COMMIT;    
END;