Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Which two statements are true about the usage of the DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure? (Choose two.)
A. You can describe remote objects.
B. You can describe anonymous PL/SQL blocks.
C. You can describe a stored procedure, stored function, packaged procedure, or packaged function.
D. You can obtain information about the position, name, and data type of the arguments of a procedure.
Answer: C,D
Q2. You set RESULT_CACHE_MAX_SIZE to a nonzero value to enable result caching. You executed the following command to check the status for the result cache:,
SQL> select dbms_result_cache.status() from dual;
You receive the following output:
DBMS_RESULT_CACHE.STATUS()
DISABLED
Identify the reason for the output.
A. The RESULT_CACHE_MODE parameter is set to FORCE.
B. The RESULT_CACHE_MODE parameter is set to MANUAL.
C. The database instance is not able to allocate memory for the result cache.
D. The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.
Answer: D
Q3. Which two statements are true about associative arrays and varrays? (Choose two.)
A. Only varrays must use sequential numbers as subscripts.
B. Only varrays can be used as column types in database tables.
C. Both associative arrays and varrays must use sequential numbers as subscripts.
D. Both associative arrays and varrays can be used as column types in database tables.
Answer: A,B
Q4. View Exhibit1 and examine the structure of the EMPLOYEES table.
itexamworld.com
View Exhibit2 and examine the code in the PL/SQL block.
The PL/SQL block fails to execute.
What could be the reason?
A. Nested tables cannot be returned by a function.
B. The NEWNAMES nested table has not been initialized.
C. The assignment operator cannot be used to transfer all the element values from GROUP1 to GROUP2.
D. The third element of OLDNAMES cannot be assigned to the third element of GROUP1 because they are of inconsistent data types.
E. LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements.
Answer: E
Q5. Which two statements are true about REF CURSOR types? (Choose two.)
A. REF CURSOR types cannot be defined inside a package.
B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a userdefined record.
D. Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function.
Answer: B,C
Q6. 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.
Answer: A,C,D
Q7. View the Exhibit and examine the PL/SQL code.
Which statement is true about the execution of the PL/SQL code?
A. It executes successfully and displays 101 and 200000 values.
B. The ASSIGN_VAL function generates an error during compilation because nested tables cannot be returned by functions.
C. The SELECT statement generates an error because the nested table has not been initialized in the ASSIGN_VAL function.
D. The ASSIGN_VAL function generates an error during compilation because the EXTEND method cannot be used with nested tables.
Answer: C
Q8. Examine the following line of code that is part of a PL/S QL application: stmt:='SELECT session_id FROM sessions WHERE ' || p_where_stmt; Identify a solution for preventing SQL injection in the above code.
A. Replace P_WHERE_STMT with a bind variable.
B. Do not use APIs that allow arbitrary query parameters to be exposed.
C. Use the RESTRICT_REFERENCES clause in the PL/SQL subprogram that contains the code.
D. Use DBMS_SQL to detect that the expression provided for P_WHERE_STMT is free from SQL injection.
Answer: B
Q9. Which three actions can be performed by using the DBMS_ASSERT package to prevent SQL injection? (Choose three.)
A. Detect a wrong user.
B. Check input string length.
C. Verify qualified SQL names. D. Validate TNS connect strings.
E. Verify an existing schema name.
F. Enclose string literals within double quotation marks.
Answer: C,E,F
Q10. View the Exhibit.
How do you reduce the chances of SQL injection for the procedure?
A. Execute the SQL statement in V_STMT as dynamic SQL.
B. Remove the default value for the arguments in the procedure.
C. Convert the condition in the WHERE clause to be accepted from the user and concatenated.
D. Convert the SELECT statement to static SQL, placing the value of P_EMAIL into a local variable.
Answer: D
Q11. Examine the structure of the EMPLOYEES table in the SCOTT schema. Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NOT NULL NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
DEPARTMENT_ID NUMBER(4)
View the Exhibit and examine the code for the EMP_TOTSAL procedure created by user SCOTT.
Which statement is true regarding the EMP_TOTSAL procedure?
A. It is created successfully, but displays the correct output message only for existent employee IDs.
B. It is created successfully and displays the correct output message for both existent and nonexistent employee IDs.
C. It generates an error because the %NOTFOUND attribute cannot be used in combination with a SELECT INTO statement.
D. It generates an error because a user-defined exception has to be included whenever the %NOTFOUND attribute is used in combination with a SELECT INTO statement.
Answer: A
Q12. Which two statements are true about the working of fine-grained access? (Choose two.)
A. Security policies can be associated only with tables, but not with views.
B. Different policies can be used for SELECT, INSERT, UPDATE, and DELETE statements.
C. User statements are dynamically modified by the Oracle server through a security policy function.
D. Fine-grained access control policies always remain in effect until they are dropped from a table or view.
Answer: B,C
Q13. Examine the settings for a user session given below:
RESULT_CACHE_MODE= FORCE
What would be the implications of this setting on query execution? (Choose all that apply.)
A. All query results are stored in the result cache if possible.
B. Query results that are bigger than the available space in the result cache are not cached.
C. Query results are stored only when you explicitly use the /*+ result_cache */ hint in your query.
D. Query results are stored even when you explicitly use the /*+ no_result_cache */ hint in your query.
Answer: A,B
Q14. Examine the structure of the PRODUCTS table. Name Null? Type
PRODUCT_ID NOT NULL NUMBER(6)
PRODUCT_NAME VARCHAR2(50)
CATEGORY_ID NUMBER(2)
SUPPLIER_ID NUMBER(6)
LIST_PRICE NUMBER(8,2)
View the Exhibit and examine the PL/SQL block.
On execution, the PL/SQL block generates the following error:
ORA-01001: invalid cursor
What could be the reason?
A. Both the cursor variable arguments should have been passed in IN mode.
B. The contents of one cursor variable cannot be assigned to another cursor variable using the := operator.
C. The CLOSE statement closes both the cursor variables, therefore the last FETCH statement cannot execute.
D. The name of the cursor variables defined and the name of the cursor variables passed as arguments must be the same.
Answer: C
Q15. The database instance was started up with the following initialization parameter values:
MEMORY_TARGET = 500M
RESULT_CACHE_MODE = FORCE
RESULT_CACHE_MAX_SIZE = 0
After the database startup, to enable the result cache, you issued the following command:
SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY;
Which is the effect of this command?
A. The query result cache is enabled and 2 MB of the memory target is allocated to the result cache.
B. The query result cache is enabled and 0.25% of the memory target is allocated to the result cache.
C. The command produces an error because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
D. The query result cache is not enabled because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
Answer: D
Q16. 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.
Answer: A