Database Programming with PL/SQL

Semester 1 Final Review

Name: Josselin Andrea Osorio Ponce

1. What are the steps for using a cursor?

a. Define, select, close

b. Open, fetch, close
c. Define, open, fetch, close
d. Open, select, fetch, close

2. The following statement is a valid cursor declaration. (True or False)

DECLARE
CURSOR dept_emp_cursor IS
SELECT department_name, COUNT(*) AS how_many FROM
departments d, employees e
WHERE d.department_id = e.department_id GROUP BY
d.department_name
HAVING COUNT(*) > 1;

3. OPEN is an executable statement that performs which of the following operations (circle answers)?

a. Executes the SELECT statement in the cursor declaration, returning the results into the active set
(fills the box with data)
b. Dynamically allocates memory for a context area
c. Retrieves the first row from the cursor.
d. Positions the pointer to the first row in the active set.

4. How do you test to see whether the cursor contains rows?

Agregando COUNT (*) la sentencia SELECT.
5. What is wrong with the following code?
El FETCH esta antes del OPEN en el cursor

DECLARE
CURSOR emp_curs IS
 SELECT last_name, salary FROM employees; v_last_name
employees.last_name%TYPE; v_salary employees.salary%TYPE;
BEGIN
FETCH emp_curs INTO v_last_name, v_salary; OPEN
emp_curs;
FETCH emp_curs INTO v_last_name, v_salary;
CLOSE emp_curs; END;
Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

6. A cursor may be reopened at any time. (True or False)

7. The example below uses what type of composite data type structure? Cursor

DECLARE
CURSOR emp_cursor IS SELECT employee_id,
last_name FROM employees
WHERE department_id = 30; v_emp_record emp_cursor
%ROWTYPE; BEGIN
OPEN emp_cursor; LOOP
FETCH emp_cursor INTO v_emp_record;
...

8. How do you reference the last_name value inside the loop in the example below?
V_last_name

DECLARE
CURSOR emp_cursor IS SELECT employee_id,
last_name FROM employees
WHERE department_id = 30; v_emp_record emp_cursor
%ROWTYPE; BEGIN
OPEN emp_cursor; LOOP
FETCH emp_cursor INTO v_emp_record;
...
9. Which are the cursor attributes that return useful information about the execution of a cursor
manipulation statement (circle answers)?

a. %OPEN
b. %FOUND
c. %NOTFOUND
d. %ROWTYPE
e. %ROWCOUNT

10. What type of loop performs the following actions: The cursor is opened, a row is fetched once for each
iteration in the loop, the loop is terminated automatically when the last row is processed, and the
cursor is closed automatically.
For loop

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 2
11. To display the last name of an employee, what code should you write at Point A in the example below?
Emp_record.salary

DECLARE
CURSOR emp_cursor IS SELECT * FROM employees; BEGIN
FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE( --
Point A -- );
END LOOP; END;

12. Select the correct method for opening the cursor in the example below (circle answer).

CURSOR emp_curs
(p_dept_id employees.department_id%TYPE, p_job_id
employees.job_id%TYPE) IS SELECT * FROM
employees
WHERE department_id = p_dept_id AND job_id =
p_job_id;

a. OPEN emp_curs (20);

b. FOR emp_rec IN emp_curs (20) LOOP
c. OPEN emp_curs ('IT_PROG', 20);
d. FOR emp_rec IN emp_curs (20, 'IT_PROG') LOOP

13. Is this a valid cursor declaration? (Yes or No)

CURSOR dept_curs (p_loc_id NUMBER(4)) IS SELECT *

FROM departments
WHERE location_id = p_loc_id;

14. What is the main purpose of the FOR UPDATE clause in a cursor declaration?
Especificar que un cursor puede ser actualizado
15. In which DML statements would you use the WHEN CURRENT OF clause?
UPDATE y DELETE

16. You want to fetch rows from the EMPLOYEES table. You want to lock the fetched rows, to prevent
other users from updating them. What would you write in Line A?

