bananaking

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
Twice
Four times
None (*)
Eight times
Once
Correct
2. 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)
Easier to write
Better data security (*)
Easier code maintenance (*)
Faster performance (*)
Do not need to declare variables
Correct
3. 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 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;
The statement will fail because you cannot
declare variables such as v_salary inside a procedure.
The procedure will be created successfully.
(*)
The statement will fail because the procedure
does not have any parameters.
Correct
4. Which of the following are characteristics of PL/SQL
stored procedures? (Choose three.) Mark for Review
(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. (*)
Incorrect. Refer to Section 7.
5. The following are the steps involved in creating, and
later modifying and re-creating, a PL/SQL procedure in Application
Express. In what sequence should these steps be performed?
Retrieve the saved code from "Saved SQL" in SQL Commands
Execute the code to create the procedure
Execute the code to re-create the procedure
Click on the "Save" button and save the procedure code
Modify the code in the SQL Commands window
Type the procedure code in the SQL Commands window Mark for Review
(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,CF,B,C,D,E,A
Incorrect. Refer to Section 7.6.
A PL/SQL procedure named MYPROC has already been created and stored
in the database. Which of the following will successfully re-create the
procedure after some changes have been made to the code? Mark for
Review
(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.
Incorrect. Refer to Section 7.
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? Mark for
Review
(1) Points
myproc(40);
myproc(10, B => 30, 50);
myproc(C => 25);
All of the above
None of the above (*)
Incorrect. Refer to Section 7.
8. Suppose you set up a parameter with an explicit IN mode.
What is true about that parameter? Mark for Review
(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.
Correct
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? Mark for Review
(1) Points
Positionally
Named (*)
A combination of positionally and named
A combination of named and defaulted
Defaulted
Correct
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? Mark for Review
(1) Points
Positional (*)
Named
A combination of positionally and named
A combination of named and defaulted
Defaulted
Correct11. Using nested blocks,
when is it necessary to label the outer block?. Mark for Review
(1) PointsYou 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 within the inner block. (*)
Block labels are just comments and are
therefore recommended but never needed.
Correct
12. Which of the following will display the value 'Smith'?
Mark for Review
(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;
Correct
13. 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; Mark for Review
(1) Points
True
False (*)
Correct
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; Mark for Review
(1) PointsThe 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.
Correct
15. What will be displayed when the following code is
executed?
<<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; Mark for Review
(1) Points
10
20 (*)
30
40
200
Incorrect. Refer to Section 6.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; Mark for Review
(1) Points
It will fail to compile because you cannot
have a subblock inside an exception section.
It will fail to compile because e_excep1 is
out of scope in the subblock.
It will fail to compile because you cannot
declare more than one exception in the same block.
It will compile successfully and return an
unhandled e_excep2 to the calling environment. (*)
Correct
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? Mark for
Review
(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.
Incorrect. Refer to Section 8.
18. A function must have at least one IN parameter, and must
return exactly one value. Mark for Review
(1) Points
True
False (*)
Correct
19. Why will this function not compile correctly?
CREATE FUNCTION bad_one
IS BEGIN
 RETURN NULL;
END bad_one; Mark for Review
(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 executable statement (as well as RETURN).
Correct
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? Mark for Review
(1) Points
myfunc('Crocodile') := v_var1;
myfunc(v_var1) := 'Crocodile';
myfunc(v_var1, 'Crocodile');
v_var1 := myfunc('Crocodile'); (*)
myfunc('Crocodile', v_var1);
Incorrect. Refer to Section 8.21.
What is wrong with the following code?
CREATE FUNCTION badfunc
(p_param NUMBER(4))
RETURN BOOLEAN
IS BEGIN
RETURN (p_param > 10);
END badfunc; Mark for Review
(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 an expression.
The NUMBER datatype must have a scale as well
as a precision.
Correct
22. Which of the following is a difference between a
procedure and a function? Mark for Review
(1) Points
Functions cannot be nested; procedures can be
nested to at least 8 levels.
A procedure can have default values for
parameters, 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 SQL
statement; a procedure can be used within SQL.A function must return a value, a procedure
may or may not. (*)
Correct
23. You want to remove the procedure NO_NEED from your
schema. You execute:
DROP PROCEDURE no_need;
Which Data Dictionary views are updated automatically? Mark for Review
(1) Points
USER_PROCEDURES
USER_OBJECTS
USER_SOURCE
All of the above. (*)
None of the above.
Correct
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. What
happens next? Mark for Review
(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 CHILD2 executes.
CHILD1 ends abruptly, PARENT also ends
abruptly and returns an unhandled exception.
PARENT does not compile because you cannot
use NULL; in an exception handler.Correct
25. The following code shows the dependencies between three
procedures:
CREATE PROCEDURE parent
IS BEGIN
 child1;
 child2;
END parent;
You now try to execute:
DROP PROCEDURE child2;
What happens? Mark for Review
(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.
Incorrect. Refer to Section 8.26.
User BOB creates procedure MYPROC using the default Definer's
Rights. BOB then executes:
 GRANT EXECUTE ON bob.myproc TO ted;
When TED invokes BOB.MYPROC, whose privileges are checked? Mark for
Review
(1) Points
TED's privileges
PUBLIC's privileges
SYSTEM's privileges
BOB's privileges (*)
ORACLE's privileges
Incorrect. Refer to Section 8.
27. How do you specify that you want a procedure MYPROCA to
use "Definer's Rights"? Mark for Review
(1) PointsCREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS...
CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...
GRANT DEFINER TO myprocA;
ALTER PROCEDURE myproca TO DEFINER;
Definer's Rights are the default, therefore
no extra code or commands are needed. (*)
Correct
28. 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);
The procedure cannot be modified. Once a
procedure 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 the
header)
REPLACE PROCEDURE someproc
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ...
CREATE OR REPLACE PROCEDURE MYPROC
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ... (*)
Incorrect. Refer to Section 7.
29. Which of the following statements about actual
parameters is NOT true? Mark for Review
(1) Points
An actual parameter is declared in the
calling environment, not in the called procedureAn actual parameter must be the name of a
variable (*)
An actual parameter can have a Boolean
datatype
The datatypes of an actual parameter and its
formal parameter must be compatible
An actual parameter can have a TIMESTAMP
datatype
Incorrect. Refer to Section 7.
30. A procedure will execute faster if it has at least one
parameter. Mark for Review
(1) Points
True
False (*)
Correct31. Which of the following
is NOT correct coding for a procedure parameter? Mark for Review
(1) Points
(p_param IN VARCHAR2)
(p_param VARCHAR2)
(p_param VARCHAR2(50)) (*)
(p_param employees.last_name%TYPE)
(p_param IN OUT VARCHAR2)
Correct
32. You want to create a procedure named SOMEPROC which
accepts a single parameter named SOMEPARM. The parameter can be up to 100
characters long. Which of the following is correct syntax to do this?
Mark for Review
(1) Points
CREATE PROCEDURE someproc
 (someparm varchar2)
IS
BEGIN ...
(*)
CREATE PROCEDURE someproc
 (someparm varchar2(100) )IS
BEGIN...
CREATE PROCEDURE someproc
IS
 (someparm VARCHAR2)
BEGIN...
CREATE PROCEDURE someproc
 someparm varchar2(100);
IS
 BEGIN...
CREATE PROCEDURE someproc
 (someparm 100)
IS
BEGIN ...
Correct
33. Which of the following best describes how an IN
parameter affects a procedure? Mark for Review
(1) Points
It describes the order in which the
procedure's statements should be executed.
It describes which parts of the procedure's
code are optional or conditional.
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.
Incorrect. Refer to Section 7.
34. Examine the following code fragment. At Line A, you want
to 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 employees
 WHERE employee_id = 100;
 IF v_salary > 30000 THEN
 -- Line A
 END IF;
... Mark for Review (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 OTHERS traps all exceptions.
Define an EXCEPTION variable and associate it
with an Oracle Server error number using PRAGMA EXCEPTION_INIT.
Incorrect. Refer to Section 6.
35. An attempt to insert a null value into a NOT NULL table
column raises an ORA-01400 exception. How can you code an exception
handler to trap this exception? Mark for Review
(1) Points
Test for WHEN ORA-1400 in the exception
section.
Declare a variable e_null_excep of type
EXCEPTION, associate it with ORA-01400 using a PRAGMA directive, and test
for WHEN e_null_excep in the exception section. (*)
Declare a variable e_null_excep of type
VARCHAR2, associate it with ORA-01400 using a PRAGMA directive, and test
for WHEN e_null_excep in the exception section.
Declare a variable as follows: e_null_excep
EXCEPTION := -01400; Then test for WHEN e_null_excep in the exception
section.
Incorrect. Refer to Section 6.36.
Examine the followiing code. Which exception handlers would
successfully trap the exception which will be raised when this code is
executed? (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; Mark for Review
(1) Points
(Choose all correct answers)
WHEN CURSOR_NOT_OPENWHEN INVALID_CURSOR (*)
WHEN OTHERS (*)
WHEN NO_DATA_FOUND
WHEN INVALID_FETCH
Correct
37. Which of these exceptions would need to be raised
explicitly by the PL/SQL programmer? Mark for Review
(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.
Correct
38. How can you retrieve the error code and error message of
any Oracle Server exception? Mark for Review
(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
Correct
39. Examine the following code. What message or messages
will 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; Mark for Review
(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
Correct
40. In which DML statements can user-defined functions be
used? Mark for Review
(1) Points
INSERT and UPDATE, but not DELETE.
INSERT only.
All DML statements. (*)
UPDATE only
DELETE only
Correct41. Which one of the
following statements about user-defined functions is NOT true? Mark
for Review
(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 a
SELECT statement. (*)Incorrect. Refer to Section 8.
42. Which of the following are NOT allowed in a function
which is used inside a SQL statement which updates the EMPLOYEES table?
(Choose two). Mark for Review
(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.
Correct
43. Which of the following is NOT an advantage of including
an exception handler in a PL/SQL block? Mark for Review
(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
Correct
44. While a PL/SQL block is executing, more than one
exception can occur at the same time. True or False? Mark for Review
(1) Points
True
False (*)
Incorrect. Refer to Section 6.
45. The following EXCEPTION section is constructed
correctly. True or False? EXCEPTION
 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS
 THEN statement_1;
 statement_2;
 WHEN OTHERS
 THEN statement_3;
END; Mark for Review
(1) Points
True (*)
False
Correct46. Which of the following
are good practice guidelines for exception handling? (Choose three.)
Mark for Review
(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 an error 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. (*)
Correct
47. A user-defined exception must be declared as a variable
of data type EXCEPTION. True or False? Mark for Review
(1) Points
True (*)
False
Correct
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
Correct
49. User-defined exceptions must be declared explicitly by
the programmer, but then are raised automatically by the Oracle Server.
True or False? Mark for Review
(1) Points
True
False (*)
Correct
50. Department-id 99 does not exist. What will be displayed
when the following code is executed?
DECLARE
 v_deptname departments.department_name%TYPE;
BEGIN
 SELECT department_name INTO v_deptname
 FROM departments WHERE department_id = 99;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 RAISE_APPLICATION_ERROR(-20201,'Department does not exist');
END; Mark for Review
(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
Incorrect. Refer to Section 6.