PLSQL Final Exam

Section 6

1. Using nested blocks, when is it necessary to label the outerblock?. (1) Points

You must always label the outer block.
You must always label both blocks.
You must label the outer block when two variables with the same name are declared, one in each block.
You must label the outer block when two variables with the same name are declared and you need to reference the

outer block's variable withinthe inner block. (*)
Block labels are just comments and are therefore recommended butnever needed.

2. Which of the following will display the value 'Smith'? (1) Points
<<outer>>
DECLARE
v_name VARCHAR2(10) := 'Smith';
BEGIN
DECLARE
v_name VARCHAR2(10) := 'Jones';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;

<<outer>>
DECLARE
v_name VARCHAR2(10) := 'Smith';
BEGIN
DECLARE
v_name VARCHAR2(10) := 'Jones';
BEGIN
DBMS_OUTPUT.PUT_LINE(<<outer>>.v_name);
END;
END;

<<outer>>
DECLARE
v_name VARCHAR2(10) := 'Smith';
BEGIN
DECLARE
v_name VARCHAR2(10) := 'Jones';

BEGIN
DBMS_OUTPUT.PUT_LINE(outer.v_name);

END;
END;(*)

<<outer>>

DECLARE
v_name VARCHAR2(10) := 'Smith';

BEGIN
<<inner>>
DECLARE

v_name VARCHAR2(10) := 'Jones';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;

3. Using two nested blocks, a TOO_MANY_ROWS exception is raised withinthe inner block. Which of the following

exception handlers willsuccessfully handle the exception? (1) Points

WHEN TOO_MANY_ROWS in the inner block
WHEN TOO_MANY_ROWS in either block
WHEN OTHERS in either block
WHEN OTHERS in the inner block
All of the above (*)

4. What will happen when the following code is executed?
DECLARE
e_outer_excep EXCEPTION;
BEGIN
DECLARE
e_inner_excep EXCEPTION;
BEGIN
RAISE e_outer_excep;
END;
EXCEPTION
WHEN e_outer_excep THENDBMS_OUTPUT.PUT_LINE('Outer raised');
WHEN e_inner_excep THEN
DBMS_OUTPUT.PUT_LINE('Inner raised');
END;
(1) Points

The code will fail to compile because e_inner_excep cannot be referenced in the outer block. (*)
The code will propagate the e_outer_excep back to the callingenvironment.
The code will execute successfully and 'Outer Raised' will bedisplayed.
The code will fail to compile because e_inner_excep was declared butnever RAISEd.

5. What will happen when the following code is executed?
DECLARE

e_excep1 EXCEPTION;

e_excep2 EXCEPTION;

BEGIN
RAISE e_excep1;
EXCEPTION
WHEN e_excep1 THEN BEGINRAISE e_excep2; END;
END;
(1) Points

It will fail to compile because you cannot have a subblock inside anexception section.
It will fail to compile because e_excep1 is out of scope in thesubblock.
It will fail to compile because you cannot declare more than oneexception in the same block.
It will compile successfully and return an unhandled e_excep2 to thecalling environment. (*)

6. The following code will execute correctly. True or False?
DECLARE
v_myvar1 NUMBER;
BEGIN
DECLARE
v_myvar2 NUMBER;
BEGIN
v_myvar1 := 100;

END;

v_myvar2 := 100; v END;
(1) Points

True
False (*)

7. Which of the following best describes a predefined Oracle Servererror? (1) Points

Has a standard Oracle error number but must be named by the PL/SQLprogrammer
Is not raised automatically but must be declared and raisedexplicitly by the PL/SQL programmer
Has a standard Oracle error number and a standard name which can be referenced in the EXCEPTION section (*)
Is associated with an Oracle error number using PRAGMA EXCEPTION_INIT

8. Which kinds of exceptions are raised implicitly (i.e., automatically)? (Choose two.) (1) Points (Choose all

correct answers)

Predefined Oracle Server errors such as NO_DATA_FOUND (*)
User-defined errors
All errors
Non-predefined Oracle Server errors such as ORA-01400 (*)

