[Solved] Place to store PL/SQL block [closed]

As Ranzo notes several of the other answers are are also plausible. So, why is E the correct answer? Because the question asks for the most appropriate location for the code.

Let’s break it down.

The logic needs to be called from several places. It is therefore necessary to define it as its own function (because we don’t want to repeat the same code).

At the same time, the customer discount logic is only called from the ORDERTOTAL program unit. Hence we don’t want to expose it as a standalone procedure or a packaged function, which could be called by other programs. That rules out A and B.

So the best place to put it is E, the declaration section of the ORDERTOTAL program. Something like:

 create procedure ordertotal
     (custid in pls_integer
      , orderid in pls_integer)
 is
     ln_running_total number;
     ....
     function calc_discount 
        (custid in pls_integer)
        return number
     is
  ....

Now if ORDERTOTAL were a packaged procedure, we would have the choice to make CALC_DISCOUNT() a private function (defined in the body but not declared in the spec). But generally speaking its better to keep the scope as tight as possible, unless there is a reasonable chance the function will be used by other procedures in the future.

For the record, C is wrong because stuff declared on the client side is not accessible to the server, and D is wrong because it would not compile.

While searching for a text version of this question, I found a PDF version here which gives the correct answer as A. 😎 And another site offering pirated exams says the correct answer is B. So the good news is, people who think they can hack the tests by simply learning the answers are going to FAIL.