1Z0-146 - Oracle 11g: Advanced PL/SQL
Go back to Oracle
How many questions are there on the 1Z0-146 exam?
There are 68 questions on the 1Z0-146 exam to be completed in 90 minutes.
How long is the 1Z0-146 exam?
The 1Z0-146 exam is 90 minutes to complete 68 questions.
How much does the 1Z0-146 exam cost?
The 1Z0-146 exam is $245. The exam provider is Pearson Vue and you can register for the exam on their website.
What is the best way to study for the 1Z0-146 exam?
The best way to study for the 1Z0-146 exam is to practice at Exam.com.
Which two statements are true about the context of an application? (Choose two.)
It is attached to a session.
It is owned by the user SYS.
You created a PL/SQL function with the RESULT_CACHE and RELIES_ON clauses. In which scenarios is the cached reuslt bypassed? (Choose all that apply)
When the database administrator has disabled the use of the result cache during application patching
Whe a data manipulation language statement in a session on a table or view that was specified in the RELIES_ON clause of a result-cached function
Match the following external C procedure components with their descriptions: 1. External procedure a. a process that starts the extproc process 2. Shared library b. a session-specific process that executes the external procedure 3. Alias library c. schema object that represents the operating system (OS) shared library 4. The extproc process d. operating system file that stores the external procedure 5. Listener process e. a unit of code written in C
1-e; 2-d; 3-c; 4-b; 5-a
Which two types of query results cannot be stored in the query result cache? (Choose two.)
results of a query having the SYSDATE function
Which statement describes the purpose of the plshprof command?
It produces HTML output from raw profiler output
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?
FOR UPDATE must be added to the SELECT statement.
Examine the following PL/SQL statements: Stmt='SELECT session_id FROM sessions WHERE '|| p_where_stmt; Identify a solution for preventing SQL injection in the above code.
Do not use APIs that allow arbitrary query parameters to be exposed
Identify the component of the PL/SQL hierarchical profiler that uploads the result of profiling into database tables.
the analyzer component
Which two statements are true about the migration of BasicFile to the SecureFile format by using the DBMS_REDEFINITION package? (Choose two.)
It allows the table that is migrated to be accessed throughout the migration process.
It requires free space that is at least equal to the space used by the table that is migrated.
Which two statements are true about the tuning of PL/SQL code? (Choose two.)
Redundant SQL statements in PL/SQL code should be avoided.
Usage of the NOT NULL constraint in PL/SQL code can degrade performance.
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?
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..
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.
The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.
Match the following external C procedure components with their descriptions: 1 External procedure a. a process that starts the extproc process 2 Shared library b. a session-specific process that executes the external procedure 3 Alias library c. schema object that represents the operating system (OS) shared library 4 The extproc process d. operating system file that stores the external procedure 5 Listener process e. a unit of code written in C
1-e; 2-d; 3-c; 4-b; 5-a
Identify two methods for immunizing PL/SQL code against SQL injection attacks. (Choose two.)
Use bind arguments.
Validate all input concatenated to dynamic SQL.
Which two statements correctly describe the features of SecureFiles? (Choose two.)
Compression is performed only on the server side and enables random reads and writes to LOB data.
Deduplication stores identical data occurring two or more times in the same LOB column as a single copy for the table.
Examine the code snippet from the declarative section of a PL/SQL block: DECLARE TYPE va1 IS VARRAY(10) OF VARCHAR2(20); SUBTYPE scale IS NUMBER(1,0); TYPE tb1 IS TABLE OF departments.department_name%TYPE INDEX BY departments.department_id%TYPE; TYPE tb2 IS TABLE OF va1 INDEX BY PLS_INTEGER; TYPE tb3 IS TABLE OF scale INDEX BY VARCHAR2(10); TYPE tb4 IS TABLE OF DATE INDEX BY DATE; TYPE tb5 IS TABLE OF NUMBER INDEX BY CHAR(2); .... Which of the above are valid definitions for associative arrays? (Choose all that apply.)
Examine the following parameter values for a session: PLSQL_CODE_TYPE = NATIVE PLSQL_OPTIMIZE_LEVEL = 2 Which two statements are true in this scenario? (Choose two.)
The compiler optimizes PL/SQL programs by elimination of unnecessary computations and exceptions.
PL/SQL statements in a PL/SQL program unit are compiled into machine-readable code, and stored in the SYSTEM tablespace.
Which two statements are true about REF CURSOR types? (Choose two.)
SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a userdefined record.
Which two statements are true about associative arrays and varrays? (Choose two.)
Only varrays must use sequential numbers as subscripts
Only varrays can be used as column types in database tables
Identify the two type of PL/SQL programs for which you consider setting the compilation method to native mode. (choose two.)
PL/SQL programs that have computation-intesive procedural operations
A PL/SQL program, which is called with the same parameters by multiple sessions
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.)
If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope does not collect data for the compiled object.
All the identifiers declared in compiled program units before altering the parameter settings do not appear in the *_IDENTIFIER static data dictionary views.
Which two conditions must be true for a PL/SQL function to be result cached? (Choose two.)
It must not be defined in an anonymous block.
It must have at least one OUT or IN OUT parameter.
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?
If sessions have different NLS_DATE_FORMAT settings, cached results have different formats.
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?
The subsequent queries on the ORDERS table within the current session are not controlled by the security policy.
Which two statements are true about associative arrays and nested tables? (Choose two.)
Only nested tables can be used as column types in database tables.
Both associative arrays and nested tables can hold an arbitrary number of elements.
Which two guidelines should be considered when designing and using cursors in a PL/SQL block? (Choose two.)
When fetching from a cursor, fetch into a record.
Whenever possible, use the cursor FOR loop instead of explicitly declaring the cursor and using the OPEN, FETCH, and CLOSE statements to manipulate the cursor.
You created a PL/SQL subprogram that successfully invokes an external C procedure. After a while, the database administrator (DBA) drops the alias library schema object. The shared library exists in the system. Which statement is true in this scenario?
The PL/SQL subprogram that depends on the external C program becomes invalid
When do you use static SQL as a technique for avoiding SQL injection?
when all Oracle identifiers are known at the time of code compilation
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?
The query results are stored and 0.25% of the memory target is allocated to the result cache
Which two statements are true about the query results stored in the qeury result cache? (Choose two.)
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
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
Which statements are true about the SecureFile storage paradigm? (Choose two.)
Automatic segment space management must be enabled for a tablespace to store SecureFile LOBs
SecureFile options enabled for a LOB column can be overriden on a per-LOB basis within the column
Which two statements are true about the working of fine-grained access? (Choose two.)
Different policies can be used for SELECT, INSERT, UPDATE and DELETE statements
User statements are dynamically modified by the Oracle Server through a security policy function
Identify the method that is used by fine-grained access (FGA).
using policy functions to generate predicates dynamically
Which two statements are true about the extproc process? (Choose two.)
It loads the dynamic library.
It converts the C output back to PL/SQL.
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.)
LAST and COUNT give different values.
The four new elements that are added contain the value 33.
Which two statements are true about the inlining of PL/SQL subprograms? (Choose two.)
Only local subroutines can be inlined.
PLSQL_OPTIMIZE_LEVEL must be set to a value greater than or equal to 2.
Examine the structure of the PRINT_MEDIA table: Name Null? Type --------------- -------- --------- ADVT_ID NUMBER ADVT_SOURCE CLOB Examine the following PL/SQL block: DECLARE lobloc CLOB; buffer VARCHAR2(100); amount NUMBER; offset NUMBER :=1; BEGIN buffer :='This is the second line of a new document'; amount := LENGTH(buffer); SELECT advt_source INTO lobloc FROM print_media WHERE advt_id=2 FOR UPDATE; DBMS_LOB.WRITE(lobloc,amount,offset,buffer); COMMIT; END; / What must be the value in the ADVT_SOURCE column for the above code to execute successfully?
a non-NULL value
Which statements are true about temporary LOBs? (Choose all that apply.)
They can be accessed only by the user who creates them.
They exist for the duration of the session in which they are created.
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; 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.)
Add a format mask parameter, such as RETURN TO_CHAR(date_hired, fmt) to GET_HIRE_DATE.
Change the return type of GET_HIRE_DATE to DATE and have each session invoke the TO_CHAR function.
Which guidelines should be considered when designing and using cursors in a PL/SQL block? ? (Choose all that apply.)
When fetching from a cursor, fetch into a record.
Use parameters with cursors so that the result set for the cursor is not tied to a specific variable in a program.
When using data manipulation language statements, (DML) reference a SQL cursor attribute immediately after the DML statement executes in the same block.
Which two statements are true about the initialization of internal LOBs? (Choose two.)
The EMPTY_CLOB() and EMPTY_BLOB() functions can be used to initialize both null and non-NULL internal LOBs.
The CLOB and BLOB columns can be initialized with a character or raw string, respectively, provided they are less than 4000 bytes in size.
Identify the two types of PL/SQL programs for which you consider setting the compilation method to native mode. (Choose two.)
PL/SQL programs that have computation-intensive procedural operations
A PL/SQL program, which is called with the same parameters by multiple sessions
Identify two strategies against SQL injection. (Choose two.)
Using parameterized queries with bind arguments
Validate user inputs to functions that use dynamic SQLs built with concatenated values
A procedure is created in the SYS schema to allow users to change the password as follows: CREATE OR REPLACE PROCEDURE change_password(p_username VARCHAR2 DEFAULT NULL, p_new_password VARCHAR2 DEFAULT NULL) IS v_sql_stmt VARCHAR2(500); BEGIN v_sql_stmt := 'ALTER USER '||p_username ||' IDENTIFIED BY ' || p_new_password; EXECUTE IMMEDIATE v_sql_stmt; END change_password; The SYS user has granted EXECUTE privilege on the procedure to the OE user. But OE is able to change the password for SYS by using this procedure. How would you protect this?
by using AUTHID CURRENT_USER in the procedure to implement the invoker's right
Which two statements are true about the SQL Query Result Cache? (Choose two.)
It can be set at the system, session, or query level.
Cached query results become invalid when the data accessed by the query is modified.
Which two statements are true about the usage of the DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure? (Choose two.)
You can describe a stored procedure, stored function, packaged procedure, or packaged function.
You can obtain information about the position, name, and data type of the arguments of a procedure.
In which two situations is the body of a result-cached function executed? (Choose two.)
if the first time a session on this database instance invokes the function with a parameter value
if a session executes a data manipulation language (DML) statement on a table or view that was specified in the RELIES_ON clause of a result-cached function
Identify two factors that you consider for compiling a PL/SQL program unit for interpreted mode. (Choose two.)
a PL/SQL program which needs to be recompiled frequently
a PL/SQL program that spends most of the time executing SQL
Which statements are true about internal LOBs? (Choose all that apply)
The can be used as attributes of a user-defined data type
They can be stored in a tablespace that is different from the tablespace that stores the table containing the LOB column
Identify the component of the PL//SQL hierarchical profiler that uploads the result of profiling into database tables:
The analyzer component