1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

simplest ideas for exception in transactons

Discussion in 'General' started by monkey, Apr 21, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I tried to write the most simple transaction (and this one is the first I made in my life):

    BEGIN TRANSACTION;

    drop table if exists p;
    drop table if exists n;

    create table p(
    idp integer primary key,
    pname text
    );

    create table n(
    nn text primary key
    );

    commit;

    begin transaction;
    insert into p values(1,'aa');
    insert into p values(2,'bb');
    insert into p values(3,'cc');
    commit;

    begin transaction;
    insert into n values('');
    commit;

    select * from p;
    select * from n;



    begin;
    savepoint sp1;
    update p
    set pname='zzz'
    where pname='bb';
    commit;


    select * from p;

    begin;
    exception
    when others then
    rollback to sp1
    raise_application_error(-20100, SQLERRM);
    END;

    What I try, is to find ide for EXCEPTION.
    I tried with:
    begin;
    exception
    when pname='aaa' then
    rollback to sp1
    raise_application_error(-20100, SQLERRM);
    END;


    and:

    begin;
    exception
    when pname='bb' then
    rollback to sp1
    raise_application_error(-20100, SQLERRM);
    END;

    But did not work (www.compileonline.com)

    What I search is JUST MOST SIMPLE IDEA FOR EXCEPTION, for one table db I made.

    If such, please, would u be so dear to suggest?
    MANY THANKS!!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    To write an correct example to you, it is necessary to consider model of your processed data, logical and physical models of your data.

    You, in the elementary case you use the single SP1 which will "be updated" after each SAVE POINT given out by you.

    You as need to define according to your logic of the task - exception handling model.

    As pay attention on your processor of an exceptional situation:
    compare these two options....

    For example :

    1)

    Code (SQL):


    DROP TABLE p;
    DROP TABLE n;
    CREATE TABLE p(
    idp INTEGER PRIMARY KEY,
    pname varchar2(100 CHAR)
    );

    CREATE TABLE n(
    idn INTEGER PRIMARY KEY,
    nname varchar2(100 CHAR)
    );

    BEGIN
       
    SAVEPOINT SP1;    
    INSERT INTO p VALUES(1,'aa');
    INSERT INTO p VALUES(2,'bb');
    INSERT INTO n VALUES(1,'aa');
    INSERT INTO n VALUES(3,'сс');

    -- may be some work
    SAVEPOINT SP1;    
    INSERT INTO p VALUES(4,'dd');
    INSERT INTO p VALUES(5,'ee');
    INSERT INTO n VALUES(4,'dd');
    INSERT INTO n VALUES(5,'ee');

    -- may be some work
    SAVEPOINT SP1;
        INSERT INTO p VALUES(4,'dd');
        INSERT INTO n VALUES(4,'dd');
       
    -- may be some work

    INSERT INTO p VALUES(6,'dd');
    INSERT INTO n VALUES(6,'ee');

    -- may be some work
    commit;

    exception
        WHEN others   THEN
    -- in this model of processing - this code is superfluous
            ROLLBACK TO sp1;
    -- pay attention on result on your processor of  handling  exceptional situation
            raise_application_error(-20100,sqlerrm);
    END;        
    /

    SELECT * FROM p;
    SELECT * FROM n;
     

    2)

    Code (SQL):

    --
    DROP TABLE p;
    DROP TABLE n;
    CREATE TABLE p(
    idp INTEGER PRIMARY KEY,
    pname varchar2(100 CHAR)
    );

    CREATE TABLE n(
    idn INTEGER PRIMARY KEY,
    nname varchar2(100 CHAR)
    );

    BEGIN
        BEGIN
            SAVEPOINT SP1;    
            INSERT INTO p VALUES(1,'aa');
            INSERT INTO p VALUES(2,'bb');
            INSERT INTO n VALUES(1,'aa');
            INSERT INTO n VALUES(3,'сс');

            SAVEPOINT SP1;    
            INSERT INTO p VALUES(4,'dd');
            INSERT INTO p VALUES(5,'ee');
            INSERT INTO n VALUES(4,'dd');
            INSERT INTO n VALUES(5,'ee');


            SAVEPOINT SP1;
            INSERT INTO p VALUES(4,'dd');
            INSERT INTO n VALUES(4,'dd');
               

            INSERT INTO p VALUES(6,'dd');
            INSERT INTO n VALUES(6,'ee');
        exception   WHEN others   THEN  ROLLBACK TO sp1;
        END;

    -- some work
    commit;
    END;
    /
    SELECT * FROM p;
    SELECT * FROM n;
     
     
    monkey likes this.
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    First, I have to appoligise, I did nt say before:

    I am working on www.compileonline.com
    and I created table n to be EMPTY just to make an empty row between outputs in that compiler (my "original" idea, cause otherwise the compiler "stiks" tables got by select together, and one does not see clearly where one table finishes and another starts:):blush)


    I ment the transaction with just one table (table p), which was ment to have 3 rows and I intended to add another 2 rows in transaction.

    My question was actually: how to create an IDEA for the ecxeption, WHICH WOULD BE OTHER THEN "OTHERS", using just one table in the whole db, (to make it easy!) because I wanted to see how the sp "dissapears" using rollback!!!

    /"others " means "any" in that case, == ANY exception ,but I wanted to see exmple for at lease ONE possible exception!!!
    Please, if somone has inspiratin, JOIN US!!!
    MANY THANKS!!!