Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: stor proc to transfer and delete recs to remote db

    Scenario: Based on static date range (1 yr) lookup on TableA, find associated records on TableB and TableC. For each table, all recs found need to be copied to corresponding tables on remote database, and the original records deleted.

    DB coding isn't my primary.. Any comment appreciated - technique, optimize. This will be run for 100's to potentially 10,000's records, so I think bulk processing would be ruled out? Here's a cursor approach:

    Code:
    PROCEDURE proc_archiveDelete
    IS  
        record_typeA    tbl_A%ROWTYPE;
        record_typeB    tbl_B%ROWTYPE;
        record_typeC    tbl_C%ROWTYPE;
        
        CURSOR a IS 
            SELECT *
            FROM tbl_A
            WHERE DT_INSERT < (SYSDATE - 365)
            FOR UPDATE;
        
        CURSOR b IS
            SELECT * 
            FROM tbl_B
            WHERE field_Id = record_typeA.field_Id
            FOR UPDATE;
            
        CURSOR c IS
            SELECT *
            FROM tbl_C
            WHERE fieldId = record_typeA.field_Id
            FOR UPDATE;
    
        BEGIN
            OPEN a;
            LOOP 
            FETCH a INTO record_typeA; 
                BEGIN
                    OPEN b;
                    LOOP
                    FETCH b INTO record_typeB;                
                        BEGIN
                            OPEN c;
                         	LOOP
                            FETCH c INTO record_typeC;
                         	INSERT INTO Tbl_C@remoteTable VALUES record_typeC;
                            DELETE FROM Tbl_C WHERE CURRENT OF c;
                            END LOOP;
                            CLOSE c;
                        EXCEPTION  
                            -- handle errors
                        END;            
                    INSERT INTO Tbl_B@remoteTable VALUES record_typeB;
                    DELETE FROM Tbl_B WHERE CURRENT OF b;
                    END LOOP;
                    CLOSE b;
                EXCEPTION
                  -- handle errors
                END;        
                INSERT INTO Tbl_A@remoteTable VALUES record_typeA;            
                DELETE FROM Tbl_A WHERE CURRENT OF a;        
           END LOOP;
           CLOSE a;           
        EXCEPTION
                  -- handle errors
        END proc_archiveDelete;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR replace PROCEDURE Proc_archivedelete 
    IS 
    BEGIN 
        INSERT INTO tbl_c@remotetable 
        SELECT * 
        FROM   tbl_c 
        WHERE  fieldid IN (SELECT fieldid 
                           FROM   tbl_a 
                           WHERE  dt_insert < ( SYSDATE - 365 )); 
    
        INSERT INTO tbl_b@remotetable 
        SELECT * 
        FROM   tbl_b 
        WHERE  fieldid IN (SELECT fieldid 
                           FROM   tbl_a 
                           WHERE  dt_insert < ( SYSDATE - 365 )); 
    
        INSERT INTO tbl_a@remotetable 
        SELECT * 
        FROM   tbl_a 
        WHERE  dt_insert < ( SYSDATE - 365 ); 
    
        DELETE FROM tbl_a 
        WHERE  dt_insert < ( SYSDATE - 365 ); 
    
        DELETE FROM tbl_b 
        WHERE  fieldid IN (SELECT fieldid 
                           FROM   tbl_a 
                           WHERE  dt_insert < ( SYSDATE - 365 )); 
    
        DELETE FROM tbl_c 
        WHERE  fieldid IN (SELECT fieldid 
                           FROM   tbl_a 
                           WHERE  dt_insert < ( SYSDATE - 365 )); 
    END proc_archivedelete; 
    
    /
    NEVER do in PL/SQL what can be done in plain SQL!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2012
    Posts
    2
    Clean approach I'll add to my list - thanks. Only thing is that one INSERT call is throwing PL/SQL: ORA-22992: cannot use LOB locators selected from remote tables Have to research that for moving data to remote table involving a CLOB.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •