Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 81:

  Which two statements are true about the DBMS_ LOB package?

  A. DBMS_LOB.COMPARE can compare parts of two LOBs.

  B. DBMS_LOB.COMPARE returns the size difference of the compared LOBs.

  C. DBMS_LOB.COMPARE is overloaded and can compare CLOBs with BLOBs.

  D. If the destination LOB is a temporary LOB, the row must be locked before calling DBMS_LOB.CONVERTTOBLOB.

  E. Before calling DBMS_LOB.CONVERTTOBLOB, both the source and destination LOB instances must exist.

  Correct Answer: AE

  Reference: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#BABDDFDH

• Question 82:

  Which two statements are correct for collecting data about identifiers in PL/SQL source code?

  A. CREATE < function/Procedure> PLSCOPE_SETTINGS = `IDENTIFIERS: ALL' AS ...

  B. ALTER SYSTEM SET PLSCOPE_SETTINGS = `IDENTIFIERS: NONE'

  C. ALTER SESSION SET PLSCOPE_SETTINGS = `IDENTIFIERS: NONE'

  D. ALTER SESSION SET PLSCOPE_SETTINGS = `IDENTIFIERS: ALL'

  E. ALTER COMPILE PLSCOPE_SETTINGS = `IDENTIFIERS: ALL'

  Correct Answer: DE

• Question 83:

  You execute this PL/SQL block:

  

  What will be the outcome?

  A. It will fail compile because of errors at lines 11 and 12.

  B. It will fail compile because of errors at lines 6 and 12.

  C. It will fail compile because of error at line 7.

  D. It will fail compile and execute successfully.

  E. It will fail compile because of errors at lines 5 and 6.

  Correct Answer: B

• Question 84:

  Examine this function call:

  cur_num := DBMS_SQL.TO_CURSOR_NUMBER (cur_val);

  Which two statements are true?

  A. CUR_VAL must be opened after this line is executed in the PL/SQL block.

  B. CUR_VAL must be a weakly typed cursor variable.

  C. CUR_VAL can be either a strongly or weakly typed cursor variable.

  D. CUR_VAL must be opened before this line is executed in the PL/SQL block.

  E. The PL/SQL block can continue to use the cursor variable after this line is executed.

  Correct Answer: CD

  Reference: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS011

• Question 85:

  Which three statements are correct with reference to intra unit inlining?

  A. Inlining will always decrease the size of a unit.

  B. Setting PLSQL_OPTIMIZE_LEVEL to 2 means automatic inlining is attempted.

  C. You cannot inline an external subroutine.

  D. Programs that make use of smaller helper subroutines are good candidates for inlining.

  E. Pragmas apply only to calls in the next statement following the pragma.

  F. You cannot inline local subroutines.

  Correct Answer: CDE

  Reference: http://dbmanagement.info/Books/MIX/Les07_PLSQL.pdf

• Question 86:

  Examine the code:

  

  Which two subprograms will be created successfully?

  A. CREATE FUNCTION p4 (y pkg.tab_typ) RETURN pkg.tab_typ IS BEGIN EXECUTE IMMEDIATE ‘SELECT pdt_id, pdt_name FROM TABLE (:b)’ BULT COLLECT INTO pkg.x USING y; RETURN pkg.x;

  END p4;

  B. CREATE PROCEDURE p1 (y IN OUT pkg.tab_typ) IS BEGIN EXECUTE IMMEDIATE ‘SELECT f (:b) FROM DUAL’ INTO y USING pkg.x; END p1;

  C. CREATE PROCEDURE p2 (v IN OUT VARCHAR2) IS BEGIN EXECUTE IMMEDIATE ‘SELECT f (:b) FROM DUAL’ INTO v USING pkg.x; END p2;

  D. CREATE FUNCTION p3 RETURN pkg. tab_typ IS BEGIN EXECUTE IMMEDIATE ‘SELECT f (:b) FROM DUAL’ INTO pkg.x; END p3;

  E. CREATE PROCEDURE p5 (y pkg. rec_typ) IS BEGIN EXECUTE IMMEDIATE ‘SELECT pdt_name FROM TABLE (:b)’ BULK COLLECT INTO y USING pkg.x; END p5;

  Correct Answer: AC

• Question 87:

  Identify the two correct scenarios where a function can be optimized using the function result cache feature.

  A. A function which inserts multiple records into a DEPARTMENTS table as part of one-time data setup for an HR application.

  B. A function which accesses multiple tables and calculates the commission to be given to a sales representative based on the number of products sold by that representative.

  C. A function which deletes all the records from an EMPLOYEES_AUDIT table based on their LOG_DATE.

  D. A function which updates the SALARY of all the employees in an EMPLOYEES table by a fixed percentage based on their DESIGNATION.

  E. A function which calculates the factorial of a given number without accessing any table.

  Correct Answer: DE

• Question 88:

  Which data dictionary view contains the list of procedures and functions defined within package specification that you can execute?

  A. ALL_SOURCE

  B. ALL_DEPENDENCIES

  C. ALL_OBJECTS

  D. ALL_PROCEDURES

  E. ALL_PLSQL_OBJECT_SETTINGS

  Correct Answer: D

• Question 89:

  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 NUMBER) 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 PLS_INTEGER) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total;

  END;

  /

  D. CREATE FUNCTION totalEmp (sal IN BINARY_FLOAT) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /

  E. CREATE FUNCTION totalEmp (sal IN POSITIVEN) RETURN NUMBER IS total NUMBER :=0; BEGIN … RETUNRN total; END; /

  Correct Answer: BC

• Question 90:

  You created a PL/SQL function with the RESULT_CACHE clause, which calculates a percentage of total

  marks for each student by querying the MARKS table.

  Under which two circumstances will the cache for this function not be used and the function body be

  executed instead?

  A. When a user fixes incorrect marks for a student, with an update to the MARKS table, and then executes the function in the same session

  B. When the amount of memory allocated for the result cache is increased

  C. When the function is executed in a session frequently with the same parameter value

  D. When the database administrator disables the result cache during ongoing application patching

  E. When the maximum amount of server result cache memory that can be used for a single result is set to

  0.

  Correct Answer: DE