Limited Time Discount Offer 30% Off - Ends in 02:00:00


Oracle 1z0-146 Exam - Oracle Database 11g: Advanced PL/SQL

Download Free Oracle 1z0-146 Exam Questions

File name






1.3 MB


Questions & Answers for Oracle 1z0-146

Showing 1-15 of 136 Questions

Question #1 - Topic 0

Which two statements are true about the migration of BasicFile to the SecureFile format by
using the DBMS_REDEFINITION package? (Choose two.)

A. It can be performed only on tables with a single LOB column.

B. It automatically creates an interim table during the migration process.

C. It allows the table that is migrated to be accessed throughout the migration process.

D. It requires free space that is at least equal to the space used by the table that is migrated.

E. It requires all constraints defined on the original table to be re-created manually after the migration.

Question #2 - Topic 0

Which two statements are true about cursor variables? (Choose two.)

A. A cursor variable points to the current row in the result set of a multirow query stored in a work area.

B. A cursor variable is an explicitly named work area in which the results of different multirow queries can be stored.

C. A cursor variable can be used only if a query is performed and its results are processed in the same subprogram.

D. A cursor variable can be used to perform a query in one subprogram, and process the results in a different subprogram.

Question #3 - Topic 0

Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL
CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,
deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND
salary Number(8),
deptname VARCHAR2(30) DEFAULT 'Sales')
SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;
v_emprec v_emprec_subtype;
v_emprec.empid := NULL; v_emprec.salary := 10000.002;
v_emprec.deptid := 50;
DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname);
Which statements are true about the above PL/SQL block? (Choose two.)

A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.

B. Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is inherited.

C. Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of the decimal.

D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.

Question #4 - Topic 0

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?

A. The corresponding shared library is also removed from the system.

B. PL/SQL subprograms can be used to invoke the external C procedure.

C. The existing extproc process is terminated and a new extproc is started.

D. The PL/SQL subprogram that depends on the external C program becomes invalid.

Question #5 - Topic 0

Examine the code in the following PL/SQL block:
List1 NumList := NumList(11,22,33,44);
( 'The last element# in List1 is ' || List1.LAST ||
' and total of elements is '||List1.COUNT);
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.

Question #6 - Topic 0

Examine the structure of the PRINT_MEDIA table:
Name Null? Type
--------------- -------- ---------
Examine the following PL/SQL block:
lobloc CLOB;
buffer VARCHAR2(100);
amount NUMBER;
offset NUMBER :=1;
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;
What must be the value in the ADVT_SOURCE column for the above code to execute

A. null

B. an empty locator

C. a non-NULL value

D. either null or any non-NULL values

Question #7 - Topic 0

A procedure is created in the SYS schema to allow users to change the password as
PROCEDURE change_password(p_username VARCHAR2 DEFAULT NULL,
p_new_password VARCHAR2 DEFAULT NULL)
v_sql_stmt VARCHAR2(500);
v_sql_stmt := 'ALTER USER '||p_username ||' IDENTIFIED BY '
|| p_new_password;
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?

A. by using the procedure as part of a PL/SQL package

B. by using a bind argument with dynamic SQL in the procedure

C. by using AUTHID DEFINER in the procedure to implement the definer's right

D. by using AUTHID CURRENT_USER in the procedure to implement the invoker's right

Question #8 - Topic 0

Identify the method that is used by fine-grained access (FGA).

A. using policy functions to generate predicates dynamically

B. creating triggers on corresponding tables to generate dynamic predicates

C. modifying the existing application code to include a predicate for all SQL statements

D. creating views with necessary predicates, and then creating synonyms with the same name as the tables

Question #9 - Topic 0

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.

Question #10 - Topic 0

Which two conditions must be true for a PL/SQL function to be result cached? (Choose

A. It must be part of a package.

B. It must be a pipelined table function.

C. It must not be defined in an anonymous block.

D. It must have at least one OUT or IN OUT parameter.

Question #11 - Topic 0

View Exhibit1 and examine the structure of the EMPLOYEES and DEPARTMENTS tables
existing in your schema.
View Exhibit2 and examine the PL/SQL block that you execute to display the department-
wise incremented salary for all the departments in your company.
The code generates an error on execution.
What correction should be done to ensure the code executes successfully?

A. The cursor variable parameter should be passed in IN OUT mode.

B. The cursor variable should be defined as a strong REF CURSOR type.

C. The cursor variable name passed as actual and formal parameters should be identical.

D. The %NOTFOUND cursor attribute cannot be used with the cursor variables and should be replaced with a user defined exception.

Question #12 - Topic 0

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

Question #13 - Topic 0

You execute the following command in the user session:
Which statement is true about the effect of the command?

A. All PL/SQL blocks that are executed subsequently in the session are traced.

B. It enables all PL/SQL blocks that are compiled subsequently in the session for tracing.

C. Only anonymous PL/SQL blocks that are executed subsequently in the session are traced.

D. It enables only named PL/SQL blocks that are executed subsequently in the session for tracing.

Question #14 - Topic 0

Which two queries' results cannot be cached? (Choose two.)

A. queries having the GROUP BY clause

B. queries having the ORDER BY clause

C. the query on dictionary and temporary tables

D. queries having SYSDATE and SYS_TIMESTAMP SQL functions

Question #15 - Topic 0

Identify the component of the PL/SQL hierarchical profiler that uploads the result of profiling
into database tables.

A. the trace file component

B. the analyzer component

C. the shared library component

D. the data collection component


Exam-Labs Special Discount
You save

Enter Your Email Address to Receive Your 30% Discount Code


Exam-Labs Special Discount
You save

Use Discount Code:

A confirmation link was sent to your e-mail.

Please check your mailbox for a message from [email protected] and follow the directions.

Download Free Demo of VCE Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.

Simply submit your e-mail address below to get started with our interactive software demo of your free trial.

  • Realistic exam simulation and exam editor with preview functions
  • Whole exam in a single file with several different question types
  • Customizable exam-taking mode & detailed score reports