PLSQL Final Exam Semester 1 - Part I

1. Which of the following best describes how an IN parameter affects aprocedure? (1) Points

It describes the order in which the procedure's statements shouldbe executed.
It describes which parts of the procedure's code are optional orconditional.
It makes the procedure execute faster.
It passes a value into the procedure when the procedure is invoked.(*)
It allows complex calculations to be executed inside the procedure.

2. Which of the following is NOT correct coding for aprocedure 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)

3. You have created the following procedure:
CREATE OR REPLACE PROCEDURE double_it
(p_param IN OUT NUMBER)
IS
BEGIN
p_param := p_param * 2;
END;

Which of the following anonymous blocks invokes this proceduresuccessfully? Mark for Review successfully? (1)

Points

BEGIN
EXECUTE double_it(20);
END;

BEGIN
SELECT double_it(20)
FROM DUAL;
END;

DECLARE
v_result NUMBER(6);
BEGIN
v_result := double_it(20);
END;

DECLARE
v_result NUMBER(6) := 20;
BEGIN
double_it(v_result);
END; (*)

BEGIN
double_it(20);
END;

4. 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 anactual parameter
p_param is an actual parameter and v_param is a formal parameter

5. You have created a procedure named MYPROC that acceptsthree IN parameters A, B, and C (all numbers). Which of

the followingcalls to MYPROC 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)

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

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

8. Which parameter mode is the default? (1) Points

IN (*)
OUT
NUMBER
VARIABLE
CONSTANT

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. What are the type of parameter modes? (1) Points

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

11. Which of the following will successfully return a user-defined
error message? (1) Points

RAISE_APPLICATION_ERROR('Error Raised',-22001);
RAISE_APPLICATION_ERROR(-20257,'Error raised'); (*)
RAISE_APPLICATION_ERROR(-22001,'Error Raised')-22001,'Error Raised');
RAISE_APPLICATION_ERROR('Error Raised',-20257);

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

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

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

True (*)
False

14. A user-defined exception is raised by using: (1) Points

FLAG exception_name;
RAISE exception-name; (*)
PRAGMA EXCEPTION_INIT
RAISE(error_number, exception_name);

15. Examine the following code (the code of CHILD2 is notshown):
CREATE PROCEDURE child1
IS v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employeesWHERE employee_id = 9999;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END child1;
CREATE PROCEDURE parent
IS BEGIN
child1;

child2;

EXCEPTION

WHEN NO_DATA_FOUND THEN NULL;

END parent;

Employee_id 9999 does not exist. What happens when PARENT is executed?
(1) Points

CHILD1 handles the exception successfully and ends. PARENTcontinues to execute and invokes CHILD2. (*)
CHILD1 ends abruptly, PARENT handles the exception successfully andends. CHILD2 does not execute.
CHILD1 ends abruptly, then PARENT also ends abruptly with anunhandled exception.
PARENT handles the exception, then CHILD1 resumes execution.
PARENT fails to compile because you cannot have the same exceptionhandler in two separate subprograms. handler

in two separate subprograms.

16. 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. What
happens 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 anexception handler.

17. 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?What happens?
(1) Points

You cannot drop CHILD2 because PARENT is dependent on it.
CHILD2 is dropped successfully. PARENT and CHILD1 are both marked
INVALID.

The database automatically drops PARENT as well.

CHILD2 is dropped successfully. PARENT is marked INVALID. CHILD1 is still valid. (*)
The database automatically drops CHILD1 as well.

18. User BOB creates procedure MYPROC using the defaultDefiner's Rights. 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

19. 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, therefore no extra code orcommands are needed. (*)

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

21. Which of the following are characteristics of PL/SQL storedprocedures? (Choose three.) (1) Points (Choose

all correct answers)

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

22. Which of the following are characteristics of PL/SQLsubprograms but 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 PL/SQL procedure named MYPROC has already been createdand stored in the database. Which of the following

will successfully recreate 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 must be dropped before it can be re-created.

24. Which of the following keywords MUST be included inevery PL/SQL procedure definition? (Choose three.) (1)

Points (Choose all correct answers)

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

25. 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 becauseemployee_id 999 does not exist.
The statement will fail because the last line of code should be END emp_proc;
The statement will fail because you cannot declare variables suchas v_salary inside a procedure.
The procedure will be created successfully. (*)
The statement will fail because the procedure does not have anyparameters.

26. Examine the following code fragment. At Line A, you want to raisean exception if the fetched salary value is

