1Z0-146 Dumps: Oracle Database 11g: Advanced PL/SQL

Dear All,

In this post, we are posting the set of questions, asked in the 1z0-146 exam.

Please download the whole dump from here: 1z0-146 Dump

Oracle Database 11g: Advanced PL/SQL	New & Upcoming Releases   Print this Exam
Exam Number: 1Z0-146 Associated Certifications: Oracle Advanced PL/SQL Developer Certified Professional Exam Product Version: SQL and PL/SQL, Exam Price: US$ 245 More on exam pricing Duration: 90 minutes Number of Questions: 68 Passing Score: 65% View passing score policy Validated Against: The exam has been validated against Oracle Database 11g Release 1 and Oracle Database 11g Release 2. format: Multiple Choice

EXAM Topics

Oracle11g: Advanced PL/SQL

·         Introduction to PL/SQL

PL/SQL Programming Concepts: Review

·         List restrictions on calling functions from SQL expressions

·         Hand exceptions

·         Manage dependencies

·         Use Oracle-supplied packages

Designing PL/SQL Code

·         Identify guidelines for cursor design

·         Use cursor variables

·         Create subtypes based on existing types

Working with Collections

·         Create collections (nested table, array and associative arrays, PL SQL tables)

·         Use collections methods

·         Manipulate collections

·         Distinguish between the different types of collections and their uses

Using Advanced Interface Methods

·         Execute external C programs from PL/SQL

·         Execute Java programs from PL/SQL

Implementing Fine-Grained Access Control for VPD

·         Explain the process of fine-grained access control

·         Implement and test fine-grained access control

Manipulating Large Objects

·         Create and manage LOB data types

·         Use the DBMS_LOB PL/SQL package

·         Use of temporary LOBs

Administering SecureFile LOBs

·         Describe SecureFile LOB features

·         Enable SecureFile LOB deduplication, compression, and encryption

·         Migrate BasicFile LOBs to the SecureFile LOB format

Performance and Tuning

·         Use native and interpreted compilation methods

·         Tune PL/SQL code

·         Enable intraunit inlining

Improving Performance with Caching

·         Improve memory usage by caching SQL result sets and using the DBMS_RESULT_CACHE package

·         Write queries that use the result cache hint

·         Set up PL/SQL functions to use PL/SQL result caching

Analyzing PL/SQL Code

·         Run reports on source code

·         Determine identifier types and usages

·         Use DBMS_METADATA to retrieve object definitions

Profiling and Tracing PL/SQL Code

·         Trace PL/SQL program execution

·         Profile PL/SQL applications

Safeguarding Your Code Against SQL Injection Attacks

·         Describe SQL injections

·         Reduce attack surfaces

·         Use DBMS_ASSERT

·         Design immune code

·         Test code for SQL injection flaws

1z0-146 Dump: Question and Answers

Which two types of metadata can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package?

Which two types of metadata can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package? (Choose two.)

A.
report of invalidated objects in a schema

B.
report of statistics of an object in the database

C.
DDL for all object grants on a table in the database

D.
data definition language (DDL) for all objects in a schema

Which two statements are true about SecureFile LOB options?

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

A.
The COMPRESSION HIGH option can be enabled only for CLOBs.

B.
The COMPRESSION HIGH option can be enabled for all internal LOBs.

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

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

Which statement describes the purpose of the plshprof command?

Which statement describes the purpose of the plshprof command?

A.
It produces HTML output from raw profiler output.

B.
It produces HTML output from profiler tables in the database.

C.
It populates profiler tables in the database from raw profiler output.

D.
It populates profiler tables in the database from raw profiler output.

Which two statements correctly describe the features of SecureFiles?

Which two statements correctly describe the features of SecureFiles? (Choose two.)

A.
Compression does not entail table or index compression and vice-versa.

B.
Encryption stores the encryption keys for the LOB columns inside the database.

C.
Encryption stores the encryption keys for the LOB columns outside the database.

D.
Compression stores identical data occurring two or more times in the same LOB column as a single copy for the table.

Which two statements are true about associative arrays and varrays?

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.

Which two statements are true about the above code?

Examine the code in the following PL/SQL block:     

DECLARE
TYPE NumList IS TABLE OF INTEGER;
List1 NumList := NumList(11,22,33,44);
BEGIN
List1.DELETE(2);
DBMS_OUTPUT.PUT_LINE
( ‘The last element# in List1 is ‘ || List1.LAST ||
‘ and total of elements is ‘||List1.COUNT);
List1.EXTEND(4,3);
END;
/
Which two statements are true about the above code? (Choose two.)

A.
LAST and COUNT give different values.

