Catching ACITIVITYCOUNT value using PROCEDURE

General
N/A

Catching ACITIVITYCOUNT value using PROCEDURE

Hi

I have created a procedure to catch activitycount of any DML.

replace PROCEDURE test_SP(tb_name varchar(250))
BEGIN
UPDATE stats_data SET TOTAL = TOTAL + ACTIVITY_COUNT WHERE TABLE_NAME=:tb_name;
END;

The problem is when I am calling this procedure after any DML statement it is not setting the proper ACTIVITYCOUNT value. Everytime it is set to 0 only.

eg:

insert into target sel * from source;
call test_SP('target');

However if I put my dml inside the procedure then it is working absolutely fine.

replace PROCEDURE test_SP(tb_name varchar(250))
BEGIN
INSERT INTO TARGET SEL * FROM SOURCE;
UPDATE stats_data SET TOTAL = TOTAL + ACTIVITY_COUNT WHERE TABLE_NAME=:tb_name;
END;

The problem is I cannot create PROCEDURE for each DML. I want a solution where I will call the procedure after few selected DML commands to fetch ACTIVITYCOUNT.

Any suggestions how to do it ?

Thanks in advance.

Nitin 'Raj' SRIVASTAVA

Tags (1)
1 REPLY
N/A

Re: Catching ACITIVITYCOUNT value using PROCEDURE

Which interface to you use to exec the SQLs?

SQLA, BTEQ, Java, Perl?