PL/SQL Section 12 Quiz

 1. What is wrong with this code example?

CREATE OR REPLACE PROCEDURE insert_emps IS
  TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
   v_emptab t_emp;
BEGIN
   FORALL i IN v_emptab.FIRST..v_emptab.LAST
     INSERT INTO employees VALUES v_emptab(i);
   END LOOP;
END insert_emps;

Tandai untuk Ditinjau

(1) Point

Nothing is wrong; it will compile successfully.

v_emptab is incorrectly typed.

FORALL does not require END LOOP. (*)

The phrase should be FOR ALL.

Correct

---

2. Which of the following are NOT benefits of using the NOCOPY hint? (Choose two)

Tandai untuk Ditinjau

(1) Point

Safer because it uses passing by value(*)

Uses a larger block of server memory for faster access(*)

Efficient since it uses less memory

Eliminates extra processing

Faster because a single copy of the data is used

Incorrect. Refer to Section 12 Lesson 2.

---

3. You want to take make a copy of all the cities in the world listed in the cities table, which contains millions of rows. The following procedure accomplishes this efficiently. True or False?

CREATE OR REPLACE PROCEDURE copy_cities IS
  TYPE t_cities IS TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER;
  v_citiestab t_emp;
BEGIN
  SELECT * BULK COLLECT INTO v_citiestab FROM cities;
  FORALL i IN v_citiestab.FIRST..v_citiestab.LAST
  INSERT INTO new_cities VALUES v_citiestab(i);
END copy_cities;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

4. In the following example, where do you place the phrase DETERMINISTIC?

CREATE OR REPLACE FUNCTION total_sal
  (p_dept_id IN -- Position A
employees.department_id%TYPE)
  RETURN NUMBER -- Position B
    IS v_total_sal NUMBER;
BEGIN
  SELECT SUM(salary) INTO v_total_sal
    FROM employees WHERE department_id = p_dept_in;
  RETURN v_total_sal -- Position C;
END total_sal;

Tandai untuk Ditinjau

(1) Point

Position A

Position B (*)

Position C

Correct

---

5. What is the correct syntax to use the RETURNING phrase at Position A?

DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
  UPDATE employees
  SET salary = salary * 1.1 WHERE employee_id = emp_id
-- Position A
  dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary);
END;

Tandai untuk Ditinjau

(1) Point

RETURNING last_name, salary TO emp_info;

RETURNING last_name, salary INTO emp_info; (*)

RETURNING FROM emp_info;

last_name, salary RETURNING INTO emp_info;

6. What does the RETURNING clause do in the example below?

CREATE OR REPLACE PROCEDURE new_dept
  (p_dept_name IN departments.name%TYPE) IS
  v_new_dept_id departments.dept_id%TYPE;
BEGIN
  INSERT INTO departments (dept_id, name)
    VALUES dept_seq.NEXTVAL, p_dept_name
    RETURNING dept_seq.CURRVAL INTO v_new_dept_id;
  DBMS_OUTPUT.PUT_LINE(p_dept_name ||' is department number ' || v_new_dept_id);
END new_dept;

Tandai untuk Ditinjau

(1) Point

Inserts the new department id in the department table

Performs the SELECT statement to determine the department id of the new department (*)

Uses the new department number in a cursor

Incorrect. Refer to Section 12 Lesson 2.

---

7. The following example code will compile successfully. True or False?

CREATE OR REPLACE PROCEDURE dept_proc IS
  TYPE t_dept IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
BEGIN
  (p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept);
-- remaining code
END dept_proc;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

8. The following procedure compiles successfully. True or False?

CREATE OR REPLACE PACKAGE emp_pkg IS
  TYPE t_emp IS TABLE OF employees%ROWTYPE
    INDEX BY BINARY_INTEGER;
  PROCEDURE emp_proc
    (p_small_arg IN NUMBER, p_big_arg NOCOPY OUT t_emp);
...
END emp_pkg;

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 12 Lesson 2.

---

9. The easiest way to include DDL statements in a PL/SQL block is to use the DBMS_SQL package. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 12 Lesson 1.

---

10. You want to create a function which drops a table. You write the following code:

