PLSQL Final Exam Semester 1

1. How can you retrieve the error code and error message of any Oracle Server exception?

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

2. Which of these exceptions would need to be raised explicitly by the PL/SQL programmer?

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.

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

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

4. Which of the following are examples of predefined Oracle Server errors? (Choose three.) (Choose all correct answers)

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

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

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.

6. The following exception handler will successfully insert the Oracle error number and error message into a log table whenever an Oracle Server error occurs. True or False?
EXCEPTION
WHEN OTHERS THEN
INSERT INTO err_log_table (num_col, char_col)
VALUES (SQLCODE, SQLERRM);
END;
(Assume that err_log_table has been created with suitable
columns and datatypes.)

True
False (*)

7. Which of the following EXCEPTION sections are constructed correctly? (Choose two.) (Choose all correct answers)

EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN OTHERS THEN statement_2;
END; (*)

EXCEPTION
WHEN OTHERS THEN statement_2;
WHEN NO_DATA_FOUND THEN statement_1;
END;

EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN NO_DATA_FOUND THEN statement_2;
WHEN OTHERS THEN statement_3;
END;

EXCEPTION
WHEN OTHERS THEN statement_1;
END; (*)

8. While a PL/SQL block is executing, more than one exception can occur at the same time. True or False?
True
False (*)

9. Which of the following best describes a PL/SQL exception?
A user enters an invalid password while trying to log on to the database.
An error occurs during execution which disrupts the
normal operation of the program. (*)
A DML statement does not modify any rows.
The programmer makes a spelling mistake while writiing the PL/SQL code.

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

True (*)
False


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

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

12. User-defined exceptions must be declared explicitly by the programmer, but then are raised automatically by the Oracle Server. True or False?

True
False (*)

13. There are no employees in department 99. What message or messages will be displayed when the following code is executed?
DECLARE
e_my_excep EXCEPTION;
BEGIN
BEGIN
UPDATE employees SET salary = 10000
WHERE department_id = 99;
IF SQL%ROWCOUNT = 0 THEN
RAISE 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 THEN
DBMS_OUTPUT.PUT_LINE('Message 4');
END;

Message 1
Message 3
Message 1

Message 2

Message 1
Message 3
Message 4

Message 1

Message 4 (*)


14. A user-defined exception must be declared as a variable of data type EXCEPTION. True or False?

True (*)
False

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

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

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

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.

17. 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 THEN
DBMS_OUTPUT.PUT_LINE('Outer raised');
WHEN e_inner_excep THEN
DBMS_OUTPUT.PUT_LINE('Inner raised');
END;

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 calling environment.
The code will execute successfully and 'Outer Raised' will be displayed.
The code will fail to compile because e_inner_excep was declared but never RAISEd.

18. Examine the following code which shows three levels of nested block. What is the scope of the variable v_middle_var?
DECLARE --outer block
v_outer_var NUMBER;
BEGIN
DECLARE --middle block
v_middle_var NUMBER;
BEGIN
DECLARE --inner block
v_inner_var NUMBER;
BEGIN
...
END;
END;
END;

All three blocks
Middle and outer blocks only
Middle and inner blocks only (*)
Middle block only
None of the above

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

True
False (*)

20. Examine the following code. What is the scope and visibility of the outer block's v_last_name?
DECLARE
v_last_name VARCHAR2(20);
BEGIN
DECLARE
v_last_name VARCHAR2(20);
BEGIN
...
END:
...
END;

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.

21. Which of the following statements about actual parameters is NOT true?

An actual parameter is declared in the calling environment, not in the called procedure
An 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

22. Which of the following can NOT be used as the datatype of a procedure parameter?
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 A PLSQL record defined using %ROWTYPE

23. Which of the following best describes how an IN parameter affects a procedure?
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.

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

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

25. Which of the following is NOT correct coding for a procedure parameter?
(p_param IN VARCHAR2)
(p_param VARCHAR2)
(p_param VARCHAR2(50)) (*)
(p_param employees.last_name%TYPE)
(p_param IN OUT VARCHAR2)

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

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

27. 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? How was parameter D referenced?

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

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

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