9. Examine the followiing code. Which exception handlers wouldsuccessfully trap the exception which will be

raised when this code isexecuted? (Choose two.)
DECLARE
CURSOR emp_curs IS SELECT * FROM employees;
v_emp_rec emp_curs%ROWTYPE;
BEGIN

FETCH emp_curs INTO v_emp_rec;

OPEN emp_curs;

CLOSE emp_curs;
EXCEPTION ...
END;
(1) Points (Choose all correct answers)

WHEN CURSOR_NOT_OPEN
WHEN INVALID_CURSOR (*)
WHEN OTHERS (*)
WHEN NO_DATA_FOUND
WHEN INVALID_FETCH

10. Which of the following best describes a user-defined exception? (1) Points

A predefined Oracle Server error such as NO_DATA_FOUND
A non-predefined Oracle Server error such as ORA-01400
An error which is not automatically raised by the Oracle server (*)
Any error which has an Oracle error number of the form ORA-nnnnn

Section 6

11. How can you retrieve the error code and error message of anyOracle Server exception? (1) Points

By using the functions SQLCODE and SQLERRM (*)
By using the functions SQLCODE and SQLERR
By using RAISE_APPLICATION_ERROR
By defining an EXCEPTION variable and using PRAGMA EXCEPTION_INIT

12. An attempt to update an employee's salary to a negative value willviolate a check constraint and raise an

ORA-02290 exception. Which of thefollowing is a correct definition of a handler for this exception? (1) Points

DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(-02290,e_sal_excep);

DECLARE
PRAGMA EXCEPTION_INIT(e_sal_excep,-02290);
e_sal_excep EXCEPTION;

DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep,-02290);(*)

DECLARE
e_sal_excep EXCEPTION;
PRAGMA_EXCEPTION_INIT(e_sal_exception,-02290);

DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep,02290);

13. There are no employees in department 99. What message or messageswill be displayed when the following code

is executed?
DECLARE
e_my_excep EXCEPTION;
BEGIN
BEGIN
UPDATE employees SET salary = 10000WHERE department_id = 99;
IF SQL%ROWCOUNT = 0 THENRAISE e_my_excep;
END IF;
EXCEPTION

WHEN e_my_excep THEN
DBMS_OUTPUT.PUT_LINE('Message 1');
RAISE e_my_excep;
DBMS_OUTPUT.PUT_LINE('Message 2');

END;
DBMS_OUTPUT.PUT_LINE('Message 3');

EXCEPTION
WHEN e_my_excep THENDBMS_OUTPUT.PUT_LINE('Message 4');
END;
WHEN e_my_excep THENDBMS_OUTPUT.PUT_LINE('Message 4');
END;
(1) Points

Message 1Message 3
Message 1Message 2
Message 1Message 3Message 4
Message 1Message 4(*)

14. There are no employees in department_id 99. What output will bedisplayed when the following code is

executed?
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_countFROM employees WHERE department_id = 99;

IF v_count = 0 THEN

RAISE NO_DATA_FOUND;

DBMS_OUTPUT.PUT_LINE('No employees found');

END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Department 99 is empty');
END;
(1) Points

No employees found
No employees found Department 99 is empty
Department 99 is empty (*)
The block will fail because you cannot explicitly RAISE a predefinedOracle Server error such as NO_DATA_FOUND

15. User-defined exceptions must be declared explicitly by theprogrammer, but then are raised automatically by

the Oracle Server. Trueor False? (1) Points

True
False (*)

16. A user-defined exception must be declared as a variable of data
type EXCEPTION. True or False? (1) Points

True (*)
False

17. Examine the following code. Why does the exception handler notfollow good practice guidelines?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employeesWHERE employee_id = 999;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
(1) Points

You should not use DBMS_OUTPUT.PUT_LINE in an exception handler.
employee_id 999 does not exist in the employees table.
The exception handler should test for the named exceptionNO_DATA_FOUND. (*)
The exception handler should COMMIT the transaction.

