Advice needed on PL/SQL code

From: Beth Wells <beth_wells_at_modusmedia.com>
Date: Tue, 28 Jan 2003 15:09:19 -0800
Message-ID: <F001.0053CA49.20030128150919@fatcity.com>

Hi all,

Please forgive this newbie question; I'm just getting started with PL/SQL. I want to write a procedure to copy all rows from one table to another one with an identical structure. The table has many rows so I'm committing every thousand records (error handling to be added later). The table has 50+ columns and (if possible) I'd like to avoid listing them all in the VALUES clause. I'm not just being lazy - I hope to deploy this code to several databases and the source table, while having the same name, may have differences in the columns. I want to do something like this:

DECLARE
   count_ NUMBER;
   CURSOR get_archive_records IS

      SELECT * FROM customer_order_table; BEGIN
   count_ := 0;
   FOR rec_ IN get_archive_records LOOP

      INSERT INTO customer_order_archive
      VALUES (rec_.*);     -- OBVIOUSLY, THIS DOES NOT WORK
      count_ := count_ + 1;
      IF MOD(count_,1000) = 0 THEN
         COMMIT;
      END IF;

   END LOOP;
   COMMIT;
END; Is there a way to accomplish this? Or is my whole approach all wrong?

Thanks in advance,
Beth

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Beth Wells
  INET: beth_wells_at_modusmedia.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 28 2003 - 17:09:19 CST