Test: Section 8 Quiz

Review your answers, feedback, and question scores below. An asterisk (*) indicates
a correct answer.

Section 8 Quiz
(Answer all questions in this section)

1. A programmer creates a PL/SQL subprogram which is compiled and

stored in the database. Two separate users then execute an application which
invokes this subprogram four times. How many times must the subprogram be
recompiled? Mark for Review
(1) Points

Two times

Eight times

Zero times (*)

Four times

One time

Correct Correct

2. A programmer wants to create a PL/SQL procedure named EMP_PROC.

What will happen when the following code is executed?
CREATE OR REPLACE PROCEDURE emp_proc IS
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 999;
DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary);
END;

Mark for Review

(1) Points

The statement will fail because you cannot declare variables such as v_salary
inside a procedure.

The statement will raise a NO_DATA_FOUND exception because employee_id 999

does not exist.

The procedure will be created successfully. (*)

 The statement will fail because the last line of code should be END emp_proc;

The statement will fail because the procedure does not have any parameters.

Correct Correct

3. Which of the following keywords MUST be included in every PL/SQL

procedure definition? (Choose three.) Mark for Review
(1) Points

(Choose all correct answers)

REPLACE

IS or AS (*)

END (*)

BEGIN (*)

DECLARE

Correct Correct

4. The following are the steps involved in creating, and later

modifying and re-creating, a PL/SQL procedure in Application Express. Which step is
missing?
Type the procedure code in the SQL Commands window
Click on the "Save" button and save the procedure code
Retrieve the saved code from "Saved SQL" in SQL Commands
Modify the code in the SQL Commands window
Execute the code to re-create the procedure
Mark for Review
(1) Points

Invoke the procedure from an anonymous block

Enter parameters and data type

Execute the procedure from USER_SOURCE data dictionary view

Execute the code to create the procedure (*)

Correct Correct

5. Which of the following are benefits of using PL/SQL subprograms

rather than anonymous blocks? (Choose three.) Mark for Review
(1) Points

(Choose all correct answers)

Do not need to declare variables

Easier code maintenance (*)

Better data security (*)

Faster performance (*)

Easier to write

Correct Correct

Page 1 of 3 Next Summary

Test: Section 8 Quiz

Review your answers, feedback, and question scores below. An asterisk (*) indicates
a correct answer.

Section 8 Quiz
(Answer all questions in this section)

6. What are the three parameter modes for procedures? Mark for
Review
(1) Points

IN, OUT, IN OUT (*)

COPY, NOCOPY, REF

R(ead), W(rite), A(ppend)

CONSTANT, VARIABLE, DEFAULT

Correct Correct

7. Three IN parameters for procedure ADD_EMPLOYEE are defined as:

(p_name VARCHAR2 ,
p_salary NUMBER := 1000,
p_hired DATE DEFAULT SYSDATE)
The procedure is invoked by:

add_employee('Jones');

What is the value of P_SALARY when the procedure starts to execute?

Mark for Review

(1) Points

NULL

The call will fail because P_SALARY is a required parameter

1000 (*)

The procedure will not compile because P_SALARY should have been coded as
DEFAULT 1000

Correct Correct

8. Which of the following statements about IN OUT parameters are

true? (Choose two.) Mark for Review
(1) Points

(Choose all correct answers)

The data type for the parameter must be VARCHAR2.

The parameter value can be returned as a new value that is set within the
procedure. (*)

The parameter value passed into the subprogram is always returned unchanged
to the calling environment.

The parameter value can be returned as the original unchanged value. (*)

Correct Correct
 9. When creating a procedure, where in the code must the parameters
be listed? Mark for Review
(1) Points

Before the procedure name

After the keyword IS or AS

After the procedure name (*)

After the keyword PROCEDURE

Correct Correct

10. The following procedure has been created:

CREATE OR REPLACE PROCEDURE defproc
(A IN NUMBER := 50,
B IN NUMBER,
C IN NUMBER DEFAULT 40)
IS .....
Which one of the following will invoke the procedure correctly?

Mark for Review

(1) Points

defproc(30, 60 => C);

defproc(40, 70); (*)

defproc;

defproc(10 => A, 25 => C);

defproc(30 => A);

Correct Correct

Previous Page 2 of 3 Next Summary

Test: Section 8 Quiz

Review your answers, feedback, and question scores below. An asterisk (*) indicates
a correct answer.

Section 8 Quiz
(Answer all questions in this section)

11. Which of the following best describes the difference between a

parameter and an argument? Mark for Review
(1) Points

A parameter is a variable that accepts a value that is passed to it, while an

argument is the value that is passed. (*)

A parameter is the name of a variable, while an argument is the datatype of

that variable.

They are both names of variables. A parameter is passed into the procedure,
while an argument is passed out of the procedure.

There is no difference; parameters and arguments are the same thing.

Correct Correct

12. You have created procedure MYPROC with a single parameter PARM1
NUMBER. Now you want to add a second parameter to the procedure. Which of the
following will change the procedure successfully? Mark for Review
(1) Points

ALTER PROCEDURE myproc ADD (parm2 NUMBER);

CREATE OR REPLACE PROCEDURE myproc

(parm1 NUMBER, parm2 NUMBER);
(You do not need to repeat the detailed code of the procedure, only the header)

REPLACE PROCEDURE myproc

(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ...

CREATE OR REPLACE PROCEDURE myproc

(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ... (*)

The procedure cannot be modified. Once a procedure has been created, the
number of parameters cannot be changed.
Correct Correct

13. What is the correct syntax to create procedure MYPROC that

accepts two number parameters X and Y? Mark for Review
(1) Points

CREATE PROCEDURE (x NUMBER, y NUMBER) myproc IS ...

CREATE PROCEDURE myproc (x NUMBER, y NUMBER) IS ... (*)

CREATE PROCEDURE myproc IS (x NUMBER, y NUMBER) ...

CREATE PROCEDURE IS myproc (x NUMBER, y NUMBER) �

Correct Correct

14. Procedure TESTPROC accepts one parameter P1, whose value is up to

1000 characters in length. Which one of the following declares this parameter
correctly? Mark for Review
(1) Points

CREATE PROCEDURE testproc

DECLARE
p1 VARCHAR2(100);
BEGIN ....

CREATE PROCEDURE testproc

(p1 VARCHAR2)
IS
BEGIN ....
(*)

CREATE PROCEDURE testproc

p1 VARCHAR2
IS
BEGIN ....

CREATE PROCEDURE testproc

IS
p1 VARCHAR2(100);
BEGIN ....

CREATE PROCEDURE testproc

(p1 VARCHAR2(100) )
IS
BEGIN ....

Incorrect Incorrect. Refer to Section 8 Lesson 2.

15. Which one of the following statements about formal and actual
parameters is true? Mark for Review
(1) Points

Formal and actual parameters must have the same name.

An actual parameter is declared within the called procedure.

Formal and actual parameters must have different names.

A formal parameter is declared within the called procedure, while an actual

parameter is declared in the calling environment. (*)

Correct Correct

Previous Page 3 of 3 Summary