Exam Code: 1Z0-146 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle 11g: Advanced PL/SQL
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-146 Exam.
Q1. View the Exhibit and examine the procedure to create a trigger name based on the table name supplied to the procedure.
Which three statements are appropriate for protecting the code in the procedure from SQL injection? (Choose three.)
A. Explicitly validate the identifier length limit.
B. Add AUTHID DEFINER to the definition of the procedure.
C. Use PRAGMA RESTRICT_REFERENCES in the procedure.
D. Filter out control characters in user-supplied identifier names.
E. Use the object ID of the table from the data dictionary to build the trigger name.
Answer: A,D,E
Q2. Which two statements are true about associative arrays and nested tables? (Choose two.)
A. Only associative arrays can hold an arbitrary number of elements.
B. Only associative arrays can use numbers and strings for subscripts.
C. Both associative arrays and nested tables can hold an arbitrary number of elements.
D. Both associative arrays and nested tables can use numbers and strings for subscripts.
Answer: B,C
Q3. In which two situations is the body of a result-cached function executed? (Choose two.)
A. if the memory allocated for the result cache is increased
B. if a session on this database instance invokes the function with the same parameter values
C. if the first time a session on this database instance invokes the function with a parameter value
D. 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
Answer: C,D
Q4. 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
Q5. Which two are major approaches that can be used to reduce the SQL injection by limiting user input? (Choose two.)
A. Restrict users accessing specified web page.
B. Use NUMBER data type if only positive integers are needed.
C. Use dynamic SQL and construct it through concatenation of input values.
D. In PL/SQL API, expose only those routines that are intended for customer use.
Answer: A,D
Q6. You enabled PL/SQL tracing in a user session using the following command:
SQL> EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_CALLS);
View Exhibit1 to examine the output. After some time, the query produces a different result as shown in Exhibit2.
What is the cause for the change?
A. The FOO procedure has been executed more than once.
B. The PLSQL_DEBUG parameter is set to FALSE for the user session.
C. The FOO procedure has been compiled with the DEBUG option, and executed.
D. Schema level statistics have been gathered by the database administrator (DBA).
Answer: C
Q7. Which two statements are true about associative arrays and nested tables? (Choose two.)
A. Only associative arrays can hold an arbitrary number of elements.
B. Only nested tables can be used as column types in database tables.
C. Both associative arrays and nested tables can hold an arbitrary number of elements.
D. Both associative arrays and nested tables can be used as column types in database tables.
Answer: B,C
Q8. Examine the structure of the TEXT_TAB table. Name Null? Type
TEXT_ID NUMBER
DOC1 CLOB
DOC2 CLOB
You issue the following INSERT commands:
INSERT INTO text_tab VALUES (1, 'This is line 1',null);
INSERT INTO text_tab VALUES (2, 'This is line 1','This is line 2');
Then you execute the following block of the PL/SQL code:
DECLARE
vc1 VARCHAR2(1000):= 'This is the preface'
lb1 CLOB;
lb2 CLOB;
BEGIN
SELECT doc1 INTO lb1 FROM text_tab WHERE text_id=1;
SELECT doc1 || doc2 INTO lb1 FROM text_tab WHERE text_id=2;
lb2 := vc1|| lb1;
UPDATE text_tab SET doc2 = lb2 WHERE text_id = 1;
END;
/
What is the outcome?
A. It executes successfully.
B. It gives an error because VARCHAR2 should be explicitly converted to CLOB.
C. It gives an error because CLOB variables should be initialized to EMPTY_CLOB().
D. It gives an error because the concatenation operator cannot be used with the CLOB data type.
Answer: A