18. Which of the following is NOT an advantage of including anexception handler in a PL/SQL block? (1) Points

Protects the database from errors
Code is more readable because error-handling routines can be written
in the same block in which the error occurred
Prevents errors from occurring (*)
Avoids costly and time-consuming correction of mistakes

19. Which of the following are good practice guidelines for exceptionhandling? (Choose three.) (1) Points

(Choose all correct answers)

Test your code with different combinations of data to see what potential errors can happen. (*)
Use an exception handler whenever there is any possibility of anerror occurring. (*)
Include a WHEN OTHERS handler as the first handler in the exception section.
Allow exceptions to propagate back to the calling environment.
Handle specific named exceptions where possible, instead of relying on WHEN OTHERS. (*)

20. Which of these exceptions can be handled by an EXCEPTION sectionin a PL/SQL block? (1) Points

A SELECT statement returns no rows
A SELECT statement returns more than one row
Any other kind of exception that can occur within the block
All of the above (*)
None of the above

21. One PL./SQL subprogram can be invoked from within manyapplications. True or False? (1) Points

True (*)
False

22. Which of the following are characteristics of PL/SQL subprogramsbut not of anonymous PL/SQL blocks? (Choose

three.) (1) Points (Choose all correct answers)

Can take parameters (*)
Are stored in the database (*)
Can begin with the keyword DECLARE Are named (*)
Are compiled every time they are executed

23. A stored PL/SQL procedure can be invoked from which of thefollowing?
A PL/SQL anonymous blockAnother PL/SQL procedureA calling application
(1) Points

A only
A and B
A and C
A, B and C (*)
B and C

24. Which of the following are benefits of using PL/SQL subprogramsrather than anonymous blocks? (Choose three.)

(1) Points (Choose all correct answers)

Easier to write
Better data security (*)
Easier code maintenance (*)
Faster performance (*)
Do not need to declare variables

25. A programmer creates a PL/SQL subprogram which is compiled andstored in the database. Two separate users

then execute an applicationwhich invokes this subprogram four times. How many times must thesubprogram be

recompiled? (1) Points

Twice
Four times
None (*)
Eight times
Once

26. Which of the following keywords MUST be included in every PL/SQLprocedure definition? (Choose three.) (1)

Points (Choose all correct answers)

REPLACE
BEGIN (*)
IS or AS (*)
DECLARE
END (*)

27. Which of the following can NOT be used as the datatype of aprocedure parameter? (1) Points

A non-SQL datatype such as BOOLEAN
The name of another procedure (*)
A large object datatype such as CLOB
A PLSQL record defined using %ROWTYPE

28. A procedure will execute faster if it has at least one parameter. (1) Points

True
False (*)

29. You want to create a procedure named SOMEPROC which accepts asingle parameter named SOMEPARM. The parameter

can be up to 100characters long. Which of the following is correct syntax to do this? (1) Points

CREATE PROCEDURE someproc
(someparm varchar2)
IS
BEGIN ...(*)

CREATE PROCEDURE someproc
(someparm varchar2(100) )
IS
BEGIN...

CREATE PROCEDURE someprocIS
(someparm VARCHAR2)
BEGIN...

CREATE PROCEDURE someproc
someparm varchar2(100);
IS
omeparm varchar2(100);
IS
BEGIN...

CREATE PROCEDURE someproc
(someparm 100)
IS
BEGIN ...

30. Examine the following procedure:
CREATE OR REPLACE PROCEDURE smallproc(p_param IN NUMBER)
IS
BEGIN ....
The procedure is invoked by:
DECLARE
v_param NUMBER := 20;
BEGIN
smallproc(v_param);
END;
Which of the following statements is true? (1) Points

p_param is a parameter and v_param is an argument
p_param is a formal parameter and 20 is an actual parameter
p_param is a formal parameter and v_param is an actual parameter (*)
p_param and v_param are both formal parameters, while 20 is an actualparameter
p_param is an actual parameter and v_param is a formal parameter