greater than 30000. How canyou 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 exception section.
Use RAISE_APPLICATION_ERROR to raise an exception explicitly. (*)
Test for WHEN OTHERS in the exception section, because WHEN OTHERStraps all exceptions.
Define an EXCEPTION variable and associate it with an Oracle Server error number using PRAGMA EXCEPTION_INIT.

27. 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 (*)

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

29. 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 employees

WHERE 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

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


31. Which of the following are examples of predefined Oracle Servererrors? (Choose three.) (1) Points (Choose

all correct answers)

TOO_MANY_ROWS (*)
NO_DATA_FOUND (*)
OTHERS
ZERO_DIVIDE (*)
E_INSERT_EXCEP

32. In a SELECT statement, where can a function NOT be used? (1) Points

In a GROUP BY or HAVING clause.
A function can be used anywhere in a SELECT statement. (*)
In a WHERE clause.
In the column list (SELECT) clause.
In an ORDER BY clause.

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

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

34. What is wrong with the following code?
CREATE FUNCTION badfunc
(p_param NUMBER(4))
RETURN BOOLEAN
IS BEGIN
RETURN (p_param > 10);
END badfunc;
(1) Points

P_PARAM must be declared AFTER the RETURN clause.
P_PARAM must have a default value.
The datatype of the IN parameter cannot have a precision or scale.
It must be NUMBER, not NUMBER(4). (*)
RETURN (p_param > 10); is wrong because you cannot return anexpression.
The NUMBER datatype must have a scale as well as a precision.

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

A procedure can include DML statements, but a function cannot.
A function must have at least one IN parameter, while parametersure can include DML statements, but a function

cannot.
A function must have at least one IN parameter, while parameters are optional 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 procedure cannot. (*)
A procedure can include an EXCEPTION section, while a functioncannot.

36. You try to create a function named MYFUNC. The function does not compile correctly because there are errors

in your code. Which Dictionaryview can you query to see the errors? (1) Points

USER_SOURCE
USER_ERRORS (*)
USER_OBJECTS
USER_DEPENDENCIES
USER_COMPILES

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

True
False (*)

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

39. You have created a function named IS_LEAPYEAR that accepts one IN parameter of datatype DATE and returns a

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

The IS_LEAPYEAR function must be in the SELECT clause, not theWHERE clause.
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 a SELECT statement. (*)

40. Where can a function be used in a query? (1) Points

Nowhere in a query.
Anywhere in a query. (*)
Only in the SELECT clause
Only in the WHERE clause
In the SELECT or WHERE clauses, but not in the ORDER BY clause.

41. 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 declare 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 and display all the employees'salaries.
The code will execute forever because there is no statement to EXIT from the loop.


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

43. Examine the following code. What is the scope andvisibility of the outer block's v_last_name?
DECLARE
v_last_name VARCHAR2(20);
BEGIN
DECLARE
v_last_name VARCHAR2(20);
BEGIN

...
END:
...

END;
(1) Points

It is in scope and visible in both blocks.
It is in scope and visible in the outer block only.
It is in scope in both blocks, but visible only in the outer block.(*)
It is visible in both blocks, but in scope only in the outer block.

44. Using two nested blocks, a TOO_MANY_ROWS exception israised within the inner block. Which of the following

exception handlerswill successfully 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 (*)

45. There are no employees in department 75. What will bedisplayed when this code is executed?
DECLARE
v_last_name employees.last_name%TYPE;

BEGIN
DBMS_OUTPUT.PUT_LINE('A');
BEGIN

SELECT last_name INTO v_last_name
FROM employees WHERE department_id = 75;

DBMS_OUTPUT.PUT_LINE('B');
END;
DBMS_OUTPUT.PUT_LINE('C');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('D');
END;
(1) Points

A
C
D

A
D (*)

A

A
B
D

None of the above

46. Using nested blocks, when isit 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 the

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

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

48. Examine the following code. Why does the exceptionhandler not follow 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 exception
NO_DATA_FOUND. (*)
The exception handler should COMMIT the transaction.

49. Which of the following are good practice guidelines forexception handling? (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 exceptionsection.
Allow exceptions to propagate back to the calling environment.
Handle specific named exceptions where possible, instead of relyingon WHEN OTHERS. (*)

50. Which of these exceptions can be handled by an EXCEPTIONsection in 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
Any other kind of exception that can occur within the block
All of the above (*)
None of the above