1. 	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? 	Mark for Review 
(1) Points
					
			
	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;

	
					
				
Correct		Correct
	
					
		2. 	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? 	Mark for Review 
(1) Points
					
			
	An INDEX BY table
	
			
	A record
	
			
	An INDEX BY table of records (*)
	
			
	A CLOB
	
			
	An explicit cursor
	
					
				
Correct		Correct
	
					
		3. 	An INDEX BY table of records can store a maximum of 255 records. True or False? 	Mark for Review 
(1) Points
					
			
	True
	
			
	False (*)
	
					
				
Correct		Correct
	
					
		4. 	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?
	Mark for Review 
(1) Points
					
			
	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;
	
					
				
Correct		Correct
	
					
		5. 	Which of the following statements about BFILEs are NOT true? (Choose two.) 	Mark for Review 
(1) Points
					
			(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.
	
					
				
Correct		Correct
	
					
		6. 	BFILEs are stored outside the database and can be queried and updated by using procedures in DBMS_LOB. True or False? 	Mark for Review 
(1) Points
					
			
	True
	
			
	False (*)
	
					
				
Incorrect		Incorrect. Refer to Section 11.
	
					
		7. 	Which of the following methods can be used to query CLOB data values? (Choose two.) 	Mark for Review 
(1) Points
					
			(Choose all correct answers)	
					
			
	SELECT (*)
	
			
	DBMS_LOB.PUT
	
			
	DBMS_LOB.GETLENGTH
	
			
	DBMS_LOB.READ (*)
	
					
				
Incorrect		Incorrect. Refer to Section 11.
	
					
		8. 	You need to add a new column to the EMPLOYEES table. This column will store each employee's favourite movie. A movie can be up to 4GB in size and the movies will be stored inside the database for better security. Which datatype must you use for this column? 	Mark for Review 
(1) Points
					
			
	CLOB
	
			
	BLOB (*)
	
			
	LONG RAW
	
			
	BFILE
	
			
	LONG
	
					
				
Correct		Correct
	
					
		9. 	A LONG column can be converted to CLOB using a single ALTER TABLE command. True or False? 	Mark for Review 
(1) Points
					
			
	True (*)
	
			
	False
	
					
				
Correct		Correct
	
					
		10. 	The JOB_APPLICANTS table contains two columns:
(applicant_id NUMBER PRIMARY KEY,
resume CLOB)

For applicant_id 100, we want to modify the value of the RESUME column value from "I worked for Oracle" to "I worked for Oracle for five years".

Which of the following will do this successfully? (Choose two.)
	Mark for Review 
(1) Points
					
			(Choose all correct answers)	
					
			
	UPDATE job_applicants
SET SUBSTR(resume, 21,14) = 'for five years'
WHERE candidate_id = 100;

	
			
	UPDATE job_applicants
SET resume = 'I worked for Oracle for five years'
WHERE candidate_id = 100;

(*)
	
			
	DECLARE
    v_locator CLOB;
BEGIN
    v_locator := 'I worked for Oracle for five years';
    UPDATE job_applicants
    SET resume = DBMS_LOB.WRITE(v_locator)
    WHERE candidate_id = 100;
END;

	
			
	DECLARE
    v_lobloc CLOB;
BEGIN
    SELECT resume INTO v_lobloc FROM job_applicants
       WHERE applicant_id = 100;
    DBMS_LOB.WRITE(v_lobloc,14,21,'for five years');
END;

(*)
	
					
				
Correct		Correct 
11. 	Which of the following best describes the difference between BLOB and BFILE data? 	Mark for Review 
(1) Points
					
			
	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.
	
					
				
Correct		Correct
	
					
		12. 	Which of the following will declare a composite PL/SQL data type named COMPO_TYPE, containing two fields named FIELD1 and FIELD2? 	Mark for Review 
(1) Points
					
			
	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));

	
					
				
Correct		Correct
	
					
		13. 	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 Review 
(1) Points
					
			
	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.

	
					
				
Correct		Correct
	
					
					
	Section 12
					
		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?
	Mark for Review 
(1) Points
					
			
	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.
	
					
				
Correct		Correct
	
					
		15. 	Which of the following techniques will make it more likely that an invalidated PL/SQL subprogram will recompile successfully? (Choose two.) 	Mark for Review 
(1) Points
					
			(Choose all correct answers)	
					
			
	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 (*)
	
					
				
Correct		Correct
	
					
		16. 	Which of the following will display the number of invalid package bodies in your schema? 	Mark for Review 
(1) Points
					
			
	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';

	
					
				
Correct		Correct
	
					
		17. 	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?
	Mark for Review 
(1) Points
					
			
	True
	
			
	False (*)
	
					
				
Correct		Correct
	
					
		18. 	Which of the following is NOT created when the utldtree.sql script is run? 	Mark for Review 
(1) Points
					
			
	The DEPTREE view
	
			
	The DEPTREE_FILL procedure
	
			
	The USER_DEPENDENCIES view (*)
	
			
	The DEPTREE_TEMPTAB table
	
					
				
Correct		Correct
	
					
		19. 	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.)
	Mark for Review 
(1) Points
					
			(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
	
					
				
Incorrect		Incorrect. Refer to Section 12.
	
					
		20. 	Function FETCH_EMP references the EMPLOYEES table. The table is modified by:
ALTER TABLE employees ADD (resume CLOB);

When will the ORACLE server try to recompile FETCH_EMP automatically?
	Mark for Review 
(1) Points
					
			
	When the command ALTER FUNCTION fetch_emp COMPILE; is executed
	
			
	The next time a user session invokes FETCH_EMP (*)
	
			
	When the RESUME column is dropped from the EMPLOYEES table
	
			
	When FETCH_EMP is dropped and recreated
	
					
				
Correct		Correct 