PLSQL Final Exam Semester 2

1. You have created several directory objects in the database, as pointers to operating system directories which contain BFILEs. Which data dictionary view would you query to see these directories?
USER_DIRECTORIES
USER_BFILES
ALL_DIRECTORIES (*)
USER_EXTERNAL_FILES
ALL_BFILES

2. Which of the following statements about BFILEs are NOT true? (Choose two.) (Choose all correct answers)
They are stored outside the database.
We can read BFILE data using the DBMS_LOB package.
We can grant SELECT object privilege on them. (*)
We can read BFILE data in a SELECT statement. (*)
The database contains a locator which points to the external BFILE.

3. The database administrator has created a directory as follows: CREATE DIRECTORY filesdir AS 'C:\BFILEDIR';
How would the DBA allow all database users to query the BFILEs in this directory?

GRANT READ ON filesdir TO PUBLIC;
GRANT READ ON DIRECTORY filesdir TO PUBLIC; (*)
GRANT SELECT ON filesdir TO PUBLIC;
GRANT QUERY ON DIRECTORY filesdir TO PUBLIC;
GRANT READ ON 'C:\BFILEDIR' TO PUBLIC;

4. Table NEWEMP contains a PHOTO_ID column of datatype LONG RAW. Which of the following will convert this column to a suitable new LOB datatype?
ALTER TABLE newemp COLUMN (photo_id BLOB);
ALTER TABLE newemp MODIFY (photo_id BFILE);
ALTER TABLE newemp MODIFY (photo_id BLOB); (*)
ALTER TABLE newemp DROP COLUMN (photo_id);
ALTER TABLE newemp ADD (photo_id BLOB);
ALTER TABLE newemp COLUMN (photo_id LONG RAW);

5. Which of the following methods can be used to query CLOB data values? (Choose two.) (Choose all correct answers)
SELECT (*)
DBMS_LOB.PUT
DBMS_LOB.GETLENGTH
DBMS_LOB.READ (*)

6. You need to store very large amounts of text data in a table column inside the database. Which datatype should you use for this column?
CLOB (*)
BLOB
LONG
VARCHAR2(4000)

 None of the above

7. BLOB, JPEG, BFILE and MP3 are all LOB column datatypes. True or False?
True
False (*)

8. CLOB and BLOB are internal LOB datatypes, while BFILE is an external LOB datatype. True or False?
True (*)
False

9. Which of the following successfully declares an INDEX BY table named DEPT_NAMES_TAB, which could be used to store all the department names from the DEPARTMENTS table?
DECLARE
TYPE t_dnames IS TABLE OF
departments.department_name%TYPE
INDEX BY INTEGER;
dept_names_tab t_dnames;


 DECLARE
TYPE t_dnames IS TABLE OF
departments.department_name%TYPE
INDEX BY BINARY_INTEGER;
dept_names_tab t_dnames; (*)

 DECLARE
TYPE t_dnames IS TABLE OF
departments.department_name%TYPE
INDEX BY PLS_INTEGER;
dept_names_tab t_dnames%TYPE;

DECLARE
TYPE t_dnames IS TABLE OF
department_name
INDEX BY BINARY_INTEGER;
dept_names_tab t_dnames;

10. The following code declares an INDEX BY table and populates it with employees' salaries, using the employee_id as the BINARY_INTEGER index of the table:
DECLARE
TYPE t_emp_sals IS TABLE OF employees.salary%TYPE
INDEX BY BINARY_INTEGER;
emp_sals_tab t_emp_sals;

BEGIN
FOR v_emprec IN (SELECT employee_id, salary FROM employees)
LOOP
-- Line A
END LOOP;
END;
What must be coded at Line A?

emp_sals_tab(employee_id) := v_emprec.salary;
t_emp_sals(v_emprec.employee_id) := v_emprec.salary;
emp_sals_tab(v_emprec.employee_id) := v_emprec.salary; (*)
emp_sals_tab(i) := v_emprec.salary;

11. Examine the following code:
DECLARE
CURSOR emp_curs IS
SELECT employee_id, first_name, last_name FROM employees;
TYPE t_mytype IS TABLE OF -- Point A
INDEX BY BINARY_INTEGER;
v_mytab t_mytype;
Which of the following can be coded at Point A?

employees%ROWTYPE
employees.salary%TYPE

emp_curs%ROWTYPE
Any one of the above (*)
None of the above

12. Package ED_PACK has declared a record type named ED_TYPE in the package specification. Which of the following anonymous blocks successfully declares a variable whose datatype is ED_TYPE? Mark for
DECLARE
v_ed_rec IS RECORD ed_pack.ed_type;
BEGIN ...

DECLARE
v_ed_rec ed_pack.ed_type;
BEGIN ...(*)

 DECLARE
v_ed_rec ed_pack.ed_type%ROWTYPE;
BEGIN...

DECLARE
v_ed_rec ed_pack.ed_type%TYPE;
BEGIN ...
None of the above. Variables of datatype ED_TYPE can be declared only within ED_PACK, not in separate subprograms or anonymous blocks.

13. Which of the following will declare a composite PL/SQL data type named COMPO_TYPE, containing two fields named FIELD1 and FIELD2?

DECLARE
compo_type
(field1 NUMBER,
field2 VARCHAR2(30));

DECLARE
TYPE compo_type IS
(field1 NUMBER,
field2 VARCHAR2(30));


DECLARE
TYPE compo_type IS RECORD
(field1 NUMBER,
field2 VARCHAR2(30)); (*)

 DECLARE
 compo_type IS RECORD
(field1 NUMBER,
field2 VARCHAR2(30));

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

The specification and body of EMPPACK are invalidated, but CALL_EMP remains valid.
The body of EMPPACK is invalidated, but the specification remains valid. (*)
EMPPACK.GET_EMP is invalidated, but other procedures in EMPPACK remain valid.
Nothing is invalidated because the PL/SQL code does not reference the GENDER column.

15. 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.) (Choose all correct answers)

ED_PROC is indirectly dependent on DEPARTMENTS (*)
EMPLOYEES is referenced by ED_VIEW (*)
ED_CURS is directly dependent on ED_VIEW
ED_PROC is referenced by ED_VIEW
ED_PROC is directly dependent on EMPLOYEES

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

True
False (*)

17. Which of the following will display dependency information which has been generated by executing the DEPTREE_FILL procedure? (Choose two.) (Choose all correct answers)
The USER_DEPENDENCIES view
The DEPTREE view (*)
The UTLDTREE script
The DISPLAY_DEPTREE view
The IDEPTREE view (*)


 18. 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 proc
edure to be recompiled successfully without editing its code? (Choose two.) (Choose all correct answers)
RENAME locations TO new_locations;
ALTER TABLE locations ADD (climate VARCHAR2(30)); (*)
ALTER TABLE locations DROP COLUMN city;
ALTER TABLE locations DROP COLUMN postal_code; (*)

19. Which of the following is NOT created when the utldtree.sql script is run?
The DEPTREE view
The DEPTREE_FILL procedure
The USER_DEPENDENCIES view (*)
The DEPTREE_TEMPTAB table

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

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 name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'DEPARTMENTS'
AND type = 'TABLE';

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