| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Help needed: OCP exam_cram book questons
Guang,
See my comments below. I think you'll find them interesting.
Jonathan
On Sun, 30 Jul 2000 15:24:43 -0800, Guang Mei wrote:
>Question 27:
>
>You query the database with this command:
>
>SELECT
>CONCAT(LOWER(SUBSTR(description,10)),
>LENGTH(product_name)) "Product ID"
>FROM inventory;
>
>Which function is evaluate second?
>a CONCAT()
>b. LOWER()
>c. SUBSTR()
>
>According to the book:
>The correct answer is c.=20
SUBSTR has to be executed prior to LOWER and CONCAT. In order for SUBSTR to be #2, it follows then that LENGTH must be #1. At first I convinced myself that I remembered that PL/SQL evaluated from right-to left in cases like this, but then I decided to test that assumption. The results are interesting, and show that the book's answer is incorrect. Read on for the details.
I created the following wrapper functions so that I could monitor the order of evaluation:
create or replace function mysubstr(x in varchar2,y in
number)
return varchar2 is
begin
dbms_output.put_line('substr');
return substr(x,y);
end;
/
create or replace function mylower(x in varchar2)
return varchar2 is
begin
dbms_output.put_line('lower');
return lower(x);
end;
/
create or replace function mylength(x in varchar2)
return number is
begin
dbms_output.put_line('length');
return length(x);
end;
/
create or replace function myconcat (x in varchar2, y in
varchar2)
return varchar2 is
begin
dbms_output.put_line('concat');
return concat(x,y);
end;
/
I then modified your query to use my wrapper functions, and executed it. Here are the results:
SQL> SELECT
2 MYCONCAT(MYLOWER(MYSUBSTR('abcdefghijklmno',10)),
3 MYLENGTH('lamp post')) "Product ID"
4 FROM dual;
Product ID
SQL>=20 SQL> --Force SQL*Plus to retrieve and display the output SQL> execute null;
PL/SQL procedure successfully completed.
It appears that Ault's book is incorrect. There are two arguments being passed to CONCAT: LOWER(SUBSTR(...)) and LENGTH(..). The leftmost argument is being evaluated first. The rightmost argument is being evaluated second. So you in fact, are absolutely correct. LOWER is the second function to be executed.
In general, I think the point that this question tests is a bad one. You could write PL/SQL for years without having to be concerned about such arcane details regarding the order of execution. The only time this matters is when you are using functions that have side-effects, and that's a poor programming practice to begin with. I'm sure there are occasionally reasons to do so, but I can't remember ever having written a function with side effects where I had to worry about the order of execution.
>-------------------
>Question 39:
>
>What is the purpose of the PL/SQL FETCH command?
>
>a. To define a cursor to be used later
>b. To retrieve values from the active set into local variables
>c. To call the rows identified by a cursor query into the active set
>d. To relase the memory used by the cursor
>
>According to the book:
>The correct answer is c. The fETCH command retrieve values returned by =
the=20
>cursor into the active set. Answer a is incorrect because this statement=
is=20
>the function of the CURSOR command. Answer b is incorrect because this =
is=20
>the function of OPEN command. Answer d is incorrect because this is the=20
>function of the CLOSE command.
>
>I thought the answer should be b. Again, why am I wrong here?
I would have said B myself. I've never heard the term "active set". However, looking in Steven Feuerstein's book, I find the following:
"When you open a cursor, PL/SQL executes the query for that cursor. It also identifies the active set--that is the rows from all involved tables that meet the criteria in the WHERE clause..."
So according to Steve, the OPEN statement causes the "active set" to be identified. This corresponds to answer C in Ault's book, so it's safe to assume that C cannot be correct for FETCH. Bottom line, I still consider B to be the correct answer.
BTW, that quote comes from Steven Feuerstein's Oracle PL/SQL Programming, 2nd Edition, Page 172, last paragraph.
>-------------------
>Question 34:
>
>Which Procedure Builder component would you use to edit the properties =
of=20
>your database objects?
>
>a. Stored Program Unit Editor
>b. Program Unit Editor
>c. PL/SQL Interpreter
>d. Object navigator
>
>
>My question (to all people who took OCP PL/SQL exam recently) is: Does =
exam=20
>cover specific oracle tool such as "Procedure Builder" or "OEM"?=20
Yes, but not to any great extent in the PL/SQL exam as I recall. Offhand, I don't even know the answer to this question. I'd have to look it up, because I don't use Procedure Builder. At least, I don't use it very often.=20
In other exams, Oracle has been more forcefull about requiring specific tool knowledge. I think they see it as a way to promote the use of their latest products. The Oracle Database Operator exam for example, is essentially a test of your knowledge of the Enterprise Manager GUI. The backup/recovery exam contains a large number of RMAN questions. Those can be frustrating to deal with if you do Received on Mon Jul 31 2000 - 10:08:37 CDT
 |
 |