| A sql question on 047 Exam [message #489993] |
Thu, 20 January 2011 09:20  |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
Hi, guys
I have question on one question on 047 exam:
The question is :
Quote:
207. You executed the following SQL statements in the given order:
CREATE TABLE orders
(order_id NUMBER(3) PRIMARY KEY,
order_date DATE,
customer_id number(3));
INSERT INTO orders VALUES (100,'10mar2007', 222);
ALTER TABLE orders MODIFY order_date NOT NULL;
UPDATE orders SET customer_id=333;
DELETE FROM order;
The DELETE statement results in the following error:
ERROR at line 1:
ORA-00942: table or view does not exist
What would be the outcome?
A. All the statements before the DELETE statement would be rolled back.
B. All the statements before the DELETE statement would be implicitly committed within the session.
C. All the statements up to the ALTER TABLE statement would be committed and the outcome of UPDATE statement would be rolled back.
D. All the statements up to the ALTER TABLE statement would be committed and the outcome of the UPDATE statement is retained uncommitted within the session.
Answer: D
Here is the exactly what I am doing the same operation as the question:
SQL> select banner from sys.v_$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table orders
2 ( order_id number(3) primary key,
3 order_date DATE,
4 customer_id number(3));
Table created.
SQL> insert into orders values(100,'10-mar-2007',222);
1 row created.
SQL> alter table orders modify order_date not null;
Table altered.
SQL> desc orders;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER(3)
ORDER_DATE NOT NULL DATE
CUSTOMER_ID NUMBER(3)
SQL> update orders set customer_id=333;
1 row updated.
The table I created is orders, but now it delete the table order(never exsits in db)
SQL> delete from order;
delete from order
*
ERROR at line 1:
[color=red]ORA-00903: invalid table name[/color]
It seems that the error id is not the one listed in the question.
After reconnect the sesssion the record still exists:
SQL> disconn;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn hr;
Enter password:
Connected.
SQL> select * from orders;
ORDER_ID ORDER_DAT CUSTOMER_ID
---------- --------- -----------
100 10-MAR-07 333
I have execute those statements in my sqlplus , but I can't reproduce this scenario and I am pluzzed.
I have few more questions on this problem:
1. In statement "delete from order" , is the table order which not exist in database correct?
2. I did exactly the same as the statement sequence, will other environment cost the different error id or something else?
3. If I am not misunderstanding, when I disconnect from the current session, the customer_id should roll back to the original value as it's not commit and the session is lost.
But after I disconnect the session, the new value still there!
I can't fix why this differences generate, could someone kindly help me analyze this?
Thanks very much.
BR,
Milo
[Updated on: Thu, 20 January 2011 10:56] by Moderator Report message to a moderator |
|
|
|
| Re: A sql question on 047 Exam [message #490004 is a reply to message #489993] |
Thu, 20 January 2011 09:59  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Are asking if it's right that there should be no table called order? Yes.
2) Different error_id is due to order being a reserved word. You aren't allowed to create a table of that name either. Possibly that error takes precedence over the table does not exist error in more recent versions of oracle. I wouldn't worry about it.
3) Sqlplus has an auto-commit ability, you can turn it off.
|
|
|
|
|
|
| Re: A sql question on 047 Exam [message #490012 is a reply to message #490010] |
Thu, 20 January 2011 10:46 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
2) Possibly, maybe the author just made a mistake. If you use a different table name you get the error message in the question and the end result is the same either way.
3) looking up the commands that set auto applies to autocommit after each statement. You're getting autocommit on disconnect, not sure how to turn that off. You could just issue a rollback before the disconnect, or use a seperate session to see what has been committed at each stage.
|
|
|
|
|
|
| Re: A sql question on 047 Exam [message #490111 is a reply to message #490075] |
Fri, 21 January 2011 01:11 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Why do you think you can only test this by exiting sqlplus?
Actually it can be tested better by opening another sqlplus session.
Execute the test in session 1, perform checks in session2:
Execute everything up until (but not including) the delete statement.
check the state of the data from session 2. You should see the non-updated values, which implies that everything up until the update was committed.
Perform the delete in session1.
Check the state of the data from session 2. You still see the same data, which means that session 1 did not commit the update.
Perform a commit in session1.
Check the state of the data from session 2. You will see the updated data, which means that the delete in sssion 1 did NOT rollback the update.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
