Best Quality Database 11g 1Z0-146 exam

Q1. The result cache is enabled for the database instance. 

Examine the following code for a PL/SQL function: 

CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR 

RESULT_CACHE RELIES_ON (HR.EMPLOYEES) 

IS 

date_hired DATE; 

BEGIN 

SELECT hire_date INTO date_hired 

FROM HR.EMPLOYEES 

WHERE EMPLOYEE_ID = emp_id; 

RETURN TO_CHAR(date_hired); 

END; 

Which statement is true in this scenario? 

A. If sessions have different NLS_DATE_FORMAT settings, cached results have different formats. 

B. The function results are not cached because the query used in the function returns the DATE data type. 

C. If sessions have different NLS_DATE_FORMAT settings, cached results have same formats because the function's return type is VARCHAR. 

D. If a function is executed with same argument value but different NLS_DATE_FORMAT for the session, the cached result is overwritten with the new function result. 

View Answer

Q2. Which guidelines should be considered when designing and using cursors in a PL/SQL block? ? (Choose all that apply.) 

A. When fetching from a cursor, fetch into a record. 

B. Use parameters with cursors so that the result set for the cursor is not tied to a specific variable in a program. 

C. Use the %NOTFOUND attribute in combination with the SELECT INTO statement to check for non existent values. 

D. Whenever possible, explicitly declare the cursor and use the OPEN, FETCH and CLOSE statements to manipulate the cursor instead of using cursor FOR loop. 

E. When using data manipulation language statements, (DML) reference a SQL cursor attribute immediately after the DML statement executes in the same block. 

View Answer

Q3. Identify three guidelines for the DBMS_ASSERT package. (Choose three.) 

A. Prefix all calls to DBMS_ASSERT with the SYS schema name. 

B. Embed DBMS_ASSERT verification routines inside the injectable string. 

C. Escape single quotes when you use the ENQUOTE_LITERAL procedure. 

D. Define and raise exceptions explicitly to handle DBMS_ASSERT exceptions. 

E. Prefix all calls to DBMS_ASSERT with a schema name that owns the subprogram that uses the DBMS_ASSERT package. 

View Answer

Q4. The database instance was started up using the automatic memory management feature. No value was set for the RESULT_CACHE_MAX_SIZE parameter. 

Examine the following initialization parameter settings for your database: 

MEMORY_TARGET = 500M 

RESULT_CACHE_MODE = MANUAL 

You execute a query by using the result_cache hint. Which statement is true in this scenario? 

A. The query results are not stored because no memory is allocated for the result cache. 

B. The query results are stored and 0.5% of the memory target is allocated to the result cache. 

C. The query results are stored and 0.25% of the memory target is allocated to the result cache. 

D. The query results are not stored because the RESULT_CACHE_MODE parameter is not set to FORCE. 

View Answer

Q5. Which two statements are true about the inlining of PL/SQL subprograms? (Choose two.) 

A. Only local subroutines can be inlined. 

B. Inlining always reduces the execution time for a PL/SQL program unit. 

C. PLSQL_OPTIMIZE_LEVEL must be set to a value greater than or equal to 2. 

D. The PL/SQL programs that make use of relatively large helper subroutines are good candidates for inlining. 

View Answer

Q6. Which two statements are true about the tuning of PL/SQL code? (Choose two.) 

A. Redundant SQL statements in PL/SQL code should be avoided. 

B. Implicit data type conversion in PL/SQL code can improve performance. 

C. Usage of the NOT NULL constraint in PL/SQL code can degrade performance. 

D. If you have one PL/SQL program unit instead of multiple smaller executable sections, performance can be improved. 

View Answer

Q7. You executed this command to gather information about the memory allocation for storing query results: 

SQL> execute dbms_result_cache.memory_report 

View the Exhibit and examine the output for the execution of the 

DBMS_RESULT_CACHE.MEMORY_REPORT procedure. 

Which two statements are true about the output in the Exhibit? (Choose two.) 

A. In total, four blocks are used by the result cache. 

B. Currently 52 KB is allocated to the result cache. 

C. Currently 32 KB is allocated to the result cache. 

D. The result cache can be increased by 65628 bytes. 

View Answer

Q8. Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block. 

CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL, 

deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200), 

salary Number(8), 

deptname VARCHAR2(30) DEFAULT 'Sales') 

/ 

DECLARE 

SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE; 

v_emprec v_emprec_subtype; 

BEGIN 

v_emprec.empid := NULL; v_emprec.salary := 10000.002; 

v_emprec.deptid := 50; 

DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname); 

END; 

/ 

Which statements are true about the above PL/SQL block? (Choose two.) 

A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited. 

B. Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is inherited. 

C. Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of the decimal. 

D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited. 

View Answer

Q9. Identify the method that is used by fine-grained access (FGA). 

A. using policy functions to generate predicates dynamically 

B. creating triggers on corresponding tables to generate dynamic predicates 

C. modifying the existing application code to include a predicate for all SQL statements 

D. creating views with necessary predicates, and then creating synonyms with the same name as the tables 

View Answer

Q10. Which two statements are true about SecureFile LOB options? (Choose two.) 

A. The DECRYPT option can be used to remove the encryption only if the LOB column is empty. 

B. The KEEP_DUPLICATES option removes the deduplication effect only on new data in a LOB column. 

C. The KEEP_DUPLICATES option removes the deduplication effect on existing and new data in a LOB column. 

D. The DECRYPT option can be used to remove the encryption from LOB columns that are empty or contain data. 

View Answer