How to retain block position after a query -oracle forms

Question

I have an oracle form with a data_block which displays 25 items.

On the form I have a scrollbar and a 'delete' button. When an item in the data block is selected, and the 'delete' button is pressed, it deletes the selected item from the database and then executes the data_block query.

By default, this returns the user to the top of the list.

I am trying to navigate to the record just before the one that is deleted from the list.

This can be done using the GO_RECORD(number) Built in Function(BIF) (assuming number is the saved value of :System.cursor_record).

And this is where I run into a problem. The GO_RECORD BIF will bring the record to the top or bottom of the displayed list of items. This can cause the list to shift up 20 items without warning.

i.e. For example Records 23 - 47 from the data_block are being displayed, and record 33 is selected. If record 33 is deleted and we use the function GO_RECORD(32), then the records diplayed will be 32-56 (effectively shifting the list down 9 records).

I'm assuming that in order to avoid this shift I will need to have some way of detemining the position of the record in the display (as opposed to the data_block).

Does anyone know if this functionality exists?

Or does anyone have another approach that might get me the same result?

Solution 1

-- save top record

 l_top_rec:= GET_BLOCK_PROPERTY('EXP_DETAIL_BLK', TOP_RECORD);
 l_cur_rec:= :SYSTEM.CURSOR_RECORD;

-- your actions

 execute_query; // or othres actions...

-- set top record

go_block(block_name);
--
first_record;
loop
  exit when GET_BLOCK_PROPERTY(block_name, TOP_RECORD) = l_top_rec;
  next_record;
end loop;
go_record(l_top_rec);
--
loop
  exit when :SYSTEM.CURSOR_RECORD = l_cur_rec or :SYSTEM.LAST_RECORD = 'TRUE';
  next_record;
end loop; 

Solution 2

first create this procedure as a program unit

            PROCEDURE SYNC_BLOCK
            -----------------------------------------------------------------------*
            --  Synchronizes the display of any scrollable block.
            --  After running an edit that loops through all records, this will
            --  restore the block's display so that the same top record is again
            --  at the top of the block's display.
            --  Blk is the name of the block.
            --  Rec_Num is the desired target current record.
            --  Top_Rec is the original Top Record of the block captured
            --  before the looping process began.
            (BLK      VARCHAR2,
            REC_NUM  NUMBER,
            TOP_REC  NUMBER) IS
            BLK_ID    BLOCK;
            TOP_NEW   PLS_INTEGER;
            REC_N     PLS_INTEGER;
            --
            Procedure Check_success is begin
            If not form_success then
            Raise form_trigger_failure;
            End if;
            End Check_success;
            Procedure Go_Rec(rec_num number) is begin
            Go_Record(Rec_num);
            Check_Success;
            End Go_Rec;
            BEGIN
            BLK_ID := FIND_BLOCK(BLK);
            IF ID_NULL(BLK_ID) THEN
            Message('  U72_GO_REC_SYNC_BLOCK: CANNOT FIND BLOCK '''||BLK||'''');
            Raise Form_trigger_failure;
            END IF;
            IF BLK <> :SYSTEM.CURSOR_BLOCK THEN
            GO_BLOCK(BLK);
            Check_Success;
            END IF;
            IF :SYSTEM.CURSOR_RECORD <> REC_NUM THEN
            GO_REC(REC_NUM);
            END IF;
            -- may need to re-set the display to the rows originally shown
            TOP_NEW := GET_BLOCK_PROPERTY(BLK_ID, TOP_RECORD);
            IF TOP_REC <> TOP_NEW THEN
            IF TOP_REC < TOP_NEW THEN
            IF :SYSTEM.CURSOR_RECORD <> TOP_REC THEN
            GO_REC(TOP_REC);
            END IF;
            ELSE
            REC_N := GET_BLOCK_PROPERTY(BLK_ID, RECORDS_DISPLAYED)
            + TOP_REC - 1;
            IF :SYSTEM.CURSOR_RECORD <> REC_N THEN
            GO_REC(REC_N);
            END IF;
            END IF;
            SYNCHRONIZE;
            -- Found that Sync caused focus change to different block. Fix here.
            IF BLK <> :SYSTEM.CURSOR_BLOCK THEN
            GO_BLOCK(BLK);
            Check_Success;
            END IF;
            IF :SYSTEM.CURSOR_RECORD <> REC_NUM THEN
            GO_REC(REC_NUM);
            END IF;
            END IF;
            -- can't go_rec to NEW record, so need to test here
            IF  :SYSTEM.LAST_RECORD = 'TRUE'
            AND REC_NUM = 1 + :SYSTEM.CURSOR_RECORD THEN
            NEXT_RECORD;
            Check_Success;
            END IF;
            --
            END SYNC_BLOCK;

second this below five lines of code do exact what you want

xx:=GET_BLOCK_PROPERTY('blk',TOP_RECORD);
xxx:=GET_BLOCK_PROPERTY('blk',CURRENT_RECORD );
go_block('blk');
execute_query();
SYNC_BLOCK('blk',xxx,xx);

please do not hesitate to contact me if you require further information