B.
LAST and COUNT give the same values.

C.
The four new elements that are added contain the value 33.

D.
The four new elements that are added contain the value 44.

Which two types of query results cannot be stored in the query result cache?

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

Which two statements are true about the working of fine-grained access?

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.

Which statement is true in this scenario?

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.

Which three statements are true about hierarchical profiling?

Which three statements are true about hierarchical profiling?
(Choose three.)

A.
It provides function-level summaries.

B.
It produces an aggregated report of the SQL and PL/SQL execution times.

C.
It is useful for understanding the structure and control flow of complex programs.

D.
It can be used to identify hotspots and tuning opportunities in PL/SQL applications.

E.
It generates trace information for PL/SQL programs in the PLSQL_TRACE_EVENTS table.

What would you do to execute the procedure successfully?

There is a Java class file in your system and you publish it using the following command:

CREATE OR REPLACE PROCEDURE ccformat
(x IN OUT VARCHAR2)
AS LANGUAGE JAVA
NAME ‘FormatCreditCardNo.formatCard(java.lang.String[])’;
However, you receive the following error when executing the CCFORMAT procedure:

ERROR at line 1:
ORA-29540: class FormatCreditCardNo does not exist
ORA-06512: at "SH.CCFORMAT", line 1
ORA-06512: at line 1

What would you do to execute the procedure successfully?

A.
Change the listener configuration.

B.
Create a directory object and link it to the Java class file.

C.
Rebuild the Java class file when the database instance is running.

D.
Use the loadjava utility to load the Java class file into the database.

Which two reports can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package?

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

Which two statements are true about the context of an application?

Which two statements are true about the context of an application? (Choose two.)

A.
It is attached to a session.

B.
It is owned by the user SYS.

C.
A user can change the context of his or her application.

D.
The PL/SQL package associated with the context must exist before the context is created.

E.
The predefined attributes in the USERENV application context can be changed as per the requirements.

Which statement would be true in this scenario?

Examine the following settings for a session:      

PLSQL_CODE_TYPE = NATIVE
PLSQL_OPTIMIZE_LEVEL = 3

Which statement would be true in this scenario?

A.
The compiler would automatically inline subprograms.

B.
The compiler would inline the code for external subroutines.

C.
The compiler would inline the code even if the INLINE pragma is set to NO.

D.
The compiler would not inline the code unless the INLINE pragma is set to YES.

Which two statements are true about the SQL Query Result Cache?

Which two statements are true about the SQL Query Result Cache? (Choose two.)

A.
It can store the query results for temporary tables.

B.
It can be set at the system, session or query level.

C.
It is used only across statements in the same session.

D.
Cached query results become invalid when the data accessed by the query is modified.

Which statement is true about user sessions that are connected currently?

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.

Identify two methods for immunizing PL/SQL code against SQL injection attacks. (Choose two.)

Identify two methods for immunizing PL/SQL code against SQL injection attacks. (Choose two.)

A.
Use bind arguments.

B.
Validate all input concatenated to dynamic SQL.

C.
Use dynamic SQLs constructed using concatenation of input values.

D.
Use subprograms as part of packages instead of stand-alone subprograms.

Which two statements are true about the tuning of PL/SQL code?

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.

whichscenarios is the cached result bypassed?

You created a PL/SQL function with the RESULT_CACHE and RELIES_ON clauses. In which
scenarios is the cached result bypassed? (Choose all that apply.)

A.
when the size for the memory allocated for the result cache is increased

B.
when the function is executed in a session frequently with the same parameter value

C.
when the database administrator has disabled the use of the result cache during application patching

D.
when a data manipulation language (DML) statement is executed in a session on a table or view that was specified in the RELIES_ON clause of a result-cached function

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

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.

Which statement is true about the output of the query?

To examine the dependencies between all PL/SQL objects and the tables and views they
reference, you executed the following query as the user OE:

SQL> SELECT owner || ‘.’ || NAME refs_table
, referenced_owner || ‘.’ || referenced_name AS table_referenced
FROM all_dependencies
WHERE owner = USER
AND TYPE IN (‘PACKAGE’, ‘PACKAGE BODY’,'PROCEDURE’, ‘FUNCTION’)
AND referenced_type IN (‘TABLE’, ‘VIEW’)
AND referenced_owner NOT IN (‘SYS’, ‘SYSTEM’)
ORDER BY owner, NAME, referenced_owner, referenced_name;

Which statement is true about the output of the query?

A.
It displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM.

B.
It displays no rows because this query needs to be executed as the user SYS for required results.

C.
It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.

D.
It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.

Regards,

Angad