CREATE OR REPLACE FUNCTION droptab
    (p_tab_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
    DROP TABLE p_tab_name;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;

Why will this procedure not compile successfully?

Tandai untuk Ditinjau

(1) Point

Because you do not have the privilege needed to drop a table

Because you can never drop a table from inside a function

Because the PL/SQL compiler cannot check if the argument of p_tab_name is a valid table-name (*)

Because you cannot use RETURN in the exception section

11. Dynamic SQL enables data-definition, data-control, or session-control statements to be written and executed from PL/SQL.

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

12. Examine the following code:

CREATE OR REPLACE PROCEDURE myproc IS
    CURSOR c_curs IS SELECT view_name FROM user_views;
BEGIN
    FOR v_curs_rec IN c_curs LOOP
       EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name;
    END LOOP;
END;

What will happen when this procedure is invoked?

Tandai untuk Ditinjau

(1) Point

The procedure will raise an exception because one of the views is a complex view.

All views in the user's schema will be dropped. (*)

The procedure will not compile successfully because the syntax of EXECUTE IMMEDIATE is incorrect.

The procedure will raise an exception because Dynamic SQL can drop tables but cannot drop views.

Incorrect. Refer to Section 12 Lesson 1.

---

13. What will happen when the following procedure is invoked?

CREATE OR REPLACE PROCEDURE do_some_work IS
    CURSOR c_curs IS SELECT object_name FROM user_objects
       WHERE object_type = 'FUNCTION';
BEGIN
    FOR v_curs_rec IN c_curs LOOP
       EXECUTE IMMEDIATE 'ALTER FUNCTION ' || v_curs_rec.object_name || ' COMPILE';
       EXIT WHEN c_curs%ROWCOUNT > 2;
    END LOOP;
END;

Tandai untuk Ditinjau

(1) Point

The first two functions in the user's schema will be recompiled.

The first three functions in the user's schema will be recompiled. (*)

The procedure will not compile successfully because you cannot ALTER functions using Dynamic SQL.

The procedure will not compile successfully because the syntax of the ALTER FUNCTION statement is incorrect.

All functions in the user's schema will be recompiled.

Correct

---

14. A programmer wants to code a procedure which will create a table with a single column. The datatype of the column will be chosen by the user who invokes the procedure. The programmer writes the following code:

CREATE OR REPLACE PROCEDURE create_tab
    (p_col_datatype IN VARCHAR2) IS
BEGIN
    CREATE TABLE newtab (only_col p_col_datatype);
END;

Why will this procedure not compile successfully?

Tandai untuk Ditinjau

(1) Point

Because you cannot create a table inside a procedure

Because the invoking user may not have CREATE TABLE privilege

Because when the procedure is compiled, Oracle cannot check if the parameter value passed into the procedure is a valid column datatype (*)

Because table NEWTAB may already exist

None of the above; the procedure will compile successfully.

Correct

---

15. Examine the following procedure, which drops a table whose name is passed as an IN parameter:

CREATE OR REPLACE PROCEDURE drop_tab
    (p_table_name IN VARCHAR2) IS
    v_sql_statement VARCHAR2(100);
BEGIN
    ...
END;

Which of the following will work correctly when coded in the procedure's executable section? (Choose two.)

Tandai untuk Ditinjau

(1) Point

v_sql_statement := 'DROP TABLE ' || p_table_name;
EXECUTE IMMEDIATE v_sql_statement;(*)

v_sql_statement := 'DROP TABLE ' || p_table_name;
EXECUTE IMMEDIATE 'v_sql_statement';

EXECUTE IMMEDIATE 'DROP TABLE p_table_name';

EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;(*)

v_sql_statement := 'DROP TABLE ';
EXECUTE IMMEDIATE v_sql_statement p_table_name;

1. The following statement is a valid example of using the RETURNING clause. True or False?

DECLARE
   TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE);
   emp_info EmpRec;
   emp_id NUMBER := 100;
BEGIN
   UPDATE employees
   SET salary = salary * 1.1 WHERE employee_id = emp_id;
     RETURNING last_name, salary INTO emp_info;
     dbms_output.put_line('Just gave a raise to ' || emp_info.last_name ||
     ', who now makes ' || emp_info.salary);
END;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

2. You want to take make a copy of all the cities in the world listed in the cities table, which contains millions of rows. The following procedure accomplishes this efficiently. True or False?

CREATE OR REPLACE PROCEDURE copy_cities IS
  TYPE t_cities IS TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER;
  v_citiestab t_emp;
BEGIN
  SELECT * BULK COLLECT INTO v_citiestab FROM cities;
  FORALL i IN v_citiestab.FIRST..v_citiestab.LAST
  INSERT INTO new_cities VALUES v_citiestab(i);
END copy_cities;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

3. A function-based index may be made using your own functions, but only if the function is created using the DETERMINISTIC clause. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

4. What does the RETURNING clause do in the example below?

CREATE OR REPLACE PROCEDURE new_dept
  (p_dept_name IN departments.name%TYPE) IS
  v_new_dept_id departments.dept_id%TYPE;
