PL/SQL Section 14 Quiz

1. Which of the following statements will show whether procedure myproc is valid or invalid?

Tandai untuk Ditinjau

(1) Point

SELECT * FROM deptree;

SELECT status FROM USER_PROCEDURES
WHERE procedure_name = 'MYPROC';

SELECT status FROM USER_OBJECTS
WHERE object_type = 'PROCEDURE'
AND object_name = 'MYPROC'; (*)

SELECT valid FROM USER_OBJECTS
WHERE object_type = 'PROCEDURE'
AND object_name = 'MYPROC';

Incorrect. Refer to Section 14 Lesson 1.

---

2. A single procedure can be both a referenced object and a dependent object. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

3. A single PL/SQL subprogram such as a procedure can be both a referenced object and a dependent object. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

4. View dept_view is based on a select from table departments. Procedure show_dept contains code which selects from dept_view. Which of the following statements are true? (Choose three.)

Tandai untuk Ditinjau

(1) Point

emp_view is directly dependent on show_dept

dept_view is directly dependent on departments(*)

show_dept is indirectly dependent on departments(*)

departments is indirectly dependent on show_dept

show_dept is directly dependent on dept_view(*)

Incorrect. Refer to Section 14 Lesson 1.

---

5. Package EMPPACK contains a public procedure GET_EMP, which contains a reference to the EMPLOYEES table. Procedure CALL_EMP invokes EMPPACK.GET_EMP. The following SQL statement is executed:

ALTER TABLE employees ADD (gender CHAR(1));

Which one of the following statements is true?

Tandai untuk Ditinjau

(1) Point

The specification and body of EMPPACK are invalidated, but CALL_EMP remains valid.

The body of EMPPACK is invalidated, but the specification remains valid. (*)

Nothing is invalidated because the PL/SQL code does not reference the GENDER column.

EMPPACK.GET_EMP is invalidated, but other procedures in EMPPACK remain valid.

Incorrect. Refer to Section 14 Lesson 1.

6. Which of the following database objects are created when the utldtree.sql script is run? (Choose three.)

Tandai untuk Ditinjau

(1) Point

The deptree and ideptree views(*)

The utldtree table

The deptree_temptab table(*)

The deptree table

The deptree_fill procedure(*)

Incorrect. Refer to Section 14 Lesson 1.

---

7. Which of the following techniques will make it more likely that an invalidated PL/SQL subprogram will recompile successfully? (Choose two.)

Tandai untuk Ditinjau

(1) Point

Declaring record structures using %ROWTYPE(*)

Using a cursor FOR loop instead of opening and closing the cursor explicitly

SELECTing a list of column-names instead of using SELECT *

Including a column list with INSERT statements(*)

Incorrect. Refer to Section 14 Lesson 1.

---

8. Package emp_pack contains two public procedures: get_emps and upd_emps. A separate procedure emp_proc invokes emp.pack.get_emps. The upd_emps package body code is now altered, and the package body (but not the package specification) is recreated.

emp_proc will be marked invalid and needs to be recompiled. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 1.

---

9. Which data dictionary view shows information about references and dependencies?

Tandai untuk Ditinjau

(1) Point

USER_LOCAL_DEPENDENCIES

DEPTREE

USER_DEPENDENCIES (*)

USER_REFERENCES

Incorrect. Refer to Section 14 Lesson 1.

---

10. Examine the following code:

CREATE FUNCTION deptfunc
RETURN NUMBER IS
    v_count NUMBER(6);
BEGIN
    SELECT COUNT(*) INTO v_count FROM departments;
    RETURN v_count;
END;

Which of the following will display the dependency between DEPTFUNC and DEPARTMENTS?

Tandai untuk Ditinjau

(1) Point

SELECT name, type
FROM user_dependencies
WHERE name IN ('DEPTFUNC','DEPARTMENTS');

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'DEPARTMENTS'
AND type = 'TABLE';

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'DEPARTMENTS'
AND referenced_type = 'TABLE'; (*)

SELECT object_name, object_type
FROM user_objects
WHERE object_name IN ('DEPARTMENTS','DEPTFUNC')
AND referenced = 'YES';

