Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: 2 x insert and rollback if something was wrong

    How can I make ROLLBACK in that case:

    Code:
    1. INSERT INTO.......
    2. INSERT INTO.......
    
    if one or two of two INSERT was wrong make ROLLBACK

    ??

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I don't see the problem.
    Why can't you simply issue a ROLLBACK?

    How/where do you execute the INSERT statements?
    Is that inside a stored procedure?
    Or a Java/Ruby/PHP/.Net/COBOL/C++ program?
    Or a script for SQL*Plus?

  3. #3
    Join Date
    Dec 2008
    Posts
    53
    Script for sql*plus - I study pl/sql and I would like to know how can I solve this problem. But maybe not 2 x INSERT but 3 x INSERT

    I can't simply make ROLLBACK because:

    1. INSERT ok
    2. INSERT error
    3. INSERT ok

    ROLLBACK - so ROLLBACK is only for 3. INSERT, how can I abort 1. INSERT ?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Wrong! Rollback will affect all changes made after the last commit. If these 3 INSERT statements were the only ones, insert #1 will be rolled back; insert #2 failed (so there were no changes to be rolled back); insert #3 will be rolled back.

  5. #5
    Join Date
    Dec 2008
    Posts
    53
    But I have autocommit in my database. What in that case ?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chomik
    What in that case ?
    In that case you have to turn it off.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by chomik
    But I have autocommit in my database.
    Are you talking about sqlplus SET System Variable named AUTOCOMMIT?
    Quote Originally Posted by chomik
    What in that case ?
    What about set it OFF?

    By the way, how are you planning to find out whether any INSERT failed after their execution?
    As you did not answer shammat's questions, it is hard to advise more.

  8. #8
    Join Date
    Dec 2008
    Posts
    53
    I was talking about autocommint not in sqlplus but in database. It isn't possible to set it OFF. I am not an admin.

    When some INSERT fail it will be an exception so:

    EXCEPTION
    WEHEN OTHERS THEN
    ROLLBACK;

    ?????

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chomik
    I was talking about autocommint not in sqlplus but in database.
    The "database" (= server) does not have an autocommit setting at all (that completely defeats the idea of a relational database)
    Quote Originally Posted by chomik
    It isn't possible to set it OFF. I am not an admin.
    The only place to turn it off is on the client side (= SQL*Plus). You don't need to be an admin to do that.

    Quote Originally Posted by chomik
    When some INSERT fail it will be an exception so:

    EXCEPTION
    WEHEN OTHERS THEN
    ROLLBACK;
    This is a PL/SQL block and it will do what you want.
    Are you writing a stored procedure?

    Quote Originally Posted by flyboy
    Are you talking about sqlplus SET System Variable
    That is not a system variable. That is a SQL*Plus setting.

  10. #10
    Join Date
    Dec 2008
    Posts
    53
    Eh...... people - ok - I am writing anonymous block for APEX (I hope you know what it is - it is a new technology of Oracle to creating Internet pages), I have in that autocommit and I can't (or I don't want) set it off.

    Could somebody tell me how can I make rollback if I have autocommit ?

    PS. I think now nobody will answer when I used strange word 'APEX'
    Last edited by chomik; 01-07-09 at 14:10.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chomik
    I have in that autocommit and I can't (or I don't want) set it off.
    If autocommit is turned on, there is no way to rollback any successful change, because any change is automatically committed. That's what autocommit means.

    If you want to use rollback you have to turn off autocommit.

    Quote Originally Posted by chomik
    PS. I think now nobody will answer when I used strange word 'APEX'
    Well it's a programming enviroment which whoever answer must be used to.
    Especially things like turning off autocommit depends largely on the programming environment.
    If you don't know how to use e.g. Uniface would you still answer a post from someone who has a problem using SQL statements in Uniface?
    Or would you know how to manage transactions in Uniface, COBOL, Progres, ACMS or Forte?

  12. #12
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    APEX (Application Express) is neither new nor strange. It is a marketing gimmick for Oracle's HTML DB product. Sure it's been updated, but just a new version of an old product.

    As for being able to do a rollback in an environment with AutoCommit turned on, think about what you are asking. What you are asking is "How do I rollback a transaction that has been committed?" The answer is you can't.

    From the APEX concept guide:

    Autocommit. If available, click the Autocommit check box to enable autocommit and disable transactional commands. See "About Transactionsin SQL Commands".

    Using Autocommit disables the transactional features of the database.

  13. #13
    Join Date
    Dec 2008
    Posts
    53
    This is strange. In database MySQL I have also autocommit and I make that what I want:
    Code:
    START TRANSACTION;
    INSERT INTO .................
    ROLLBACK;
    and Oracle in that case is worse :/

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chomik
    This is strange. In database MySQL I have also autocommit and I make that what I want:
    START TRANSACTION disables autocommit.
    Quote Originally Posted by manual
    To disable autocommit mode for a single series of statements, use the START TRANSACTION statement
    Quoted from: http://dev.mysql.com/doc/refman/5.0/en/commit.html

  15. #15
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by chomik
    and Oracle in that case is worse :/
    Depends on user habits: as shammat pointed out each database has slightly different terminology and approach. On the other hand, in Oracle you do not need to specify START TRANSACTION in any mode - it is started automatically after previous transaction end (COMMIT or ROLLBACK).

    Just in case that you are trying something like described in Roland Bouman's blog: MySQL: Transactions and Autocommit, then you may insert multiple rows in one INSERT statement even in Oracle (at least in 10gR2), although not very comfortable:
    Code:
    INSERT ALL
      INTO <table_name> (<column list>) VALUES(<value list>)
      INTO <table_name> (<column list>) VALUES(<value list>)
      INTO <table_name> (<column list>) VALUES(<value list>)
    SELECT 1 FROM dual;

Posting Permissions

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