Loop trough data block with different time elapsed

Question

I'm using Oracle Forms 10g, on a Oracle Database version 7. I have a data block who is refreshed (new execute_query) from two different ways:

1.A button how implement this:

PROCEDURE refresh
IS
   ID   NUMBER;
BEGIN
   ID := :myblock.id;
   GO_BLOCK ('myBlock');
   EXECUTE_QUERY;
   -- Keep the record selected after the refresh
   POSITION (ID);
END;

PROCEDURE POSITION (ID IN NUMBER)
IS
   L_record   NUMBER (5) := NULL;
BEGIN
   GO_BLOCK ('myBlock');
   GO_ITEM ('myBlock.ID');

   FIRST_RECORD;

   LOOP

      IF :myblock.id = myID THEN
         L_record := GET_BLOCK_PROPERTY ('myBlock', CURRENT_RECORD);
      END IF;

      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' OR :myblock.ID = myID;
      NEXT_RECORD;
   END LOOP;

   IF L_record IS NOT NULL THEN
      GO_RECORD (L_record);
   END IF;
END;

2.The other is one other button who do this:

PROCEDURE newRefresh
IS
   ID   NUMBER;
BEGIN
   ID := :myblock.id;
   refresh;
   POSITION (ID);
END;

Ignoring the reason of this two buttons, my problem is when I call the second button, the first call of POSITION procedure takes too long (because we have a lot of records and POST_QUERY trigger), but the second call of the same procedure is very fast. What is the reason of this behaviour? Is there a fastest way of positioning the focus on the same record selected before?

Littlefoot Littlefoot · Accepted Answer · 2019-04-24T18:17:52

Why is it slow? Who knows ... All we can do is to blindly guess and still be VERY far from actual reason. Too few data to compute. I'd suggest you to run the form in debug mode (as Forms 10g allows it) and trace its execution to see what's going on.

As of faster positioning: consider this approach:

create a parameter, let's call it position

refresh button(s) would:

-- save current position
:parameter.position := :system.trigger_record;

go_block('myBlock');
execute_query;

-- go to previously saved position
go_record(:parameter.position);

(BTW, are you really using Oracle database version 7.x? Whoa!)

Loop trough data block with different time elapsed

1 Answers