Incorrect. Refer to Section 14 Lesson 1.

11. In this scenario, the following status is given for each procedure:

- Procedure A is local and has a time stamp of 10 AM
- Procedure B is remote and has a local time stamp of 5 AM and has a remote time stamp of 4 AM

In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 2.

---

12. In this scenario, the following status is given for each procedure:

- Procedure A is local and has a time stamp of 10 AM
- Procedure B is remote and has a local and remote time stamp of 10:30 AM

In Timestamp Mode, Procedure A, which is dependent on Procedure B, will execute successfully at 11 AM. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

13. With remote dependencies, which of the following is True?

Tandai untuk Ditinjau

(1) Point

The dependency status is not recorded.

There are two separate data dictionaries on two different servers. (*)

There is one master data dictionary that resides on one server which identifies the status of all schema objects.

Incorrect. Refer to Section 14 Lesson 2.

---

14. In this scenario, the following status is given for each procedure:

- Procedure A is local, executed, and invalidated because the remote Procedure B time stamp does not match the local time stamp for Procedure B
- Procedure A is recompiled.

In Timestamp Mode, now Procedure A will execute successfully. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

15. A change in a remote referenced subprogram is automatically recorded as invalid if its base object changes and that new status is relayed to the dependent object's status and automatically marked as invalid. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 2. 

1. A procedure includes the following code:

CURSOR loc_curs IS SELECT location_id, city, country_id FROM locations;

Which of the following changes to the LOCATIONS table will allow the procedure to be recompiled successfully without editing its code? (Choose two.)

Tandai untuk Ditinjau

(1) Point

ALTER TABLE locations ADD (climate VARCHAR2(30));(*)

RENAME locations TO new_locations;

ALTER TABLE locations DROP COLUMN postal_code;(*)

ALTER TABLE locations DROP COLUMN city;

Incorrect. Refer to Section 14 Lesson 1.

---

2. Which of the following is NOT created when the utldtree.sql script is run?

Tandai untuk Ditinjau

(1) Point

The DEPTREE_FILL procedure

The DEPTREE_TEMPTAB table

The DEPTREE view

The USER_DEPENDENCIES view (*)

Incorrect. Refer to Section 14 Lesson 1.

---

3. A SELECT from the DEPTREE table displays table LOCATIONS at nested level 0 and procedure LOCPROC at nested level 2. This shows that LOCPROC is directly dependent on LOCATIONS. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 1.

---

4. Which of the following will display dependency information which has been generated by executing the DEPTREE_FILL procedure? (Choose two.)

Tandai untuk Ditinjau

(1) Point

The DISPLAY_DEPTREE view

The IDEPTREE view(*)

The DEPTREE view(*)

The USER_DEPENDENCIES view

The UTLDTREE script

Incorrect. Refer to Section 14 Lesson 1.

---

5. Which of the following will display only the number of invalid package bodies in your schema?

Tandai untuk Ditinjau

(1) Point

SELECT COUNT(*) FROM user_objects
WHERE object_type LIKE 'PACKAGE%'
AND status = 'INVALID';

SELECT COUNT(*) FROM user_packages
WHERE status = 'INVALID';

SELECT COUNT(*) FROM user_objects
WHERE object_type = 'PACKAGE BODY'
AND status = 'INVALID'; (*)

SELECT COUNT(*) FROM user_dependencies
WHERE type = 'PACKAGE BODY'
AND status = 'INVALID';

Incorrect. Refer to Section 14 Lesson 1.

6. Which data dictionary view shows information about references and dependencies?

Tandai untuk Ditinjau

(1) Point

USER_DEPENDENCIES (*)

USER_REFERENCES

DEPTREE

USER_LOCAL_DEPENDENCIES

Correct

---

7. A SELECT from DEPTREE produced the following output.

>NESTED_LEVEL	>TYPE	>NAME
>0	>TABLE	>EMPLOYEES
>1	>VIEW	>EMP_VW
>2	>PROCEDURE	>ADD_EMP
>1	>PROCEDURE	>QUERY_EMP

What dependencies does this show? (Choose three.)

