Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    37

    Unanswered: Successfull Query Exxecution Check

    Hi
    My Sql statement is delete from employee, it will delete all the records from the employee table.

    What i want to do is If the ablove sql runs successfull then its fine other wisw it should raise an error.

    Something like that

    IF (SQL query Executed Successfull)
    ITS ok
    else
    raise an exception XYZ

    Exception
    When XYZ then
    rollback;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >My Sql statement is delete from employee, it will delete all the records from the employee table.
    truncate table would be more efficient.
    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
    Mar 2012
    Posts
    37
    With the help of truncate the rollback will not work,,,

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If the DELETE throws an error it will be rolled back automatically.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Mar 2012
    Posts
    37
    One more question i am using
    begin
    FOR x IN (select table_name From All_Tables Where Owner = 'MASTER' and table_name like '%SMS%')
    LOOP
    <>
    END LOOP;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    raise_application_error (-1,'SELECT STATEMENT DOES NOT RETURN ANY ROWS');
    end;
    But when select statement do not return any data the exception is not raised..

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A cursor loop will not generate an error if nothing is found, The loop will just bee skipped. to set the error do something like this.

    Code:
    declare
    flag varchar2(1);
    begin
    flag := 'N';
    FOR x IN (select table_name From All_Tables Where Owner = 'MASTER' and table_name like '%SMS%')
    LOOP
    flag := 'Y';
    <>
    END LOOP;
    if flag = 'N' then
        raise_application_error (-1,'SELECT STATEMENT DOES NOT RETURN ANY ROWS');
    end if;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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