I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like:
DECLARE
TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE;
l_data t_toptab_list;
BEGIN
SELECT ... -- many columns, with some manipulation, etc.
BULK COLLECT INTO l_data
FROM a,b,c,d
WHERE ... -- join and filter, etc.
-- EDIT:
FORALL i IN 1..l_data.count
INSERT INTO toptab VALUES l_data(i);
END;
In order for the BULK COLLECT
to work properly, I have to list the columns in the SELECT
clause in exactly the same order as they are declared in the toptab
table. So the problem is that this code would be run in multiple environments. How do I either: (a) guarantee that all environments have the exact same column ordering, or (b) explicitly specify which columns go to which record elements but still use a bulk bind?
I understand that (a) could be done through strict schema management procedures. But let's say that's not an option here. In fact the different schemas may already have different column orderings. Can I reorder the columns without rebuilding the entire table? (Prohibitive.)
I know that (b) can be accomplished without the BULK COLLECT
by explicitly selecting into each record element, but I really want to do it in bulk.
Any ideas?
Edit: I want to bulk insert into toptab
with the data I've collected. I separate these two steps because I need to batch the inserts into smaller transactions. (We use streams and find smaller transactions keep things moving smoother. However, batching each transaction is still much faster than doing N individual inserts.)
Best Answer
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 andBULK COLLECT
into that or to define a different collection for each column that you specify as the targets of yourBULK COLLECT
.For the object approach, you'd define an object
and then use that object rather than the
%ROWTYPE
in your codeYou could also define a different collection for each column (you could probably define a much smaller number of collection types)