NEW QUESTION NO: 5
Consider a function totalEmp () which takes a number as an input parameter and returns the total number of employees who have a salary higher than that parameter.
Examine this PL/SQL package AS

Which two definitions of totalEmp () result in an implicit conversion by Oracle Database on executing this PL/SQL block?
A. CREATE FUNCTION totalEmp (sal IN POSITIVEN) RETURN NUMBER IS
total NUMBER :=0;
BEGIN
…
RETUNRN total;
END;
/
B. CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IS
total NUMBER :=0;
BEGIN
…
RETUNRN total;
END;
/ 
C. CREATE FUNCTION totalEmp (sal IN BINARY_FLOAT) RETURN NUMBER IS
total NUMBER :=0;
BEGIN
…
RETUNRN total;
END;
/
D. CREATE FUNCTION totalEmp (sal IN PLS_INTEGER) RETURN NUMBER IS
total NUMBER :=0;
BEGIN
…
RETUNRN total;
END;
/
E. CREATE FUNCTION totalEmp (sal IN NUMBER) RETURN NUMBER IS
total NUMBER :=0;
BEGIN
…
RETUNRN total;
END;
/
Answer: B,D

NEW QUESTION NO: 6
Which must be true in order to add RESULT_CACHE to a function header and have it compile successfully?
A. The IN parameters must not include BLOB, CLOB, collection or record data types.
B. The function must be created with invoker’s rights or in an anonymous block.
C. The function must be declared as a pipelined table function.
D. The function must have an OUT or an IN OUT parameter.
Answer: C
Explanation/Reference:
Reference: https://docs.oracle.com/cd/E18283_01/appdev.112/e17126/subprograms.htm#insertedID11

NEW QUESTION NO: 7
Examine the following SQL statement:
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;
What is the result of executing this statements?
A. The PL/SQL optimize level for subsequently compiled PL/SQL units will be set to 3 and inlining will be disabled.
B. The PL/SQL optimize level for some existing PL/SQL units will be changed as an immediate result.
C. This statement will fail because PLSQL_OPTIMIZE_LEVEL can only be set at the system level,
D. The PL/SQL optimize level for subsequently complied PL/SQL units will be set to 3 and inlining will be enabled.
Answer: A

NEW QUESTION NO: 8
Which PRAGMA statement may enable associated PL/SQL functions to run more efficiently when called from SQL?
A. PRAGMA SERIALLY_REUSABLE;
B. PRAGMA UDF;
C. PRAGMA INLINE (‘<function_name>’, ‘YES’);
D. PRAGMA AUTONOMOUS_TRANSACTION;
Answer: B
Explanation/Reference:
Reference: https://mwidlake.wordpress.com/2015/11/04/pragma-udf-speeding-up-your-plsql-functions- called-from-sql/

NEW QUESTION NO: 9
Which tablespace is used to store the data collected by PL/Scope?
A. UNDOTBS1
B. SYSAUX
C. SYSTEM
D. TEMP
E. USERS
Answer: B
Explanation/Reference:
Reference: https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm#BABDGJAF

NEW QUESTION NO: 10
Examine the structure of the DEPT table which exists in both BRANCH1 and BRANCH2 schemas:

Examine this code:

Which three modifications must be done to endure the anonymous block displays the output form the BRANCH2.DEF DEPT table?
A. Change the IF condition in the anonymous block to CARDINALITY (1_dept) IS NULL.
B. Add AUTHID CURRENT_USER to the EMP_COUNT function.
C. Change the IF condition in the anonymous block to 1_dept IS EMPTY.
D. IN BRANCH2 execute GRANT INHERIT PRIVILEGES ON USER branch2 TO branch1;
E. Add BEQUEATH CURRENT_USER to the EMP_COUNTS_VW view.
F. Add BEQUEATH DEFINER to the EMP_COUNT_VW view.
Answer: A,D,E

NEW QUESTION NO: 11
Examine this function body:

Which two headers will allow this function to compile successfully and take advantage of both invoker’s rights and function result caching?
A. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2
RESULT_CACHE RELIES_ON (departments)
AUTHID CURRENT_USER
IS
date_hired DATE;
B. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2
RESULT_CACHE RELIES_ON (employees)
AUTHID CURRENT_USER
IS
date_hired DATE;
C. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2
AUTHID DEFINER
IS
date_hired DATE;
D. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2
RESULT_CACHE
AUTHID DEFINER
IS
date_hired DATE;
E. CREATE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR2
RESULT_CACHE
AUTHID CURRENT_USER
IS
date_hired DATE;
Answer: B,C

NEW QUESTION NO: 12
The STUDENTS table exists in your schema.
Examine the DECLARE section of a PL/SQL block:

Which two blocks are valid?
A. BEGIN
OPEN stcur;
cursor3 :=stcur;
END;
B. BEGIN
OPEN cursor1 FOR SELECT * FROM students;
cursor2 :=cursor1;
END;
C. BEGIN
OPEN cursor3 FOR SELECT * FROM students;
cursor1 :=cursor3;
END;
D. BEGIN
OPEN cursor1 FOR SELECT * FROM students;
stcur :=cursor1;
END;
E. BEGIN
OPEN stcur;
cursor1 :=stcur;
END;
Answer: A,B

NEW QUESTION NO: 13
You are logged on to the SCOTT schema and the schema has EMP and DEPT tables already created:
Examine this PL/SQL procedure:

Which PL/SQL block will raise an exception?
A. EXEC get_tab_row_count (‘emp’);
B. EXEC get_tab_row_count (‘DEPT, EMP’)
C. EXEC get_tab_row_count (‘ “EMP” ’);
D. EXEC get_tab_row_count (‘SCOTT.EMP’);
E. EXEC get_tab_row_count (‘DEPT’);
Answer: C

NEW QUESTION NO: 14
Examine this block of code used to calculate the price increase for all the productivity by 1% and then by
2%.

What will be the outcome on execution?
A. It will give an error because the calculated column in the cursor is not using a column alias in this block.
B. It will give an error because PDT_REC is not declared.
C. It will go into an endless loop because the loop exist condition is missing.
D. It will display the price increase by 1% only for all the products.
E. It will display the price increase by 1% only for the first product.
Answer: C

NEW QUESTION NO: 15

Examine this code:

You want to display the contents of CREATE_LIST.
Which two lines need to be corrected in the PL/SQL block?
A. Line 5
B. Line 2
C. Line 3
D. Line 7
E. Line 6
Answer: C,E