29. What are the type of parameter modes?
CHARACTER, NUMBER, DATE, BOOLEAN

CONSTANT, VARIABLE, DEFAULT
LOCAL, GLOBAL, BOTH
IN, OUT, IN OUT (*)

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

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

31. Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose three.) (Choose all correct answers)
REPLACE
BEGIN (*)
IS or AS (*)
DECLARE

END (*)

32. Which of the following are characteristics of PL/SQL stored procedures? (Choose three.) (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. (*)

33. A PL/SQL procedure named MY_PROC1 has been successfully created in the database. The procedure has no parameters. Which of the following will successfully invoke the procedure in Application Express? (Choose two.) (Choose all correct answers)
DECLARE
v_var1 NUMBER := 20;
BEGIN
my_proc1(v_var1);
END;

EXECUTE my_proc1;
BEGIN
my_proc1;
END; (*)

CREATE OR REPLACE PROCEDURE my_proc2 IS
BEGIN
my_proc1;
END my_proc2; (*)

SELECT my_proc1 FROM DUAL;

34. A stored PL/SQL procedure can be invoked from which of the following?
A. A PL/SQL anonymous block
B. Another PL/SQL procedure
C. A calling application

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

35. A PL/SQL stored procedure can accept one or more input parameters and can return one or more output values to the calling environment. True or False?

True (*)
False

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

Twice
Four times
None (*)
Eight times
Once

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

23
11
66
17 (*)
An error message will be displayed because you cannot nest user-defined functions.

38. You have created a function named NEWFUNC. You now change some of
the function code, and try to recreate the function by executing:
CREATE OR REPLACE FUNCTION newfunc .... ;
What happens?
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 ....;
A second function named NEWFUNC_2 is created.
The function is dropped but not recreated.

39. Which of the following is a difference between a procedure and a function?
A procedure 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 function cannot.

40. Which of the following best describes a stored function?
A subprogram that must return exactly one value. (*)
A subprogram that must have at least one IN parameter.
A subprogram that has no OUT or IN OUT parameters.
A subprogram that executes automatically when a DML statement is executed on a table.
A subprogram which invokes another subprogram.

41. A function must have at least one IN parameter, and must return exactly one value.
True

False (*)

42. In a SELECT statement, where can a function NOT be used?
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.

43. User REYHAN creates the following procedure: CREATE PROCEDURE proc1 AUTHID CURRENT_USER IS v_count NUMBER; BEGIN SELECT COUNT(*)
INTO v_count FROM tom.employees; END; User BILL wants to execute this procedure.
What privileges will BILL need?

EXECUTE on REYHAN.PROC1 and SELECT on TOM.EMPLOYEES (*)
EXECUTE on REYHAN.PROC1 SELECT on TOM.EMPLOYEES
BILL needs no privileges
None of the above. The procedure will fail to compile because REYHAN does not have SELECT privilege on TOM.EMPLOYEES.

44. How do you specify that you want a procedure MYPROCA to use Invoker's
Rights?

CREATE OR REPLACE PROCEDURE myproca
AUTHID 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 myproca
AUTHID OWNER IS...

45. Which of the following are NOT allowed in a function which is used inside a SQL statement which updates the EMPLOYEES table? (Choose two). (Choose all correct answers)
SELECT .... FROM departments ....;
COMMIT; (*)
A RETURN statement.
DDL statements such as CREATE or ALTER. (*)
A WHEN OTHERS exception handler.

46. Which one of the following statements about user-defined functions is NOT true?
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. (*)


47. What is one of the advantages of using user-defined functions in a SQL statement?
They automate repetitive formulas which otherwise you would have to type 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.

48. Which Data Dictionary view can be used to display the detailed code of a procedure in your schema?
USER_PROCEDURES
USER_OBJECTS
USER_SOURCE (*)
USER_SUBPROGRAMS
None of the above.

49. You want to see the names, modes and data types of the formal parameters of function MY_FUNC in your schema. How can you do this? (Choose two) (Choose all correct answers)
Query USER_PARAMETERS
Query USER_SOURCE (*)
Query USER_FUNCTIONS
SHOW PARAMETER my_funct;
DESCRIBE my_funct; (*)

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

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.