CURSOR emp_curs IS
SELECT department_name, employee_id, last_name, salary FROM employees
e, departments d
WHERE e.department_id = d.department_id
-- Line A -- ;

a. FOR UPDATE
b. FOR UPDATE OF employee_id
c. FOR UPDATE OF departments
Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 3
17. You have declared a cursor as SELECT .... FOR UPDATE; You have OPENed the cursor and locked
the FETCHed rows. When are these row locks released?

Cuando se utiliza explícitamente: COMMIT o ROLLBACK en su transacción

18. What would you enter at Line A?

DECLARE
CURSOR region_cur IS SELECT * FROM
regions;
v_region_rec region_cur%ROWTYPE;
CURSOR country_cur (p_region_id NUMBER) IS SELECT *
FROM countries
WHERE region_id = p_region_id; v_country_rec
country_cur%ROWTYPE; BEGIN
OPEN region_cur; LOOP
FETCH region_cur INTO v_region_rec; EXIT WHEN
region_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE
(v_region_rec.region_name);
-- Line A -- LOOP
FETCH country_cur INTO v_country_rec; EXIT WHEN country_cur
%NOTFOUND;
......
a. OPEN country_cur (p_region_id);
b. OPEN country_cur (region_cur.region_id);
c. OPEN country_cur (region_cur.region_id);
d. OPEN country_cur (v_region_rec.region_id);

19. You cannot use a FOR loop with multiple cursors. (True or False)

20. A PL/SQL record is a composite data type consisting of a group of related data items stored as fields,
each with its own name and data type.
21. How do you create a PL/SQL record based on the EMPLOYEES table?

a. p_emp_record IN employees%TYPE
b. p_emp_record IN employees%ROWTYPE
c. p_emp_record IN employees%RECORD

22. How do you reference the SALARY field in the P_EMP_RECORD record?
Sabiendo que se debe referenciar “record_name.field_name” entonces: P_EMP_RECORD.SALARY

23. A user-defined record cannot be defined using other records. (True or False)

24. The declaration below is a valid definition of a user-defined record using the PERSON_TYPE record.
(True or False)
Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 4
 TYPE employee_type IS RECORD (job_id
VARCHAR2(10), salary
NUMBER(8,2), person_data person_type);
25. A PL/SQL collection is a composite data type and contains a set of occurrences of the same kind of
data.

26. An INDEX BY table is based on a single field or column.

27. What is the default data type of the primary key of an index?
La syntax para crearla puede ser mediante CREATE TABLE o ALTER TABLE, siendo un valor NOT NULL

28. The following example declares two variables using the T_NAMES type. (True or False)

DECLARE
TYPE t_names IS TABLE OF VARCHAR2(50) INDEX BY
PLS_INTEGER;
last_names_tab t_names; first_names_tab t_names;
...

29. What are examples of methods to reference data or information about a table (circle answers)?

a. EXISTS
b. COUNT
c. PREVIOUS
d. NEXT

30. What does the following assignment do?

Indica que se entregara el numero más largo / alto de la tabla

v_highest_number := last_names_tab.LAST;
31. Which of the following examples declares an INDEX BY table of record?

a. TYPE t_emprec IS TABLE OF employees%TYPE INDEX BY

BINARY_INTEGER;
b. TYPE t_emprec IS TABLE OF employees%ROWTYPE INDEX BY
BINARY_INTEGER;
c. TYPE t_emprec IS INDEX TABLE OF employees%ROWTYPE BY BINARY_INTEGER;

32. An exception is an error that occurs during the execution of the block, which disrupts the normal
operation of the program. (True or False)

33. What functions does an exception handler perform (circle answers)?

a. Defines the recovery actions to be performed when exceptions are raised.

b. Passes the error to the calling environment.
Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 5
 c. Allows a block of code to be executed completely.

34. The WHEN OTHERS handler can be the first of multiple handlers. (True or False)

35. An user-defined exception is raised when the programmer issues the RAISE statement.

36. A PRAGMA-EXCEPTIOM-INITOracle server error has an Oracle-supplied name associated with it.

