Re: ocp: sql: question?

From: Fahim <fhashmat_at_yahoo.com>
Date: 21 Apr 2003 22:02:01 -0700
Message-ID: <62f7c5ff.0304212102.aa09037_at_posting.google.com>


"ks" <kshathrya_at_attbi.com> wrote in message news:<QSAoa.13050$Si4.6203_at_rwcrnsc51.ops.asp.att.net>...
> i came across this in some place.
> can someone explain:
>
> Q 1.
> 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
>
> Ans: D

All the statement before Alter statement gets commited because of DDL ALTER statement, all the ddl statement issue commit internally, and once you issue commit, all the savepoint would be purged, and initialize, The last statement will be executed but it won't be commit if you exit SQL without issue commit, but there would be no row in the table at that point to update so it doesn't matter you commit or not. I'll pick D if there is no other choice, but all the DMl statements except the last update would be commited and the line ROLLBACK To B will raise the error of Savepoint B never established. Received on Tue Apr 22 2003 - 07:02:01 CEST

Original text of this message