Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    16

    Unanswered: Is this query sufficient to handle exceptions

    Hi All,
    I have this store proc to delete from 2 tables. This code would run daily to ensure the tables dont get too large , so after the data has been copied to the target(PARTS) table it deletes the source table (SUPPLY). It does a check to make sure the source table batch number are equal and then deletes if true. Each time data is pulled from the web and copy to source table the batch number increases

    Is the code below good to handle exception?

    CREATE OR REPLACE PROCEDURE SUPPLYDELTABLE
    AS

    DECLARE
    MSG VARCHAR2(200);
    RETCODE NUMBER;
    PARTSBATCH_NUM NUMBER(12);
    SUPPLIESBATCH_NUM NUMBER(12);

    BEGIN

    select max(bat_number)
    into PARTSBATCH_NUM
    from parts;

    select max(bat_number)
    into SUPPLIESBATCH_NUM
    from supply;



    if (PARTSBATCH_NUM = SUPPLIESBATCH_NUM) THEN
    delete from supply;


    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    MSG :=SUBSTR
    (
    SQLERRM, 1,200
    );
    RETCODE :=SQLCODE;
    ROLLBACK;
    dbms_output.put_line
    (

    'ERROR CODE: ' || RETCODE||'ERROR MSG: '|| MSG
    );
    END SUPPLYDELTABLE

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code you posted appears to be Oracle instead of ANSI SQL, so I've moved your thread to the Oracle forum so that it will get more appropriate responses.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >delete from supply;

    above results in ZERO rows remaining in SUPPLY table; but TRUNCATE SUPPLY would be faster & more efficient

    WHEN OTHERS see URL below
    http://www.orafaq.com/forum/t/174329/136107/
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What's the purpose of the whole job? You said that you don't want your tables to be too large. The PARTS table will eventually contain all records that ever existed in the SUPPLY table so it will be "large" anyway.

    Besides, what do you call "too large"?

  5. #5
    Join Date
    Jan 2013
    Posts
    16

    The purpose of this job is to delete from the parts table .

    Quote Originally Posted by Littlefoot View Post
    What's the purpose of the whole job? You said that you don't want your tables to be too large. The PARTS table will eventually contain all records that ever existed in the SUPPLY table so it will be "large" anyway.

    Besides, what do you call "too large"?

    The tables wont be large because before loading into the parts or supply table the data is deleted or truncated. The code to delete from the supply table is new and I wanted verification to see if the code is ok.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The code is OK if it does what is required of it.

    In this case, the whole exception handler should be eliminated.
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by peadove View Post
    . . . before loading into the parts or supply table the data is deleted or truncated. . .
    Very dangerous, what if new rows are added while you are still copying? You may be safer using partitioning.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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