37. The TOO_MANY_ROWS error is raised under what typical condition?

exception

38. The example below is a valid block of code. (True or False)

EXCEPTION
WHEN NO_DATA_FOUND THEN
Statement 1;
WHEN NO_DATA_FOUND THEN
Statement 2;
WHEN OTHERS THEN
Statement 3; END;
39. The example below is a valid block of code. (True or False)

EXCEPTION
WHEN ZERO_DIVIDE OR TOO_MANY_ROWS OR NO_DATA_FOUND THEN
Statement 1;
WHEN OTHERS THEN
Statement 2; END;

40. What two pre-defined functions provide the Oracle serer error number and message?
SQLCODE y SQLERRM
41. The example declaration below is valid for a non-predefined exception. (True or False)

DECLARE
e_insert_exp EXCEPTION;
PRAGMA EXCEPTION_INIT (e_insert_exp, -01400);

42. What message will appear when this exception is reached?

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE ||': '|| SQLERRM); END;

a. The code number and its associated error message.

b. The code will fail because SQLCODE and SQLERRM cannot be used directly in a SQL statement.
c. The code will fail because SQLCODE and SQLERRM were not declared.
d. The code will fail because the error number is represented by SQLERRNUM.

43. What type of exception is raised by the programmer? User-defined exceptions. 

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 6
44. What steps are necessary for a programmer to define and use an error not defined by the database
(circle answers)?

a. Name the exception

b. Associate it with an error number
c. Explicitly raise the exception
d. Handle the exception

45. What is the method for calling an exception not defined by the database?

a. e_insert_exp;
b. CALL e_insert_exp;
c. RAISE e_insert_exp;
d. PRAGMA EXCEPTION_INIT (e_insert_exp);

46. To display your own error message and code, what is the correct code?

a. RAISE_APPLICATION_ERROR(20001, 'Message');
b. RAISE_ERROR (-20001, 'Message');
c. RAISE_APPLICATION_ERROR (-20001, 'Message');
d. RAISE APPLICATION_ERROR (20001, 'Message');

47. How would you reference the father’s birth date in Line A?
v_date_of_birth
<<outer>> DECLARE v_father_name
VARCHAR2(20):='Patrick';
v_date_of_birth DATE:='20-Apr-1972'; BEGIN
DECLARE
v_child_name VARCHAR2(20):='Mike';
v_date_of_birth DATE:='12-Dec-2002'; BEGIN
DBMS_OUTPUT.PUT_LINE('Name of father: '
|| v_father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth: '
|| -- Line A -- );

48. What is the scope and visibility of v_name in the example below?

<<outer>> DECLARE v_name

VARCHAR2(20) BEGIN
<<inner>> DECLARE v_name
VARCHAR2(20) BEGIN
... END;
... END;

a. It is in scope and visible in the outer block only.

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 7
 b. It is visible in both blocks but in scope only in the outer block.
c. It is in scope and visible in both blocks.
d. It is in scope in both blocks but visible only in the outer block.
49. Why does this example fail?
Debido a los bloques estan mal declarados, porque el mensaje debe de estar referenciado para cada bloque en
especifico

<<outer>> DECLARE v_name1

VARCHAR2(20); BEGIN
<<inner>> DECLARE v_name2
VARCHAR2(20); BEGIN
DBMS_OUTPUT.PUT_LINE(v_name1);
END;
DBMS_OUTPUT.PUT_LINE(v_name2); END;

50. A pre-defined Oracle server exception can be raised in an inner block and must be handled in the
outermost block. (True or False)

51. Two records exist for department 50. What will be displayed when this code is run?

DECLARE v_last_name
VARCHAR2(20); BEGIN
DBMS_OUTPUT.PUT_LINE('Message 1'); BEGIN
SELECT last_name into v_last_name
FROM employees WHERE department_id = 50; DBMS_OUTPUT.PUT_LINE('Message
2'); END; DBMS_OUTPUT.PUT_LINE('Message 3'); EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Message 4');
END;

a. Message 1
b. Message 1 Message 4
c. Message 1 Message 3
Message 4

