Home » SQL & PL/SQL » SQL & PL/SQL » Possible bugs in a book
| Possible bugs in a book [message #6702] |
Fri, 02 May 2003 04:39  |
Kenton Cronberg
Messages: 5
Registered: May 2003
|
Junior Member |
|
|
Hi!
I just received a message from the Oracle FAQ Team that they apologize for not being able to answer any question at this time and I am to post my question on the Oracle FAQ Message Forums at http://www.orafaq.net/faqmessb.htm. So I am posting the below question for the second time. Did I do something wrong with the question the first time?
I am preparing for the first certificate examination in my Oracle DBA certified training program. I have an excellent SYBEX book named 'OCP: Oracle8i DBA SQL and PL/SQL Study Guide'. I think I found at least one bug
in the book. If I tell you the possible error and page number, will you be able to let me know if I am right and what it should be? If so, then I will get back to you shortly.
Thank you!
|
|
|
|
| Re: Possible bugs in a book [message #6704 is a reply to message #6702] |
Fri, 02 May 2003 05:37  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Kenton,
I don't know why you were sent the message that you received. However, let me see if I can explain about these forums. The people answering your questions, like me, are just fellow Oracle users. So, unless someone happens to have the same book, the page number tells us nothing. However, if you will type the item in question in your post, so that we know what you are talking about, then we may be able to help. If we know what the issue is, then we can possibly give you our opinions, post examples that prove or disprove them, and/or provide links to Oracle on-line documentation on the subject. If you then feel that you are correct, then you can contact the publisher of the book.
Barbara
|
|
|
|
| Re: Possible bugs in a book [message #6735 is a reply to message #6702] |
Sat, 03 May 2003 02:25 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In response to your e-mail, which stated:
"Barbara,
Thank you for your reply. Good idea that I just type the item in question. Here is one problem according to one page in a book:
Problem:
Consider the following PL/SQL block and choose the most appropriate answer:
DECLARE
CURSOR c_emp IS SELECT empno, ename
FROM emp
WHERE salary<3500
FOR UPDATE;
BEGIN
FOR r_emp IN c_emp LOOP
UPDATE emp
SET salary=salary*1.25
WHERE CURRENT OF c_emp;
END LOOP;
END;
A. The block will give a salary raise by 25 percent for each employee whose salary is below 3500.
B. The block will fail because there is no column name specified in the FOR UPDATE clause.
C. You cannot use a WHERE CURRENT OF clause with the cursor FOR loop.
D. The SALARY column used in the WHERE clause is not appropriate because it is not in the SELECT clause.
Given answer:
A. The block will execute without any error.
I thought that the block will fail because it should be 'SET salary=r_emp.salary*1.25' in that one line after 'UPDATE emp'. Do you agree?
Thank you for your time.
Kenton"
My reply:
When in doubt, test it and see. I don't currently have the typical emp table available, so I created a substitute with sufficient columns and data to test the code. In the examples, below I have tested three scenarios: the original code, the code using "r_emp." as you suggested with no other modifications, and the codes using "r_emp." and adding the salary column to the cursor. As the book states, the code executes correctly and provides a salary raise by 25 percent for each employee whose salary is below 3500 (no bug in the book). When I add only "r_emp." in front of salary, as you suggested, the code produces an error, because the salary column is not in the cursor. When I add "r_emp." in front of salary and add salary to the cursor, then it executes correctly.
In order to try to understand why this is, understand that salary is a column in the table and r_emp.salary is a column in the cursor, only if you selected that column when creating the cursor. So, you can either tell it to set the salary to the salary times 1.25 or set the salary to the r_emp.salary times 1.25 and the result is the same. This has to do with the usage of FOR UPDATE and WHERE CURRENT OF. The FOR UPDATE locks the rows to be updated and the WHERE CURRENT OF tells it to update the current row of your cursor that you are processing as you loop through it.
If the explanation above and the examples below don't make it clear, please feel free to ask more questions.
My test examples:
SQL> CREATE TABLE emp
2 (empno NUMBER,
3 ename VARCHAR2 (15),
4 salary NUMBER)
5 /
Table created.
SQL> INSERT INTO emp
2 VALUES (1, 'NAME1', 3000)
3 /
1 row created.
SQL> INSERT INTO emp
2 VALUES (2, 'NAME2', 4000)
3 /
1 row created.
SQL> INSERT INTO emp
2 VALUES (3, 'NAME3', 2000)
3 /
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> SELECT * FROM emp
2 /
EMPNO ENAME SALARY
--------- --------------- ---------
1 NAME1 3000
2 NAME2 4000
3 NAME3 2000
SQL> DECLARE
2 CURSOR c_emp IS SELECT empno, ename
3 FROM emp
4 WHERE salary<3500
5 FOR UPDATE;
6 BEGIN
7 FOR r_emp IN c_emp LOOP
8 UPDATE emp
9 SET salary=salary*1.25
10 WHERE CURRENT OF c_emp;
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM emp
2 /
EMPNO ENAME SALARY
--------- --------------- ---------
1 NAME1 3750
2 NAME2 4000
3 NAME3 2500
SQL> ROLLBACK
2 /
Rollback complete.
SQL> SELECT * FROM emp
2 /
EMPNO ENAME SALARY
--------- --------------- ---------
1 NAME1 3000
2 NAME2 4000
3 NAME3 2000
SQL> DECLARE
2 CURSOR c_emp IS SELECT empno, ename
3 FROM emp
4 WHERE salary<3500
5 FOR UPDATE;
6 BEGIN
7 FOR r_emp IN c_emp LOOP
8 UPDATE emp
9 SET salary=r_emp.salary*1.25
10 WHERE CURRENT OF c_emp;
11 END LOOP;
12 END;
13 /
SET salary=r_emp.salary*1.25
*
ERROR at line 9:
ORA-06550: line 9, column 24:
PLS-00302: component 'SALARY' must be declared
ORA-06550: line 8, column 7:
PL/SQL: SQL Statement ignored
SQL> SELECT * FROM emp
2 /
EMPNO ENAME SALARY
--------- --------------- ---------
1 NAME1 3000
2 NAME2 4000
3 NAME3 2000
SQL> DECLARE
2 CURSOR c_emp IS SELECT empno, ename, salary
3 FROM emp
4 WHERE salary<3500
5 FOR UPDATE;
6 BEGIN
7 FOR r_emp IN c_emp LOOP
8 UPDATE emp
9 SET salary=r_emp.salary*1.25
10 WHERE CURRENT OF c_emp;
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM emp
2 /
EMPNO ENAME SALARY
--------- --------------- ---------
1 NAME1 3750
2 NAME2 4000
3 NAME3 2500
SQL> DROP TABLE emp
2 /
Table dropped.
|
|
|
|
| Re: Possible bugs in a book [message #6768 is a reply to message #6735] |
Mon, 05 May 2003 23:30 |
Kenton Cronberg
Messages: 5
Registered: May 2003
|
Junior Member |
|
|
Barbara,
You have answered all my questions re some explicit cursor examples in the book! I now understand that the key is whether the specified column or columns in the UPDATE/SET line is or are in the cursor or not. You had an excellent idea of testing three scenarios as shown in your reply!
I also had another item in question from the book:
Problem:
Consider the following PL/SQL block. Which line has an error?
1 DECLARE
2 CURSOR c_emp IS SELECT empno, ename, salary
3 FROM emp
4 WHERE salary<3500;
5 BEGIN
6 FOR r_emp IN c_emp LOOP
7 UPDATE emp
8 SET salary=salary*1.25
9 WHERE CURRENT OF c_emp;
10 END LOOP;
11 END;
A. Line 2
B. Line 6
C. Line 9
D. The code has no error.
Given answer:
C. The WHERE CURRENT OF clause can only be used if the cursor is defined with a FOR UPDATE clause.
I ORGINALLY thought that the line 8 should be 'SET salary=r_emp.salary*1.25' and, thus, the error in line 8 would occur before the error in line 9. I NOW know that 'SET salary=salary*1.25' is also correct. Thus, line 9 is the only line that has an error.
Thus, I do not see any bugs in the book for now. I appreciate your PROMPT help!
Kenton
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 14:21:53 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