BEGIN
  INSERT INTO departments (dept_id, name)
    VALUES dept_seq.NEXTVAL, p_dept_name
    RETURNING dept_seq.CURRVAL INTO v_new_dept_id;
  DBMS_OUTPUT.PUT_LINE(p_dept_name ||' is department number ' || v_new_dept_id);
END new_dept;

Tandai untuk Ditinjau

(1) Point

Inserts the new department id in the department table

Uses the new department number in a cursor

Performs the SELECT statement to determine the department id of the new department (*)

Incorrect. Refer to Section 12 Lesson 2.

---

5. FORALL can only be used with the INSERT statement. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

6. The following example code will compile successfully. True or False?

CREATE OR REPLACE PROCEDURE dept_proc IS
  TYPE t_dept IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
BEGIN
  (p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept);
-- remaining code
END dept_proc;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

7. The following procedure compiles successfully. True or False?

CREATE OR REPLACE PACKAGE emp_pkg IS
  TYPE t_emp IS TABLE OF employees%ROWTYPE
    INDEX BY BINARY_INTEGER;
  PROCEDURE emp_proc
    (p_small_arg IN NUMBER, p_big_arg NOCOPY OUT t_emp);
...
END emp_pkg;

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 12 Lesson 2.

---

8. In the following example, where do you place the phrase BULK COLLECT?

...
BEGIN
  SELECT -- Position A
     salary -- Position B
     INTO v_saltab -- Position C
     FROM employees WHERE department_id = 20 ORDER BY salary
     -- Position D
;
...

Tandai untuk Ditinjau

(1) Point

Position A

Position B (*)

Position C

Position D

Correct

---

9. The DBMS_SQL package is easier to use than EXECUTE IMMEDIATE. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 12 Lesson 1.

---

10. A public packaged procedure contains the following SQL statement:
UPDATE employees
SET salary = salary * 1.1;
When is this SQL statement parsed?

Tandai untuk Ditinjau

(1) Point

When the package header is loaded into memory

When the package body is created (*)

When the package is loaded into memory

Only the first time the procedure is executed

When the package specification is created

11. What happens when a SQL statement is parsed? (Choose three.)

Tandai untuk Ditinjau

(1) Point

The user's required privileges are checked.(*)

The syntax of the statement is checked.(*)

The results of the statement are returned to the user.

The statement is executed.

Oracle queries the Data Dictionary to make sure that the tables referenced in the SQL statement exist.(*)

Incorrect. Refer to Section 12 Lesson 1.

---

12. A SQL statement can pass through several stages. Which of the following is NOT one of these stages?

Tandai untuk Ditinjau

(1) Point

EXECUTE

RETURN (*)

BIND

PARSE

FETCH

Incorrect. Refer to Section 12 Lesson 1.

---

13. What will happen when the following procedure is invoked?

CREATE OR REPLACE PROCEDURE do_some_work IS
    CURSOR c_curs IS SELECT object_name FROM user_objects
       WHERE object_type = 'FUNCTION';
BEGIN
    FOR v_curs_rec IN c_curs LOOP
       EXECUTE IMMEDIATE 'ALTER FUNCTION ' || v_curs_rec.object_name || ' COMPILE';
       EXIT WHEN c_curs%ROWCOUNT > 2;
    END LOOP;
END;

Tandai untuk Ditinjau

(1) Point

The first three functions in the user's schema will be recompiled. (*)

The first two functions in the user's schema will be recompiled.

The procedure will not compile successfully because you cannot ALTER functions using Dynamic SQL.

All functions in the user's schema will be recompiled.

The procedure will not compile successfully because the syntax of the ALTER FUNCTION statement is incorrect.

Correct

---

14. The following procedure adds a column of datatype DATE to the EMPLOYEES table. The name of the new column is passed to the procedure as a parameter.

CREATE OR REPLACE PROCEDURE addcol
    (p_col_name IN VARCHAR2) IS
    v_first_string VARCHAR2(100) := 'ALTER TABLE EMPLOYEES ADD (';
    v_second_string VARCHAR2(6) := ' DATE)';
BEGIN
    ... Line A
END;

Which of the following will work correctly when coded at line A? (Choose two.)

Tandai untuk Ditinjau

(1) Point

v_first_string := v_first_string || p_col_name;
EXECUTE IMMEDIATE v_first_string || v_second_string;(*)

v_first_string || p_col_name || v_second_string;

EXECUTE IMMEDIATE 'v_first_string' || p_col_name || 'v_second_string';

EXECUTE v_first_string || p_col_name || v_second_string;

EXECUTE IMMEDIATE v_first_string || p_col_name || v_second_string;(*)