52. Procedures and functions are PL/SQL bloques_ compiled and stored in the database.
53. What is the command to make a new procedure named get_name?

a. INSERT OR REPLACE PROCEDURE get_name…

b. CREATE PROCEDURE get_name…
c. SAVE PROCEDURE get_name;

54. Stored procedures and functions improve performance of PL/SQL code. (True or False)

55. Which data dictionary views contain information about a procedure (circle answer)?

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 8
 a. USER_PROCEDURES
b. USER_OBJECTS
c. USER_CODE

56. What are the ways to execute the procedure get_name (circle answers)?

a. From an anonymous block

b. From another procedure
c. From a DML statement
d. From Application Express

57. What is the phrase to update corrected code for the procedure get_name?

58. Which data dictionary view contains the code of the procedure get_name?

59. A parameter is the name of the variable passed into or out of a procedure. (True or False)

60. What is an argument?

a. A parameter
b. A variable
c. A value

61. Choose the correct syntax to create a procedure named get_name with two values p_id and p_name.

a. CREATE PROCEDURE (p_id NUMBER, p_name VARCHAR2) get_name IS…

b. CREATE PROCEDURE get_name (p_id NUMBER, p_name VARCHAR2) IS…
c. CREATE PROCEDURE get_name (p_id NUMBER(3), p_name VARCHAR2(25)) IS…

62. The name of a variable can be passed into a procedure as an argument. (True or False)

63. Formal parameter data types are defined with sizes. (True or False)

64 . What is an actual parameter (circle answers)?

a. A variable
b. A literal value
c. An expression

65. What is the default parameter mode if no mode is specified?

IN es el modo por default para parámetros
66. What is the parameter mode for a_emp_name in the query_emp procedure in the following example?
Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 9
 DECLARE
a_emp_name employees.last_name%TYPE; a_emp_sal
employees.salary%TYPE; BEGIN
query_emp(178, a_emp_name, a_emp_sal);
DBMS_OUTPUT.PUT_LINE('Name: ' || a_emp_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || a_emp_sal); END;

67. What is the actual parameter of the get_name procedure?

CREATE PROCEDURE get_name (p_id

INTEGER)
IS ...
BEGIN ... END;

DECLARE
v_id NUMBER(3) := 50; BEGIN
subproc(v_id); END;

a. p_id
b. v_id
c. 50
68. What is the correct method for calling the procedure add_dept (circle answers)?

CREATE OR REPLACE PROCEDURE add_dept

(p_name IN departments.department_name%TYPE, p_loc IN departments.location_id%TYPE :=
25) IS BEGIN
INSERT INTO departments (department_id, department_name, location_id)
VALUES (departments_seq.NEXTVAL, p_name, p_loc); END add_dept;

a. EXECUTE add_dept ('Administration', 25);

b. add_dept ('Administration', 25);
c. add_dept ('Administration', p_loc=>25);
d. add_dept ('Administration');

69. Which mode of parameters cannot have a DEFAULT value?

Un IN OUT
70. What command is used to remove a procedure from the database?
Un DROP PROCEDURE

71. A function is a named PL/SQL block that can accept optional IN parameters and must return a single
value. (True or False)

72. A function can be called as part of a PL/SQL expression only. (True or False)

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 10
73. What can a function do that a procedure cannot do (circle answer)?

a. Use all parameter three modes

b. Require a RETURN statement
c. Be called in a SQL expression

74. What is a valid call of the get_sal function shown below (circle answers)?

CREATE OR REPLACE FUNCTION get_sal

(p_id employees.employee_id%TYPE) RETURN
NUMBER IS
v_sal employees.salary%TYPE := 0; BEGIN
SELECT salary INTO v_sal
FROM employees WHERE employee_id = p_id; RETURN v_sal;
END get_sal;

a. v_salary := get_sal (100);

b. get_sal (100);
c. DBMS_OUTPUT.PUT_LINE (get_sal(100));
d. SELECT get_sal(100) FROM dual;

