This content has been marked as final. Show 3 replies
it is not working after want to insert between in the tableThat is very cryptic. What do you mean?
Furthermore, this is just a procedure. How are you using it? In which trigger is this procedure called?
It can be as simple as this example of a KEY-EXEQRY trigger (where :emp.run_total is a non-database item holding the running total):
Edited by: InoL on May 24, 2013 10:13 AM
declare l_run_total number := 0; begin execute_query; go_record(1); loop l_run_total := l_run_total + nvl(:emp.sal,0); :emp.run_total := l_run_total; exit when :system.last_record = 'TRUE'; next_record; end loop; go_record(1); end;
The suggestion which InoL gave is the perfect one. The procedure is also neat.
But, it is useful only when you have the first column as database column and the data is saved in a database table. The totals will be seen when you query the records and it won't be dynamic.
If you want it dynamic, you can use a timer as well. Call the same procedure on when-timer-expired. So that, if user changes the first value (:emp.sal for example) in any of the records, the summary (:emp.sal_total) will be updated after a particular time interval.
Why write code for this? Oracle Forms 10g has Calculated/Summary fields that will do this for you without the need of writing any PL/SQL.
Simply add a non-table item to the data block with the item you want to keep the running total for. Then change the following properties of the item:
Data Type: Number
Calculation Mode: Summary
Summary Function: Sum
Summarized Block: <Your Data Block>
Summarized Item: <Your Block Item>
Number of Items Displayed: 1 (if your item is in a Multi-Record (Tabular) layout else you don't need to change this property.
You will also need to change the following property of the block: Query All Records: Yes
If changing the Block's "Query All Records" property causes your form to be too slow because of the number of records in your block, then you could keep a running total manually by using a combination of the Post-Query and When-Validate-Item (WVI) triggers. If your block does not allow data entry, then you could do this with just the Post-Query trigger. For example:
If your block allows data entry, then you would add the following to your existing WVI trigger.
BEGIN /* This code sample assumes you have added a non-table item */ /* to your block called SUMMARY. */ :YOUR_BLOCK.SUMMARY := :YOUR_BLOCK.SUMMARY + NVL(:RS_1,0); END;
Hope this helps.
BEGIN /* Perform your data entry validation logic first... */ IF .... ELSE ...Validation is successful... :YOUR_BLOCK.SUMMARY := :YOUR_BLOCK.SUMMARY + NVL(:RS_1,0); END IF; END;