Want to know Testking 1Z0-146 Exam practice test features? Want to lear more about Oracle Oracle 11g: Advanced PL/SQL certification experience? Study Vivid Oracle 1Z0-146 answers to Renewal 1Z0-146 questions at Testking. Gat a success with an absolute guarantee to pass Oracle 1Z0-146 (Oracle 11g: Advanced PL/SQL) test on your first attempt.
2018 NEW RECOMMEND
Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions
Q41. Which two types of query results cannot be stored in the query result cache? (Choose two.)
A. subquery results
B. results of a query having the SYSDATE function
C. results of a query having the GROUP BY clause
D. results of a query having the DATE data type in the WHERE clause
Q42. You executed the following command to alter the session parameter:
SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
Which two statements are true in this scenario? (Choose two.)
A. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope does not collect data for the compiled object.
B. All the identifiers declared in compiled program units before altering the parameter settings appear in the *_IDENTIFIER static data dictionary views.
C. All the identifiers declared in compiled program units before altering the parameter settings do not appear in the *_IDENTIFIER static data dictionary views.
D. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope collects data for the compiled object and stores it in the SYSTEM tablespace.
Q43. Identify the two types of PL/SQL programs for which you consider setting the compilation method to native mode. (Choose two.)
A. PL/SQL programs that are still in the debugging phase of development
B. PL/SQL programs that have computation-intensive procedural operations
C. A PL/SQL program, which is called with the same parameters by multiple sessions
D. PL/SQL programs that spend most of their execution time in executing SQL statements
Q44. Which two reports can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package? (Choose two.)
A. DDL report for all objects dependent on a table
B. DDL report for all the objects stored in a tablespace
C. DDL report for all the invalidated objects in a schema
D. data definition language (DDL) report for all the tables in a schema
Q45. 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)
SELECT hire_date INTO date_hired
WHERE EMPLOYEE_ID = emp_id;
You notice that results for the functions are not used effectively. What do you recommend for better utilization of the result cache? (Choose all that apply.)
A. Set the RESULT_CACHE_MODE parameter to FORCE.
B. Increase the value for the RESULT_CACHE_MAX_SIZE parameter.
C. Add a format mask parameter, such as RETURN TO_CHAR(date_hired, fmt) to GET_HIRE_DATE.
D. Change the return type of GET_HIRE_DATE to DATE and have each session invoke the TO_CHAR function.
Q46. Which two statements are true about associative arrays and varrays? (Choose two.)
A. Only varrays must start with the subscript 1.
B. Only varrays can be used as column types in database tables.
C. Both associative arrays and varrays must start with the subscript 1.
D. Both associative arrays and varrays can be used as column types in database tables.
Q47. 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.
Q48. Examine the structure of the EMPLOYEES table that exists in your schema. Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
JOB_ID NOT NULL VARCHAR2(10)
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.
Q49. 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:
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;
buf := '0123456789';
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.
Q50. Which two statements are true about the extproc process? (Choose two.)
A. It loads the dynamic library.
B. It is started by the server process.
C. It converts the C output back to PL/SQL.
D. A single extproc process serves all user sessions.