Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Unhappy Unanswered: Help on an exam answer

    I need help understading the following exam question (I'm pasting the exact text):

    The PRODUCT table contains these columns: PRODUCT_ID NUMBER PK
    NAME VARCHAR2(30) LIST_PRICE NUMBER(7,2) COST NUMBER(7,2)

    You logged on to the database to update the PRODUCT table. After your session began, you issued these statements:
    INSERT INTO product VALUES(4,'Ceiling Fan',59.99, 32.45);
    INSERT INTO product VALUES(5,'Ceiling Fan',69.99,37.20);
    SAVEPOINT A;
    UPDATE product SET cost = 0;
    SAVEPOINT B;
    DELETE FROM product WHERE UPPER(name) = 'CEILING FAN';
    ALTER TABLE product ADD qoh NUMBER DEFAULT 10;
    ROLLBACK TO B;
    UPDATE product SET name = 'CEILING FAN KIT' WHERE product_id = 4;

    Then you exit iSQL*Plus. Which of the statements you issued were committed?

    A. only the INSERT statements
    B. only the INSERT statements and the first UPDATE statement
    C. the INSERT statements, the first UPDATE statement, and the DELETE statement
    D. all of the DML operations
    E. none of the DML operations

    I have answered C, but the answers sheet says it is D. I imagined that - since there's no mention of any AUTOCOMMIT setting - and, knowing that DDL statements (ALTER TABLE, in this case) perform implicit COMMITs, that the only moment the session would have gone through a COMMIT would be after the ALTER TABLE statement above.

    Can anyone help me clarifying this one ?

  2. #2
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30
    Any catch on "iSQL *Plus" ? Does iSQL automatically commits statements?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, it doesn't. But exiting (i)SQL*Plus does:
    Code:
    SQL> create table a (a number);
    
    Table created.
    
    SQL> insert into a values (123);
    
    1 row created.
    
    SQL> exit
    Disconnected from Oracle Database <...>
    
    M:\>sqlplus scott/tiger
    
    SQL> select * From a;
    
             A
    ----------
           123

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The reason is because the ALTER command forces a commit and the rollback is ignored, so the answer is D.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30
    What about the last UPDATE, beilstwh - the one AFTER the ALTER TABLE? I thought it would not be commited.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    By default behavoir, the update would be committed when sql*plus exits. If it aborts, a rollback is done. this behavoir has to be turned off if you don't want it to happen. Also I suspect that your instructor was trying to make the point that a DDL command causes a commit.
    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
  •