Tandai untuk Ditinjau

(1) Point

ADD_EMP is directly dependent on EMP_VW(*)

QUERY_EMP is directly dependent on ADD_EMP

EMP_VW is directly dependent on EMPLOYEES(*)

QUERY_EMP is directly dependent on EMPLOYEES(*)

ADD_EMP is directly dependent on EMPLOYEES

Incorrect. Refer to Section 14 Lesson 1.

---

8. A single procedure can be both a referenced object and a dependent object. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

9. User BOB wants to know which objects reference his DEPARTMENTS table. Which of the following must he execute to populate the DEPTREE_TEMPTAB table?

Tandai untuk Ditinjau

(1) Point

BEGIN
    ideptree('TABLE','BOB','DEPARTMENTS');
END;

BEGIN
    utldtree('DEPARTMENTS');
END;

BEGIN
    deptree_fill('TABLE','BOB','DEPARTMENTS');
END; (*)

BEGIN
    deptree_fill('TABLE','DEPARTMENTS');
END;

Incorrect. Refer to Section 14 Lesson 1.

---

10. View dept_view is based on a select from table departments. Procedure show_dept contains code which selects from dept_view. Which of the following statements are true? (Choose three.)

Tandai untuk Ditinjau

(1) Point

show_dept is indirectly dependent on departments(*)

show_dept is directly dependent on dept_view(*)

dept_view is directly dependent on departments(*)

emp_view is directly dependent on show_dept

departments is indirectly dependent on show_dept

11. The Signature of a procedure is calculated from :

Tandai untuk Ditinjau

(1) Point

The name of the procedure.

The modes of the parameters.

The data types of the parameters.

All of these make up the signature. (*)

Incorrect. Refer to Section 14 Lesson 2.

---

12. In this scenario, the following status is given for each procedure:

- Procedure A is local and has a time stamp of 10 AM
- Procedure B is remote and has a local and remote time stamp of 10:30 AM

In Timestamp Mode, Procedure A, which is dependent on Procedure B, will execute successfully at 11 AM. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

13. If two related objects are in different databases, the dependency between them is automatically recorded on the dependent object's data dictionary. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 2.

---

14. Which statement for setting a database parameter is the default for remote dependency checking?

Tandai untuk Ditinjau

(1) Point

ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP (*)

ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE

ALTER SESSION REMOTE_DEPENDENCIES_MODE = TIMESTAMP

ALTER SESSION REMOTE_DEPENDENCIES_MODE = SIGNATURE

Correct

---

15. Which is not a mode that can be used to determine the dependency status of schema objects when dealing with remote dependencies?

Tandai untuk Ditinjau

(1) Point

Time Stamp Mode

All of these are valid.

Time Mode (*)

Signature Mode

1. Examine the following code:

CREATE VIEW ed_view AS
    SELECT * FROM employees NATURAL JOIN departments;
CREATE PROCEDURE ed_proc IS
   CURSOR ed_curs IS SELECT * FROM ed_view;

Which of the following statements about dependencies are true? (Choose two.)

Tandai untuk Ditinjau

(1) Point

ED_PROC is indirectly dependent on DEPARTMENTS.(*)

ED_PROC is directly dependent on EMPLOYEES.

EMPLOYEES is referenced by ED_VIEW.(*)

ED_PROC is referenced by ED_VIEW.

ED_CURS is directly dependent on ED_VIEW.

Incorrect. Refer to Section 14 Lesson 1.

---

2. Procedure get_depts has been marked invalid because one of the objects it references has been altered. Which of the following statements are true? (Choose two.)

Tandai untuk Ditinjau

(1) Point

The procedure can be recompiled manually by:
ALTER PROCEDURE get_depts RECOMPILE;

The procedure will be recompiled automatically the next time it is invoked. The recompilation may or may not be successful.(*)

The procedure does not need to be recompiled.

The procedure can be recompiled manually by:
ALTER PROCEDURE get_depts COMPILE;(*)

The procedure will be recompiled automatically the next time it is invoked. The recompilation will always be successful.

Incorrect. Refer to Section 14 Lesson 1.

---

