Image Modified

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.

...

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.

...

Anchor
DBForwardingTargetSToredProcedure DBForwardingTargetSToredProcedure

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.

...

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;
/ 

Anchor
CleanupStoredProcedure CleanupStoredProcedure

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.

...

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.

...