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?
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