3. A procedure includes the following code:

SELECT first_name, salary INTO v_first_name, v_salary
FROM employees WHERE employee_id = 100;

Which of the following changes to the employees table will allow the procedure to be recompiled successfully ? (Choose two.)

Tandai untuk Ditinjau

(1) Point

The table name is changed to newemps.

The table is dropped.

A new column is added to the table.(*)

The table is dropped but a public table exists with the same name and structure.(*)

The first_name column is dropped from the table.

Incorrect. Refer to Section 14 Lesson 1.

---

4. Examine the following code:

CREATE FUNCTION deptfunc
RETURN NUMBER IS
    v_count NUMBER(6);
BEGIN
    SELECT COUNT(*) INTO v_count FROM departments;
    RETURN v_count;
END;

Which of the following will display the dependency between DEPTFUNC and DEPARTMENTS?

Tandai untuk Ditinjau

(1) Point

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'DEPARTMENTS'
AND type = 'TABLE';

SELECT name, type
FROM user_dependencies
WHERE name IN ('DEPTFUNC','DEPARTMENTS');

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'DEPARTMENTS'
AND referenced_type = 'TABLE'; (*)

SELECT object_name, object_type
FROM user_objects
WHERE object_name IN ('DEPARTMENTS','DEPTFUNC')
AND referenced = 'YES';

Incorrect. Refer to Section 14 Lesson 1.

---

5. A SELECT from the DEPTREE table displays table LOCATIONS at nested level 0 and procedure LOCPROC at nested level 2. This shows that LOCPROC is directly dependent on LOCATIONS. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 1.

6. When a table is dropped, all PL/SQL subprograms that reference the table are automatically dropped. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 1.

---

7. Which of the following will display dependency information which has been generated by executing the DEPTREE_FILL procedure? (Choose two.)

Tandai untuk Ditinjau

(1) Point

The UTLDTREE script

The DISPLAY_DEPTREE view

The DEPTREE view(*)

The IDEPTREE view(*)

The USER_DEPENDENCIES view

Incorrect. Refer to Section 14 Lesson 1.

---

8. PL/SQL procedure A invokes procedure B, which in turn invokes procedure C, which references table T. If table T is dropped, which of the following statements is true?

Tandai untuk Ditinjau

(1) Point

A, B and C are all still valid

B and C are invalid but A is still valid

C is invalid but A and B are still valid

None of these.

A, B and C are all invalid (*)

Incorrect. Refer to Section 14 Lesson 1.

---

9. A single PL/SQL subprogram such as a procedure can be both a referenced object and a dependent object. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

10. The IDEPTREE view shows dependencies by indenting the lines of output instead of by using a NESTED_LEVEL column. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

11. Procedure B has a new IN OUT parameter added to it. It compiles successfully. Procedure A is dependent on Procedure B. In Signature mode,what will happen?

Tandai untuk Ditinjau

(1) Point

Both procedures will execute successfully.

Procedure A will fail when executed. (*)

Procedure B will fail when executed.

Both procedures will fail.

Incorrect. Refer to Section 14 Lesson 2.

---

12. Procedure B has the ZERO_DIVIDE pre-defined exception added to its EXCEPTION section. It is compiled successfully. In Timestamp Mode, Procedure A, which is dependent on remote Procedure B, will compile and execute successfully. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 14 Lesson 2.

---

13. In Signature Mode, a compiled procedure would be invalidated if its dependent procedure has a parameter data type change from NUMBER to _________________.

Tandai untuk Ditinjau

(1) Point

BINARY_INTEGER

DECIMAL

INTEGER

VARCHAR2 (*)

Incorrect. Refer to Section 14 Lesson 2.

---

14. Procedure B has its local variable emp_number changed to emp_name. The data type of emp_id is changed from number to integer. It is compiled successfully. In Signature Mode, Procedure A, which is dependent on remote Procedure B, will compile and execute successfully. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

15. Which Data Dictionary table stores information about a procedure's timestamp?

Tandai untuk Ditinjau

(1) Point

USER_TIMESTAMP

USER_PROCEDURES

USER_OBJECTS (*)

USER_MODE