Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: Transaction management in PL/SQL

    Hi,

    Not sure if this is possible but what I'm trying to do is create a procedure that selects everything from table A into table B then truncate table A, but I want to ensure there is no possibility that I miss an insert into table A and lose the data when I truncate.

    I am looking at this sort of idea:

    CREATE OR REPLACE procedure tester as
    begin
    set transaction read only
    insert into table_b select * from table_a;
    truncate table table_a;
    end;
    /

    Is the theory OK or ??

    Tks,
    Breen.

  2. #2
    Join Date
    Aug 2003
    Posts
    41
    I think you can add an exception section to capture any errors.

    like,


    Exception
    When Others then
    Rollback;


    Also, an initial count of tableA and a count of inserted records in tableB before the truncate statement can make sure you dont miss a thing!.

    -Sunil.

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanks Sunil,

    Decided to avoid it all and set it up as a CRON job.

    Cheers,
    Breen.

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    If you' ll decide to do this in PL/SQL after all, beware of any foreign keys in table a. If you want to truncate the table using pl/sql the statement will fail if any keys are referencing this table. They have to be disabled first.

    Good luck.
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Transaction management in PL/SQL

    Originally posted by Breen
    Hi,

    Not sure if this is possible but what I'm trying to do is create a procedure that selects everything from table A into table B then truncate table A, but I want to ensure there is no possibility that I miss an insert into table A and lose the data when I truncate.

    I am looking at this sort of idea:

    CREATE OR REPLACE procedure tester as
    begin
    set transaction read only
    insert into table_b select * from table_a;
    truncate table table_a;
    end;
    /

    Is the theory OK or ??

    Tks,
    Breen.
    IIRC, Create, truncate automaitcally commit the changes as they are DDL transactions as opposed to DML.

    My suggestion is to put in a statement similar to
    if Select count(*) as A_Count from Table_a <> Select count(*) as B_Count from Table_b then
    exit
    else
    commit;
    truncate table_a
    end if;

    Baer with me on the procedure language. I haven't done may oracle stored procedures.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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