Incorrect. Refer to Section 12 Lesson 1.

---

15. Only one call to DBMS_SQL is needed in order to drop a table. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

1. The following statement is a valid example of using the RETURNING clause. True or False?

DECLARE
   TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE);
   emp_info EmpRec;
   emp_id NUMBER := 100;
BEGIN
   UPDATE employees
   SET salary = salary * 1.1 WHERE employee_id = emp_id;
     RETURNING last_name, salary INTO emp_info;
     dbms_output.put_line('Just gave a raise to ' || emp_info.last_name ||
     ', who now makes ' || emp_info.salary);
END;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

2. In the following example, where do you place the phrase BULK COLLECT?

DECLARE
   TYPE NameList IS TABLE OF emp.ename%TYPE;
   names NameList;
   CURSOR c1 IS SELECT ename -- Position A
   FROM emp WHERE job = 'CLERK';
BEGIN
   OPEN c1;
   FETCH c1 -- Position B
INTO -- Position C
names;
   ...
   CLOSE c1;
END;

Tandai untuk Ditinjau

(1) Point

Position A

Position B (*)

Position C

Incorrect. Refer to Section 12 Lesson 2.

---

3. In the following example, where do you place the phrase DETERMINISTIC?

CREATE OR REPLACE FUNCTION total_sal
  (p_dept_id IN -- Position A
employees.department_id%TYPE)
  RETURN NUMBER -- Position B
    IS v_total_sal NUMBER;
BEGIN
  SELECT SUM(salary) INTO v_total_sal
    FROM employees WHERE department_id = p_dept_in;
  RETURN v_total_sal -- Position C;
END total_sal;

Tandai untuk Ditinjau

(1) Point

Position A

Position B (*)

Position C

Incorrect. Refer to Section 12 Lesson 2.

---

4. Where would you place the BULK COLLECT statement in the following example?

DECLARE
 TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
 dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id, location_id
  -- Position A
  FROM departments
  WHERE department_id > 70;
BEGIN
 OPEN c1
  -- Position B;
 FETCH c1
  -- Position C
 INTO dept_recs;
END;

Tandai untuk Ditinjau

(1) Point

Position A

Position B

Position C (*)

Incorrect. Refer to Section 12 Lesson 2.

---

5. You want to take make a copy of all the cities in the world listed in the cities table, which contains millions of rows. The following procedure accomplishes this efficiently. True or False?

CREATE OR REPLACE PROCEDURE copy_cities IS
  TYPE t_cities IS TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER;
  v_citiestab t_emp;
BEGIN
  SELECT * BULK COLLECT INTO v_citiestab FROM cities;
  FORALL i IN v_citiestab.FIRST..v_citiestab.LAST
  INSERT INTO new_cities VALUES v_citiestab(i);
END copy_cities;

Tandai untuk Ditinjau

(1) Point

True (*)

False

6. FORALL can only be used with the INSERT statement. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 12 Lesson 2.

---

7. A function-based index may be made using your own functions, but only if the function is created using the DETERMINISTIC clause. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

8. The following example code will compile successfully. True or False?

CREATE OR REPLACE PROCEDURE dept_proc IS
  TYPE t_dept IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
BEGIN
  (p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept);
-- remaining code
END dept_proc;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

9. Dynamic SQL enables data-definition, data-control, or session-control statements to be written and executed from PL/SQL.

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

10. Which of the following SQL statements can be included in a PL/SQL block only by using Dynamic SQL? (Choose two.)

Tandai untuk Ditinjau

(1) Point

ALTER(*)

DELETE

SELECT ..... FOR UPDATE NOWAIT

SAVEPOINT

GRANT(*)

11. When SQL statements are included within a procedure, the statements are parsed when the procedure is compiled. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

12. For which of the following is it necessary to use Dynamic SQL? (Choose three.)

Tandai untuk Ditinjau

(1) Point

ALTER(*)

UPDATE

GRANT(*)

SAVEPOINT

DROP(*)

Incorrect. Refer to Section 12 Lesson 1.

---

13. The DBMS_SQL package is easier to use than EXECUTE IMMEDIATE. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 12 Lesson 1.

---

14. Only one call to DBMS_SQL is needed in order to drop a table. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 12 Lesson 1.

---

15. A public packaged procedure contains the following SQL statement:
UPDATE employees
SET salary = salary * 1.1;
When is this SQL statement parsed?

Tandai untuk Ditinjau

(1) Point

When the package header is loaded into memory

When the package is loaded into memory

When the package specification is created

When the package body is created (*)

Only the first time the procedure is executed