Q1. Examine the structure of the EMPLOYEES table that exists in your schema. Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
DEPARTMENT_ID NUMBER(4)
You successfully create a GET_MAX procedure to find the maximum salary in the department of a specified employee.
You then code a PL/SQL block to display the maximum salary in the departments of the first five employees in the EMPLOYEES table.
View the Exhibit. Examine the procedure and the block of PL/SQL code.
What is the outcome of executing the block of PL/SQL code?
A. It executes successfully and gives the required output.
B. It gives an error because ROWNUM cannot be used in cursor definitions.
C. It gives an error because usage of the %ROWCOUNT attribute is not valid.
D. It executes successfully, but does not give the required output because the procedure call resets the %ROWCOUNT value.
Answer: A
Q2. 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.
Answer: C
Q3. Examine the commands:
CREATE TYPE typ_course_tab IS VARRAY(5) OF VARCHAR2(20)
/ CREATE TYPE typ_course_nst AS TABLE OF typ_course_tab / CREATE TABLE faculty (faculty_id NUMBER(5), faculty_name VARCHAR2(30), courses typ_course_nst) NESTED TABLE courses STORE AS course_stor_tab / INSERT INTO faculty VALUES (101, 'Jones', NULL); UPDATE (SELECT courses FROM faculty WHERE faculty_id=101) SET courses = typ_course_nst(11,'Oracle'); Which statement is true about the execution of these commands?
A. All the commands execute successfully.
B. Only the first two commands execute successfully. C. Only the first four commands execute suc cessfully. D. Only the first three commands execute successfully.
Answer: C
Q4. Identify two strategies against SQL injection. (Choose two.)
A. Using parameterized queries with bind arguments.
B. Use subprograms that are run with the definer's right.
C. Use RESTRICT_REFERENCE clauses in functions that use dynamic SQLs.
D. Validate user inputs to functions that use dynamic SQLs built with concatenated values.
Answer: A,D
Q5. You have an OE_ACCESS_ORDERS_POLICY security policy implemented on the ORDERS table in the OE schema. The user sessions are able to access only the desired rows. The database administrator (DBA) uses the following command:
SQL> EXECUTE
DBMS_RLS.ENABLE_POLICY('OE','ORDERS','OE_ORDERS_ACCESS_POLICY',FALSE);
Which statement is true about user sessions that are connected currently?
A. The security policy remains in effect till the end of the current session.
B. The subsequent queries on the ORDERS table produce an ORA-01031: insufficient privileges error.
C. The subsequent queries on the ORDERS table within the current session are not controlled by the security policy.
D. The subsequent queries on the ORDERS table produce an ORA-28112: failed to execute policy function error.
Answer: C
Q6. View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
Examine the PL/SQL block that you execute to find the average salary for employees in the
'Sales' department:
DECLARE
TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20);
v_emp_sal emp_sal;
PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS
BEGIN
SELECT AVG(salary) INTO p_arr(p_dept_name)
FROM employees WHERE department_id=
(SELECT department_id FROM departments
WHERE department_name=p_dept_name);
END get_sal; BEGIN
get_sal('Sales',v_emp_sal);
DBMS_OUTPUT.PUT_LINE( v_emp_sal('Sales'));
END;
/
What is the outcome?
A. It executes successfully and gives the correct output.
B. It generates an error because the associative array definition is not valid.
C. It generates an error because an associative array cannot be passed to a procedure in OUT mode.
D. It generates an error because an associative array cannot be used with the SELECT INTO statement.
Answer: A
Q7. Which two statements are true about the query results stored in the query result cache? (Choose two.)
A. If any of the tables used to build a query is modified by an ongoing transaction in the current session, the query result is not cached.
B. A query result based on a read-consistent snapshot of data that is older than the latest committed version of the data is not cached.
C. Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.
D. A query result for a query that has a bind variable is stored in the cache and is reused if the query is equivalent even when the bind variable has a different value.
Answer: A,B
Q8. You executed the following command:
SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL'
You create a new package called PACK1. View Exhibit1 to examine the PL/SQL code for the
PACK1 package specification and body.
You issue the following query to see all unique identifiers with a name, such as %1:
SQL> SELECT NAME, SIGNATURE, TYPE
FROM USER_IDENTIFIERS
WHERE NAME LIKE '%1' AND USAGE='DECLARATION'
ORDER BY OBJECT_TYPE, USAGE_ID;
View Exhibit2 to examine the output of the query. Which two statements are true about the output of the query? (Choose two.)
A. The SIGNATURE column has a unique value for an identifier except for identifiers with the same name.
B. The TYPE column has the value of packages, function or procedures, object types, PL/SQL types, triggers, or exceptions.
C. The query shows the output for only those identifiers for PL/SQL objects, which are created by the user and are compiled after the ALTER SESSION command.
D. The ALTER SESSION command automatically collects identifier data and the query shows the output for all the identifiers for PL/SQL objects, which are created by the user.
Answer: B,C
Q9. The database instance was recently started up. Examine the following parameter settings for the database instance: NAME TYPE VALUE
result_cache_max_result integer 5
result_cache_max_size big integer 0
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
You reset the value for the result_cache_max_size parameter by issuing the following command:
SQL> ALTER SYSTEM SET result_cache_max_size = 1056k SCOPE = BOTH;
System altered.
Which statement is true in this scenario?
A. 1056 KB is allocated for the result cache and the result cache is enabled.
B. 1056 KB is allocated for the result cache, but the result cache is disabled.
C. The results for only the queries that have the RESULT_CACHE hint are cached.
D. The results for all the queries except those having the NO_RESULT_CACHE hint are cached.
Answer: B
Q10. Examine the structure of the TEST_DETAILS table: Name Null? Type
TEST_ID NUMBER
DESCRIPTION CLOB
DESCRIPTION data was entered earlier and saved for TEST_ID 12.
You execute this PL/SQL block to add data to the end of the existing data in the DESCRIPTION column for TEST_ID 12:
DECLARE
clob_loc CLOB;
buf CHAR(12);
BEGIN
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;
buf := '0123456789'
DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf);
COMMIT;
END;
/
It generates an error on execution.
What correction should you do to achieve the required result?
A. WRITEAPPEND must be replaced with APPEND.
B. The BUF variable data type must be changed to CLOB. C. FOR UPDATE must be added to the SELECT statement.
D. The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND.
Answer: C