Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Advice needed on PL/SQL code

RE: Advice needed on PL/SQL code

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 30 Jan 2003 05:43:56 -0800
Message-ID: <F001.0053E6FF.20030130054356@fatcity.com>


Beth,

Since your archiving process involves many tables, I think the best approach for you would be as follows:

Create a work table that will hold the primary key value for the master table.

the first step of your process would involve selecting the records to be archived from the master table, and storing the PK values into this work table - very simple and straightforward.

the next step would be to have a cursor read this work table, getting the PK value for the master table. you could then process the move in a transactional way - inserting the values into the archive table and deleting from the master table for each work record processed. if you think about it, the work table has no update transactions running against it, and the bulk of the process is running in a transactional mode - read a record, read the data to be moved, move it, and delete it. rinse , lather and repeat.

at the end of the process, you simply truncate the work table. it is easily restartable - simply skip the step where you populate the work table, and start step 2.

I've done this like this many times. it's more work, but will never fail!

good luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Thursday, January 30, 2003 12:34 AM To: Multiple recipients of list ORACLE-L

Thanks all, this is helpful information for my boss, who wants to schedule a procedure to bulk copy data between tables on a regular basis.

For my own problem, I should have been more specific in my example, but I was thinking more about my boss' issue than my own. (What a great employee I am!) I want to select rows older than five years from an original table, insert them into an archive table, delete from the original, and do the same for related tables based on the original's primary key. The only way I can think of to do this is to loop through a cursor, performing actions on all related tables using one 'master' row at a time, and periodically issuing either a commit or rollback. This code will be delivered to DBAs with varying skill levels, so I need to make it re-startable if it fails for any reason. I want to play it safe and commit every thousand rows or so because the sizes of the tables vary significantly between databases.

Charlie Mengler pointed out that I could easily end up with ORA-01555 errors using my original example, which I had a hard time understanding until I re-read Steve Adams' tip on his website (thanks to whoever posted the link a few days ago). Since I will be deleting the original records after successfully inserting to archive tables, I believe I can periodically issue a commit and then close and re-open the cursor.

Is anyone else archiving old data this way? Have I overlooked a better way?

Thanks again,
Beth

-----Original Message-----

Sent: Wednesday, January 29, 2003 9:35 AM To: Multiple recipients of list ORACLE-L

Robert,

Thanks for point that out. Also, if there is index on the table, redo will still be generated for index.

Richard

-----Original Message-----

Sent: Tuesday, January 28, 2003 10:54 PM To: Multiple recipients of list ORACLE-L

/*+ append */ alone does not prevent redo generation. Only with NOLOGGING in the SQL
will redo generation be suppressed.

Cheers!

RF

-----Original Message-----

Sent: Tuesday, January 28, 2003 6:29 PM
To: Multiple recipients of list ORACLE-L

If it's a lot of rows and looks like you are archiving it to a historical table, you could do:

insert /*+ append */ into target_table select * from source_table;

this will do a direct path insert, so it uses space above HWM. It doesn't generate redo.

Richard

-----Original Message-----

Sent: Tuesday, January 28, 2003 6:09 PM
To: Multiple recipients of list ORACLE-L

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

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

Author: Richard Ji
  INET: richard.ji_at_mobilespring.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).

--

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

Author: Robert Freeman
  INET: robertgfreeman_at_yahoo.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).
--

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

Author: Richard Ji
  INET: richard.ji_at_mobilespring.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).

--

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

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

Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us
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 Thu Jan 30 2003 - 07:43:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US