PLSQL Final Exam Semester 1 - Part II

1. 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 the
subprogram be recompiled? (1) Points

Twice
Four times
None (*)
Eight times
Once

2. Which of the following are benefits of using PL/SQLsubprograms rather 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

3. A programmer wants to create a PL/SQL procedure namedEMP_PROC. What will happen when the following code is executed?
CREATE OR REPLACE PROCEDURE emp_proc ISv_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employeesWHERE employee_id = 999;
DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary);
END;(1) Points

The statement will raise a NO_DATA_FOUND exception because employee_id 999 does not exist.
The statement will fail because the last line of code should be END emp_proc; of code should be END emp_proc;
The statement will fail because you cannotdeclare variables such as v_salary inside a procedure.
The procedure will be created successfully.(*)
The statement will fail because the proceduredoes not have any parameters.

4. Which of the following are characteristics of PL/SQLstored procedures? (Choose three.) (1) Points (Choose all correct answers)

They are named PL/SQL blocks (*)
They must return exactly one value to thecalling environment.
They can have an exception section. (*)
They can be invoked from inside a SQLstatement.
They can accept parameters. (*)

5. The following are the steps involved in creating, andlater modifying and re-creating, a PL/SQL procedure in
pplicationExpress. In what sequence should these steps be performed?
(1) Points
F,C,A,B,E,D
F,B,D,A,E,C (*)
E,D,F,C,A,B
F,B,D,E,A,C
F,B,C,D,E,A

6. A PL/SQL procedure named MYPROC has already been created and storedin the database. Which of the following will successfully re-create the procedure after some changes have been made to the code? (1) Points
CREATE PROCEDURE myproc IS ...
CREATE OR REPLACE PROCEDURE myproc IS ....(*)
UPDATE PROCEDURE myproc IS ...
ALTER PROCEDURE myproc IS ...
None of the above, because the procedure mustbe dropped before it can be re-created.

7. The following procedure has been created:
CREATE OR REPLACE PROCEDURE myproc
(A IN NUMBER := 20,
B IN NUMBER,
C IN NUMBER DEFAULT 30)
IS .....
Which of the following will invoke the procedure correctly? (1) Points

myproc(40);
myproc(10, B => 30, 50);
myproc(C => 25);
All of the above
None of the above (*)

8. Suppose you set up a parameter with an explicit IN mode. What is true 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 bechanged inside the subprogram). (*)
nt (its value cannot bechanged inside the subprogram). (*)
It must be the same type as the matching OUTparameter.
It inherits its type from the matching OUTparameter.

9. Procedure SOMEPROC has five parameters named A, B, C, D, E in that order. 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

10. Procedure SOMEPROC has five parameters named A, B, C, D,E in that order. 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

