1. 	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? 
			
	C is invalid but A and B are still valid
	
			
	A, B and C are all invalid (*)
	
			
	B and C are invalid but A is still valid
	
			
	A, B and C are all still valid
	
			
	None of the above
	


2. 	A procedure show_emps contains the following declaration:
CURSOR emp_curs IS SELECT last_name, salary FROM employees;

What will happen to the procedure if a new column is added to the employees table?
	
					
			
	The procedure will still be valid and execute correctly because it does not reference the added column.
	
			
	The procedure will automatically be dropped and must be recreated.
	
			
	The procedure will be marked invalid and must be recompiled before it can be reexecuted. (*)
	
			
	Users' privileges to execute the procedure will automatically be revoked.

3. 	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.) 	
			
	departments is indirectly dependent on show_dept
	
			
	show_dept is directly dependent on emp_view (*)
	
			
	dept_view is directly dependent on departments (*)
	
			
	show_dept is indirectly dependent on departments (*)
	
			
	emp_view is directly dependent on show_dept

4. 	A single PL/SQL subprogram such as a procedure can be both a referenced object and a dependent object. True or False? 				
			
	True (*)
	
			
	False
	
				
5. 	Which data dictionary view shows information about references and dependencies? 	
			
	DEPTREE
	
			
	USER_DEPENDENCIES (*)
	
			
	USER_REFERENCES
	
			
	USER_LOCAL_DEPENDENCIES
	
 
6. 	Which of the following statements will show whether procedure myproc is valid or invalid? 	
			
	SELECT status FROM USER_OBJECTS
WHERE object_type = 'PROCEDURE'
AND object_name = 'MYPROC';

(*)
	
			
	SELECT status FROM USER_PROCEDURES
WHERE procedure_name = 'MYPROC';

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

	
			
	SELECT * FROM deptree;


7. 	Which of the following database objects are created when the utldtree.sql script is run? (Choose three.) 	
	The utldtree table
	
			
	The deptree_temptab table (*)
	
			
	The deptree and ideptree views (*)
	
			
	The deptree table
	
			
	The deptree_fill procedure (*)

	8. 	User ALICE owns a procedure show_emps which references table employees. Which of the following will generate information that shows this dependency? 	
			
	BEGIN deptree_fill('TABLE','EMPLOYEES');
END;

	
			
	BEGIN deptree_fill('PROCEDURE','ALICE','SHOW_EMPS');
END;

	
			
	BEGIN deptree_fill('ALICE','TABLE','EMPLOYEES');
END;

	
			
	BEGIN deptree_fill('TABLE','ALICE','EMPLOYEES');
END;

(*)
	
			
	BEGIN deptree_fill('ALICE','PROCEDURE','SHOW_EMPS');
END;


9. 	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.)

			
	QUERY_EMP is directly dependent on EMPLOYEES (*)
	
			
	ADD_EMP is directly dependent on EMPLOYEES
	
			
	ADD_EMP is directly depedent on EMP_VW (*)
	
			
	QUERY_EMP is directly dependent on ADD_EMP
	
			
	EMP_VW is directly dependent on EMPLOYEES (*)
	

10. 	The IDEPTREE view shows dependencies by indenting the lines of output instead of by using a NESTED_LEVEL column. True or False? 	Mark for Review 
(1) Points
					
			
	True (*)
	
			
	False
	

	11. 	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.) 	
			
	The procedure will be recompiled automatically the next time it is invoked. The recompilation will always be successful.

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

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

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

	
			
	The procedure does not need to be recompiled. 
12. 	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.)

					
			
	The table is dropped but a public table exists with the same name and structure. (*)
	
			
	The table is dropped.
	
			
	A new column is added to the table. (*)
	
			
	The table name is changed to newemps.
	
			
	The first_name column is dropped from the table.
	

	
				
13. 	Which of the following will NOT help to minimize dependency failures? (Choose two.) 	
					
					
			
	SELECTing a list of column names instead of using SELECT * (*)
	
			
	Declaring records using the %ROWTYPE attribute
	
			
	Including a column list with INSERT statements
	
			
	Declaring scalar variables with NOT NULL if the corresponding table column has a NOT NULL constraint (*)
	
			
	Declaring scalar variables using the %TYPE attribute
	
					
14. 	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?

					
			
	True
	
			
	False (*)
	