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. 	BFILEs are stored outside the database and can be queried and updated by using procedures in DBMS_LOB. True or False? 	
	True
	
			
	False (*)
	
					
		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. 	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;
				
		5. 	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
			
		6. 	We want to store a complete copy of the DEPARTMENTS table (all the rows and all the columns) in a single PL/SQL variable. Which of the following kinds of variable could we use? 
			
	An INDEX BY table
	
			
	A record
	
			
	An INDEX BY table of records (*)
	
			
	A CLOB
	
			
	An explicit cursor
	
					
		7. 	A PL/SQL package named MYPACK declares a record type named MYTYPE as a public variable in the package specification. Which of the following anonymous blocks successfully declares a local variable of datatype MYTYPE? 			
	DECLARE
    v_myrec IS RECORD mypack.mytype;
BEGIN ...

				
	DECLARE
    v_myrec mypack.mytype;
BEGIN ...

(*)
				
	DECLARE
    v_myrec mytype;
BEGIN ...
			
	DECLARE
    v_myrec IS RECORD (mypack.mytype);
BEGIN ...

					
		8. 	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));
				
		9. 	CLOB and BLOB are internal LOB datatypes, while BFILE is an external LOB datatype. True or False? 	Mark for Review 
(1) Points
					
			
	True (*)
	
			
	False
	
				
		10. 	Which of the following methods can be used to query CLOB data values? (Choose two.) 					
			
	SELECT (*)
	
			
	DBMS_LOB.PUT
	
			
	DBMS_LOB.GETLENGTH
	
			
	DBMS_LOB.READ (*)

11. 	BLOB, JPEG, BFILE and MP3 are all LOB column datatypes. True or False? 	
			
	True
	
			
	False (*)
	
					
		12. 	Which of the following best describes the difference between BLOB and BFILE data? 	
	A BLOB can contain text data while a BFILE cannot.
	
			
	BLOB data is stored inside the database, while BFILE data is outside the database in separate operating system files. (*)
	
			
	The maximum size of a BLOB is 2GB; a BFILE can be up to 128TB if needed.
	
			
	A table can contain several BLOB columns but only one BFILE column.
	
			
	There is no difference between a BLOB and a BFILE.
				
		13. 	A LONG column can be converted to CLOB using a single ALTER TABLE command. True or False? 	Mark for Review 
(1) Points
					
			
	True (*)
	
			
	False

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

					
		15. 	The PL/SQL variable V_LAST_NAME is used to store fetched values of the LAST_NAME column of the EMPLOYEES table. To minimize dependency failures, the variable should be declared as:

v_last_name VARCHAR2(25);

True or False?
	
	True
	
			
	False (*)
	

					
		16. 	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
	
		
		17. 	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.)
				
			
	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

					
		18. 	User BOB wants to know which objects reference his DEPARTMENTS table. Which of the following must he execute to populate the DEPTREE_TEMPTAB table? 	
			
	BEGIN
    utldtree('DEPARTMENTS');
END;
		
	BEGIN
    deptree_fill('TABLE','BOB','DEPARTMENTS');
END;

(*)
			
	BEGIN
    deptree_fill('TABLE','DEPARTMENTS');
END;	
			
	BEGIN
    ideptree('TABLE','BOB','DEPARTMENTS');
END;
	
					
		19. 	Which of the following will display the number of invalid package bodies in your schema? 				
			
	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';

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

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

	
					
		20. 	A single procedure can be both a referenced object and a dependent object. True or False? 				
			
	True (*)
			
	False