11. Using nested blocks, when is it necessary to label the outer block?. (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 theouter block's variable within the inner block. (*)
Block labels are just comments and are therefore recommended but never needed.

12. 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;

13. The following code will execute correctly. True orFalse?
DECLARE
v_myvar1 NUMBER;
BEGIN
DECLARE
v_myvar2 NUMBER;
BEGIN

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

True
False (*)

14. What will happen when the following code is executed?

BEGIN --outer block
DECLARE --inner block
CURSOR emp_curs IS SELECT * FROM employees;
v_emp_rec emp_curs%ROWTYPE;

BEGIN
OPEN emp_curs;
LOOP

FETCH emp_curs INTO v_emp_rec;
DBMS_OUTPUT.PUT_LINE(v_emp_rec.salary);

END LOOP;
END;
CLOSE emp_curs;

END; (1) Points

The code will fail because you cannot declarea cursor in an inner block. a cursor in an inner block.
The code will fail because the cursor is declared in the inner block but is referenced in the outer block. (*)
The code will execute successfully anddisplay all the employees' salaries.
The code will execute forever because there is no statement to EXIT from the loop.

15. What will be displayed when the following code isexecuted?
<<outer>>
DECLARE
v_myvar NUMBER;

BEGIN
v_myvar := 10;
DECLARE

v_myvar NUMBER := 200;

BEGIN
outer.v_myvar := 20;
v_myvar := v_myvar / 0; --this raises a ZERO_DIVIDE error
outer.v_myvar := 30;

END;
v_myvar := 40;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(v_myvar);
END; (1) Points

10
20 (*)
30
40
200

16. 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 BEGIN RAISE e_excep2;
END;
END; (1) Points

It will fail to compile because you cannothave a subblock inside an exception section.
It will fail to compile because e_excep1 isout of scope in the subblock.
It will fail to compile because you cannotdeclare more than one exception in the same block.
It will compile successfully and return anunhandled e_excep2 to the calling environment. (*)

17. 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 BEGIN

x:= add_em;
END;

None of them will work.

18. A function must have at least one IN parameter, and must
return exactly one value. (1) Points

True
False (*)

19. 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 RETURNbefore 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 atleast one executable statement (as well as RETURN).

20. A function named MYFUNC has been created. This function accepts one IN parameter of datatype VARCHAR2 and returns a NUMBER.
You want to invoke the function within the following anonymous block:
DECLARE
v_var1 NUMBER(6,2);
BEGIN
--Line A
END;

What could be coded at Liine A? (1) Points

myfunc('Crocodile') := v_var1;
myfunc(v_var1) := 'Crocodile';
myfunc(v_var1, 'Crocodile');
v_var1 := myfunc('Crocodile'); (*)
myfunc('Crocodile', v_var1);

21. What is wrong with the following code?

CREATE FUNCTION badfunc
(p_param NUMBER(4))
RETURN BOOLEAN
IS BEGIN
RETURN (p_param > 10);

a precision or scale. It must be NUMBER, not NUMBER(4). (*)

END badfunc;
(1) Points
Mark for Review
P_PARAM must be declared AFTER the RETURN
clause.
P_PARAM must have a default value.
The datatype of the IN parameter cannot have

RETURN (p_param > 10); is wrong because youcannot return an expression.

The NUMBER datatype must have a scale as wellas a precision.

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

Functions cannot be nested; procedures can benested to at least 8 levels.
A procedure can have default values forparameters, while a function cannot.
An explicit cursor can be declared in a procedure, but not in a function.
A function cannot be used within a SQLstatement; a procedure can be used within SQL.
A function must return a value, a proceduremay or may not. (*) may or may not. (*)

23. You want to remove the procedure NO_NEED from yourschema. You execute:
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.

24. 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 CHILD1continues to execute.
CHILD1 ends abruptly. PARENT handles theexception and then ends. CHILD2 does not execute. (*)
CHILD1 ends abruptly, PARENT handles theexception, then CHILD2 executes.
CHILD1 ends abruptly, PARENT also endsabruptly and returns an unhandled exception.
PARENT does not compile because you cannotuse NULL; in an exception handler.

25. The following code shows the dependencies between threeprocedures:
CREATE PROCEDURE parent

IS BEGIN
child1;
child2;

END parent;
You now try to execute:

DROP PROCEDURE child2;
What happens? (1) Points

You cannot drop CHILD2 because PARENT isdependent on it.
CHILD2 is dropped successfully. PARENT andCHILD1 are both marked INVALID.
The database automatically drops PARENT aswell.
CHILD2 is dropped successfully. PARENT ismarked INVALID. CHILD1 is still valid. (*)
The database automatically drops CHILD1 aswell.

26. User BOB creates procedure MYPROC using the default Definer'sRights. BOB then executes:

GRANT EXECUTE ON bob.myproc TO ted;
When TED invokes BOB.MYPROC, whose privileges are checked? (1) Points

TED's privileges
PUBLIC's privileges
SYSTEM's privileges
BOB's privileges (*)
ORACLE's privileges

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

CREATE OR REPLACE PROCEDURE myprocaAUTHID CURRENT_USER IS... AUTHID 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, thereforeno extra code or commands are needed. (*)

28. You have created procedure MYPROC with a singleparameter PARM1 NUMBER. Now you want to add a second parameter to theprocedure. Which of the following will change the procedure successfully? (1) Points

ALTER PROCEDURE myproc ADD (parm2 NUMBER);

The procedure cannot be modified. Once aprocedure has been created, the number of parameters cannot be changed.
CREATE OR REPLACE PROCEDURE someproc(parm1 NUMBER, parm2 NUMBER);

(You do not need to repeat the detailed code of the procedure, only theheader)
REPLACE PROCEDURE someproc(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ...

CREATE OR REPLACE PROCEDURE MYPROC
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ... (*)

29. Which of the following statements about actualparameters is NOT true? (1) Points

An actual parameter is declared in thecalling environment, not in the called procedure
An actual parameter must be the name of a variable (*) e of a variable (*)
An actual parameter can have a Booleandatatype
The datatypes of an actual parameter and itsformal parameter must be compatible
An actual parameter can have a TIMESTAMPdatatype

30. A procedure will execute faster if it has at least oneparameter. (1) Points

True
False (*)

31. Which of the followingis NOT correct coding for a procedure parameter? (1) Points

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

32. You want to create a procedure named SOMEPROC whichaccepts a single 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... BEGIN...

CREATE PROCEDURE someprocIS
(someparm VARCHAR2)
BEGIN...

CREATE PROCEDURE someprocsomeparm varchar2(100);
IS
BEGIN...

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

33. Which of the following best describes how an INparameter affects a procedure? (1) Points

It describes the order in which the procedure's statements should be executed.
It describes which parts of the procedure'scode are optional or conditional.
It makes the procedure execute faster.
It passes a value into the procedure when theprocedure is invoked. (*)
It allows complex calculations to be executedinside the procedure.

34. Examine the following code fragment. At Line A, you wantto raise an exception if the fetched salary value is greater than 30000. How can you do this?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employeesWHERE employee_id = 100;
IF v_salary > 30000 THEN--Line A
END IF;
...
(1) Points

Test for WHEN VALUE_TOO_HIGH in the exceptionsection.
Use RAISE_APPLICATION_ERROR to raise an exception explicitly. (*)
Test for WHEN OTHERS in the exceptionsection, because WHEN OTHERS traps all exceptions.
Define an EXCEPTION variable and associate it with an Oracle Server error number using PRAGMA EXCEPTION_INIT.

35. An attempt to insert a null value into a NOT NULL tablecolumn raises an ORA-01400 exception. How can you code an exceptionhandler to trap this exception? (1) Points

Test for WHEN ORA-1400 in the exceptionsection.

Declare a variable e_null_excep of typeEXCEPTION, associate it with ORA-01400 using a PRAGMA directive, and testfor WHEN e_null_excep in the exception section. (*)

Declare a variable e_null_excep of typeVARCHAR2, associate it with ORA-01400 using a PRAGMA directive, and testfor WHEN e_null_excep in the exception section.

Declare a variable as follows: e_null_excepEXCEPTION := -01400; Then test for WHEN e_null_excep in the exceptionsection.

36.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

37. Which of these exceptions would need to be raisedexplicitly by the PL/SQL programmer? (1) Points
OTHERS
A SELECT statement returns more than one row.
A check constraint is violated.
A SQL UPDATE statement does not update any rows. (*)
A row is FETCHed from a cursor while the cursor is closed.

38. How can you retrieve the error code and error message ofany Oracle 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

39. Examine the following code. What message or messageswill be displayed when this code is executed?
DECLARE
v_last_name employees.last_name%TYPE;
v_number NUMBER := 27;

BEGIN

v_number := v_number / 0;
SELECT last_name INTO v_last_name FROM employeesWHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows were found');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END; Mark for Review
SELECT last_name INTO v_last_name FROM employeesWHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows were found');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
(1) Points

No rows were found
Attempt to divide by zero (*)
Attempt to divide by zero No rows were found
An error occurred
No message will be displayed

40. 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

41. Which one of the following statements about user-defined functions is NOT true? (1) Points

They can execute spell-checking routines.
They can be used inside SQL statements.
They can be combined (nested) together, similar to nesting system functions, for example INITCAP(SUBSTR( .....)).
They can return a TIMESTAMP datatype.
They can allow you to COMMIT from inside aSELECT statement. (*)

42. Which of the following are NOT allowed in a functionwhich is used inside a SQL statement which updates the EMPLOYEES table? (Choose two). (1) Points (Choose all correct answers)

SELECT .... FROM departments ....;
COMMIT; (*)
A RETURN statement.
DDL statements such as CREATE or ALTER. (*)
A WHEN OTHERS exception handler.

43. Which of the following is NOT an advantage of includingan exception handler in a PL/SQL block? (1) Points

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

44. While a PL/SQL block is executing, more than oneexception can occur at the same time. True or False? (1) Points

True
False (*)

45. The following EXCEPTION section is constructedcorrectly. True or False?

EXCEPTION

WHEN NO_DATA_FOUND OR TOO_MANY_ROWS

THEN statement_1;

statement_2;

WHEN OTHERS

THEN statement_3;
END;
(1) Points

True (*)
False

46. Which of the followingare good practice guidelines for exception handling? (Choose three.) (1) Points (Choose all correct answers)

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

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

True (*)
False

48. A user-defined exception can be raised:
A. In the declaration section
B. In the executable section
C. In the exception section Mark for Review
(1) Points

B
C
A and B
B and C (*)
A and C

49. User-defined exceptions must be declared explicitly bythe programmer, but then are raised automatically by the Oracle Server. True or False? (1) Points

True
False (*)

50. Department-id 99 does not exist. What will be displayedwhen the following code is executed?
DECLARE
v_deptname departments.department_name%TYPE;
BEGIN
SELECT department_name INTO v_deptnameFROM departments WHERE department_id = 99;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20201,'Department does not exist');
END;
(1) Points

ORA-01403: No Data Found ORA-20201:
Department does not exist
ORA-01403: No Data Found
ORA-20201: Department does not exist (*)
None of the above