All Forums General
TD_Raj 50 posts Joined 05/10
29 May 2012
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

ulrich 816 posts Joined 09/09
29 May 2012

Which interface to you use to exec the SQLs?

SQLA, BTEQ, Java, Perl?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.