31. You have created a procedure named MYPROC that accepts three INparameters A, B, and C (all numbers). Which

of the following calls toMYPROC is NOT correct? (1) Points

myproc(5,10,20);
myproc(a=>5,b=>10,20) (*)
myproc(a=>5,b=>10,c=>20)
myproc(5,10,c=>20)

32. Which of the following is NOT correct coding for a procedureparameter? (1) Points

(p_param IN VARCHAR2)
(p_param VARCHAR2)
(p_param VARCHAR2(50)) (*)
(p_param employees.last_name%TYPE)
(p_param IN OUT VARCHAR2)

33. What are the type of parameter modes? (1) Points

CHARACTER, NUMBER, DATE, BOOLEAN
CONSTANT, VARIABLE, DEFAULT
LOCAL, GLOBAL, BOTH
IN, OUT, IN OUT (*)

34. Procedure SOMEPROC has five parameters named A, B, C, D, E in thatorder. The procedure was called as

follows:
SOMEPROC(10,20,D=>50);
How was parameter B referenced?
(1) Points

Positional (*)
Named
A combination of positionally and named
A combination of named and defaulted
Defaulted

35. Procedure SOMEPROC has five parameters named A, B, C, D, E in thatorder. The procedure was called as

follows:
SOMEPROC(10,20,D=>50);
How was parameter D referenced?
(1) Points

Positionally
Named (*)
A combination of positionally and named
A combination of named and defaulted
Defaulted

36. Suppose you set up a parameter with an explicit IN mode. What istrue about that parameter? (1) Points

It must have a DEFAULT value.
It cannot have a DEFAULT value.
It acts like a constant (its value cannot be changed inside the subprogram). (*)
It must be the same type as the matching OUT parameter.
It inherits its type from the matching OUT parameter.

37. In which DML statements can user-defined functions be used? (1) Points

INSERT and UPDATE, but not DELETE.
INSERT only.
All DML statements. (*)
UPDATE only
DELETE only

38. You have created a function named IS_LEAPYEAR that accepts one INparameter of datatype DATE and returns a

Boolean value (TRUE or FALSE)
depending on whether the date is in a leap year. What is wrong with thisquery:
SELECT last_name, hire_dateFROM employeesWHERE is_leapyear(hire_date)=TRUE;
(1) Points

The IS_LEAPYEAR function must be in the SELECT clause, not the WHEREclause.
YEAR function must be in the SELECT clause, not the WHEREclause.
You cannot use DATE and BOOLEAN datatypes in the same function.
The SELECT statement returns more than one row.
IS_LEAPYEAR is a reserved word in the SQL language.
The function returns a Boolean, and therefore cannot be used within aSELECT statement. (*)

39. What is one of the advantages of using user-defined functions in a SQL statement? (1) Points

They automate repetitive formulas which otherwise you would have totype in full every time you used them. (*)
They execute faster than system-defined functions such as UPPER and LOWER.
They allow you to execute DML from inside a SELECT statement.
They allow you to use functions which return a BOOLEAN.
They are stored on your local PC, not in the database.

40. How do you specify that you want a procedure MYPROCA to use "Definer's Rights"? (1) Points

CREATE OR REPLACE PROCEDURE myprocaAUTHID CURRENT_USER IS...
CREATE OR REPLACE PROCEDURE myprocaAUTHID OWNER IS...
GRANT DEFINER TO myprocA;
ALTER PROCEDURE myproca TO DEFINER;
Definer's Rights are the default, therefore no extra code or commandsare needed. (*)

Section 8

41. How do you specify that you want a procedure MYPROCA to use Invoker's Rights? (1) Points

CREATE OR REPLACE PROCEDURE myprocaAUTHID CURRENT_USER IS... (*)
Invoker's Rights are the default, therefore no extra code is needed.
GRANT INVOKER TO myprocA;
ALTER PROCEDURE myproca TO INVOKER;
CREATE OR REPLACE PROCEDURE myprocaAUTHID OWNER IS...

