Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question

2 Answers 2

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;
share|improve this answer
    
I just edited in some additional info to show how I want to insert the data. I like the idea of not having to explicitly call out each column, and it seems like I would need to do so either in the select or in the insert. –  Glenn Moss Mar 27 '12 at 19:04
    
I'm wondering what kind of approaches I can take to managing the definitions between environments. It's not that the table will really have a different structure, but through incremental additions of columns (which happens not infrequently) they may get added in a slightly different order. –  Glenn Moss Mar 27 '12 at 19:05
    
@GlennMoss - Why are columns getting added in different orders in different environments? Normally, you'd have a script that you ran in development to add a few new columns that would be run in test, then staging, then prod so that all the environments have exactly the same definition of the table. And, periodically, you'd refresh the lower environments with clones of production. –  Justin Cave Mar 27 '12 at 19:20
    
I'm not sure how it would happen. I am seeing a discrepancy between some of our dev environments. These would be easy to fix. I don't know if we have these kinds of problems in QA or prod environments. I'm trying to explore the problem and see if there's a solution that avoids the dependency on specific column ordering. –  Glenn Moss Mar 27 '12 at 19:27
1  
TBH, you'd be better off ensuring your dev environments are in-line with QA & prod. I certainly wouldn't go down the path of having to modify production code because of lax change management. –  Phil Mar 27 '12 at 19:33

In that case you could use an explicit column list in all SELECT and INSERT :

DECLARE
  cursor cs is select col1, col2... from toptab;
  TYPE t_toptab_list IS TABLE OF cs%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 (select col1, col2... from toptab) VALUES l_data(i);
                     -- ^ same order as cursor 
END;
/
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.