PL/SQL QUESTIONS:

1. Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low

Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn?t have to.

Score: ____________ Comment: ________________________________________________________

2. What is a mutating table error and how can you get around it?
Level: Intermediate

Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

Score: ____________ Comment: ________________________________________________________

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low

Expected answer: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

Score: ____________ Comment: ________________________________________________________


4. What packages (if any) has Oracle provided for use by developers?
Level: Intermediate to high

Expected answer: Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

Score: ____________ Comment: ________________________________________________________

5. Describe the use of PL/SQL tables
Level: Intermediate

Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

Score: ____________ Comment: ________________________________________________________

6. When is a declare statement needed ?
Level: Low

The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

Score: ____________ Comment: ________________________________________________________

7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?
Level: Intermediate

Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

Score: ____________ Comment: ________________________________________________________

8. What are SQLCODE and SQLERRM and why are they important for PL/SQL develo...

Show full text: 53,284 characters