75. A RETURN statement cannot be included in an exception handler in a function. (True or False)

76. What is wrong with the following code?

CREATE FUNCTION annual_comp (sal employees.salary

%TYPE,
comm_pct IN employees.commission%TYPE) RETURN
NUMBER(5,2)
IS
RETURN (sal*12) + NVL(comm_pct,0)*12*sal; END
annual_comp;

77. A function cannot be used in a WHERE clause. (True or False)

78. A function can be used in the SET clause of an UPDATE statement. (True or False)

79. Which of the following SELECT statements using the function double_sal will work (circle answers)?

CREATE OR REPLACE FUNCTION double_sal (p_salary IN employees.salary

%TYPE) RETURN NUMBER IS
BEGIN
RETURN (p_salary * 2); END;

a. SELECT * FROM employees WHERE double_sal

(salary) > 20000;
Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 11
 b. SELECT last_name, double_sal (salary) FROM employees WHERE department_id = 50;
c. SELECT SUM double_sal (salary) FROM employees;

80. What is the correct statement to remove the double_sal function from your schema?

a. DELETE FUNCTION double_sal;

b. DROP FUNCTION double_sal;
c. ALTER FUNCTION double_sal DISABLE;
d. DROP SUBPROGRAM double_sal;

81. Which data dictionary view is used to see the code of a function you created?
USER_OBJECT

82. Which dictionary view will list all the PL/SQL subprograms in your schema?
user_objects (*)
83. Which data dictionary view will contain the list of tables that you have privileges to?
ALL * tables

84. Which privileges are found for tables but not for views (circle answers)?

a. SELECT
b. UDPATE
c. INDEX
d. ALTER

85. Christina creates a view called EMP_VIEW that is based on a SELECT from her EMPLOYEES table.
She now wants Tony to be able to query the view. What is the smallest set of object privileges that
Christina must grant to Tony?
GRANT SELECT ON EMPLOYEES TO Tony

86. Which statement will fail?

a. GRANT ALTER ON dept_view TO PUBLIC;

b. GRANT INSERT, UPDATE ON employees TO TOM, SUSAN;
c. GRANT SELECT ON departments TO PUBLIC;

87. To invoke a subprogram using Definer’s Rights, a user needs only EXECUTE privilege on the
subprogram. The user does NOT need any privileges on the objects referenced by SQL statements
within the subprogram. (True or False)

88. Jared creates a procedure called DEL_REGION using Definer's Rights, which deletes a row from his
REGIONS table. What privilege(s) will Pete need to be able to execute Jared’s procedure?

Un usuario de definer's rights procedure requiere solo el privilegio para ejecutar el procedimiento, porque el
procedimiento de definer’s rights opera bajo el dominio de seguridad del usuario propietario del

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 12
procedimiento, independientemente de quién lo esté ejecutando. El propietario del procedimiento debe tener
todos los privilegios de objeto necesarios para los objetos a los que se hace referencia.

Para otorgar el creador del procedemiento solo se debe utilizar el privilegio EXECUTE al usuario primero

89. Petra owns a DEPARTMENTS table. Zachary needs to update the location_id column of Petra’s table,
but no other columns. Which SQL statement should Petra execute to allow this?

a. GRANT UPDATE ON departments TO zachary;

b. GRANT UPDATE (location_id) ON departments TO zachary;
c. GRANT UPDATE ON departments (location_id) TO zachary;
d. GRANT UPDATE ON departments.location_id TO zachary;

90. What command is used to remove privileges from a user? El commando REVOKE

91. What is the clause to invoke Invoker’s Rights? AUTHID

92. Procedure UPD_EMPS includes an UPDATE on the EMPLOYEES table. The procedure was created
using Invoker's Rights. Which of the following statements are true (circle answers)?

a. The creator of the procedure needs UPDATE privilege on EMPLOYEES.

b. The user who executes the procedure needs EXECUTE privilege on the procedure.
c. The user who executes the procedure needs UPDATE privilege on EMPLOYEES.
d. The user who executes the procedure does not need any privileges.

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 13