If the definition of toptab will potentially be different in different environments (which is, of course, a separate issue that needs addressing), your options would generally be to either create an object type that does have a defined order and BULK COLLECT into that or to define a different collection for each column that you specify as the targets of your BULK COLLECT.
For the object approach, you'd define an object
CREATE TYPE toptab_like_object
AS OBJECT (
col1 number,
col2 number,
...
colN number
);
and then use that object rather than the %ROWTYPE in your code
DECLARE
TYPE t_toptab_list IS TABLE OF toptab_like_object;
l_data t_toptab_list;
BEGIN
SELECT toptab_like_object( ... ) -- many columns, with some manipulation, etc.
BULK COLLECT INTO l_data
FROM a,b,c,d
WHERE ... -- join and filter, etc.
END;
You could also define a different collection for each column (you could probably define a much smaller number of collection types)
DECLARE
TYPE t_col1_list IS TABLE OF toptab.col1%TYPE;
TYPE t_col2_list IS TABLE OF toptab.col2%TYPE;
...
TYPE t_colN_list IS TABLE OF toptab.colN%TYPE;
l_col1 t_col1_list;
l_col2 t_col2_list;
...
l_colN t_colN_list;
BEGIN
SELECT ... -- many columns, with some manipulation, etc.
BULK COLLECT INTO l_col1, l_col2, ... l_colN
FROM a,b,c,d
WHERE ... -- join and filter, etc.
END;
Best Answer
Background Info
Transactions are recorded in the log/archive log files.
From there, you can find such things as
SYS_CONTEXT) (*)TIME
The "time of transaction" comes from the SCN. As such, the time resolution is "+/- 3 seconds".
If you need a higher resolution, then the Business needs to accept some schema modifications.
example:
VERSIONS BETWEEN Queries
A VERSIONS BETWEEN query adds the following pseudo columns
"how far back you can go" is dependent on the UNDO tablespace. Which is, usually, 1 hour. YMMV.
To extended the range, use Flashback Data Archive.
LogMiner
Prior to 11g, the only way to access "when a transaction occurs" is to use LogMiner.
This can be done through the DBMS_LOGMNR Package.
Flashback Data Archive
11g introduced a feature called Flashback Data Archive.
This feature
This feature is included with all editions of Oracle starting with 11.2.0.4. Prior to 11.2.0.4, you have to have a specific EE add-on ($$$$).
(*) With 12c, Flashback Data Archive can optionally record the values of SYS_CONTEXT also.
Other Links
Here are some additional URLs regarding Flashback Data Archive