42. A function must have at least one IN parameter, and must returnexactly one value. (1) Points

True
False (*)

43. Which of the following is a difference between a procedure and afunction? (1) Points

A procedure can include DML statements, but a function cannot.
A function must have at least one IN parameter, while parameters areoptional for a procedure.
A procedure can return a BOOLEAN datatype, while a function cannot.
A function can be used inside a SQL statement, while a procedurecannot. (*)
A procedure can include an EXCEPTION section, while a functioncannot.

44. Why will this function not compile correctly?
CREATE FUNCTION bad_one
IS BEGIN
RETURN NULL;
END bad_one;
(1) Points

You cannot RETURN a NULL.
You must declare the type of the RETURN before the IS. (*)
You must have at least one IN parameter.
You must code CREATE OR REPLACE, not CREATE.
The body of the function must contain at least one executablestatement (as well as RETURN).

45. Consider the following function:
CREATE FUNCTION ADD_EM
(a NUMBER := 1,
b NUMBER := 2 )
RETURN NUMBER

IS BEGIN
RETURN (a+b);
END ADD_EM;

Which one of the following blocks will NOT work correctly?
(1) Points

DECLARE
x NUMBER;
BEGIN
x:= add_em(b=4);
END;(*)

DECLARE
x NUMBER;
BEGIN
x:= add_em(4);
END;

DECLARE
x NUMBER;
BEGIN
x:= add_em(4,5);
END;

DECLARE
x NUMBER;
BEGIN
x:= add_em;
END;

None of them will work.

46. Examine the following code:
CREATE OR REPLACE FUNCTION add_func
(p_param1 NUMBER, p_param2 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (p_param1 + p_param2);
END;
What will be displayed when the following SQL statement is executed?
SELECT add_func(6, add_func(3,8)) FROM dual;
(1) Points

23
11
66
17 (*)

An error message will be displayed because you cannot nest user-
defined functions.

47. You have created a function named NEWFUNC. You now change some ofthe function code, and try to recreate the

function by executing:
CREATE OR REPLACE FUNCTION newfunc .... ;
What happens?
(1) Points

The command fails because the function already exists.
The function is automatically dropped and then recreated. (*)
The command fails because you should execute: CREATE AND REPLACE....;
ails because you should execute: CREATE AND REPLACE....;
A second function named NEWFUNC_2 is created.
The function is dropped but not recreated.

48. The following code shows the dependencies between three
procedures:
CREATE PROCEDURE parentIS BEGIN
child1;

child2;
END parent;
You now try to execute:

DROP PROCEDURE child2;
What happens?
(1) Points

You cannot drop CHILD2 because PARENT is dependent on it.
CHILD2 is dropped successfully. PARENT and CHILD1 are both markedINVALID.
The database automatically drops PARENT as well.
CHILD2 is dropped successfully. PARENT is marked INVALID. CHILD1 isstill valid. (*)
The database automatically drops CHILD1 as well.

49. You want to remove the procedure NO_NEED from your schema. Youexecute:
DROP PROCEDURE no_need;
Which Data Dictionary views are updated automatically?
(1) Points

USER_PROCEDURES
USER_OBJECTS
USER_SOURCE
All of the above. (*)
None of the above.

50. Examine the following code: CREATE PROCEDURE parent
IS BEGIN
child1;
child2;

EXCEPTION

WHEN NO_DATA_FOUND THEN NULL;
END parent;
Neither CHILD1 nor CHILD2 has an exception handler.
When PARENT is invoked, CHILD1 raises a NO_DATA_FOUND exception. Whathappens next?
(1) Points

PARENT handles the exception, then CHILD1 continues to execute.
CHILD1 ends abruptly. PARENT handles the exception and then ends.
CHILD2 does not execute. (*)
CHILD1 ends abruptly, PARENT handles the exception, then CHILD2executes.
CHILD1 ends abruptly, PARENT also ends abruptly and returns anunhandled exception.
PARENT does not compile because you cannot use NULL; in an exceptionhandler.