Home » Other » Training & Certification » A sql question on 047 Exam (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0, Linux)
A sql question on 047 Exam [message #489993] Thu, 20 January 2011 09:20 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #490010 is a reply to message #490004] Thu, 20 January 2011 10:29 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, cookiemonster
Thanks for reply.
2) Do you means it's a feature update in Oracle, so the question might be in an old version of Oracle?
3) I have turned this feature off, but I still can't get the desired result.
SQL> set auto off
SQL> update orders
  2  set customer_id=333;

1 row updated.

SQL> select * from orders;

  ORDER_ID ORDER_DAT CUSTOMER_ID
---------- --------- -----------
       100 10-MAR-07         333

SQL> delete from order;
delete from order
            *
ERROR at line 1:
ORA-00903: invalid table name


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/hr;
Connected.
SQL> select * from orders;

  ORDER_ID ORDER_DAT CUSTOMER_ID
---------- --------- -----------
       100 10-MAR-07         333



Thanks very much.

BR,
Milo
Re: A sql question on 047 Exam [message #490012 is a reply to message #490010] Thu, 20 January 2011 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #490075 is a reply to message #490012] Thu, 20 January 2011 17:31 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, cookiemonster
Thanks for your explainning.
I did both ways you mention, both work.

I found one comment from oracle document site:
Quote:
SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.


So it won't turn off the autocommit in SQL*Plus.
So is there any tool I can issue sql statement and skip SQL* Plus?

Thanks very much,
BR,
Milo
Re: A sql question on 047 Exam [message #490111 is a reply to message #490075] Fri, 21 January 2011 01:11 Go to previous messageGo to next message
Frank
Messages: 7877
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.
Re: A sql question on 047 Exam [message #490112 is a reply to message #490111] Fri, 21 January 2011 01:32 Go to previous message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Frank
I just wanna to know more.
Actually, cookiemonster has provided me the same solution and I have already tried.

Thanks very much for giving me a very clear process to verify each key points of the answer. Smile

BR,
Milo
Previous Topic: What is OCM?
Next Topic: OCP hands on courses
Goto Forum:
  


Current Time: Sat Sep 20 17:47:38 CDT 2014

Total time taken to generate the page: 0.09560 seconds