Tables and Stored Procedures(3.0)
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
.
Example - Working table with 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.
Example - After collection stored procedure
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;
Note!
It is recommended for the previously described stored procedure to use an internal cursor with several commits, not to overflow the rollback segments.
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.
Example - Database forwarding target stored procedure
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.
Example - Cleanup stored procedure
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.
Example - After forwarding stored procedure
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;
The billing system must avoid reading rows that contains 'UNSAFE' in the txn_safe_indicator column, to ensure no data is read that could be rolled back later on.