Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 21:

  Which is the correct method for releasing unused money after deleting elements of a collection?

  A. Flush the shared pool using the ALTER SYSTEM command.

  B. Use the TRUNCATE method on the collection.

  C. Execute DBMS_SHARED_POOL.UNKEEP.

  D. Execute DBMS_SESSION.FREE_UNUSED_USER_MEMORY.

  Correct Answer: D

  Reference: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sessio.htm#BHCCFGIF

• Question 22:

  The user MY_USER has been assigned the roles CONNECT, RESOURCE, and DBA.

  Which statement generates the DDL for these privileges?

  A. SELECT DBMS_METADATA.GET_GRANTED_DDL ('SYSTEM_GRANT', 'my_user') FROM dual;

  B. SELECT DBMS_METADATA.GET_GRANTED_DDL ('ROLE_GRANT', 'my_user') FROM dual;

  C. SELECT DBMS_METADATA.GET_GRANTED_DDL ('OBJECT_GRANT', 'my_user') FROM dual;

  D. SELECT DBMS_METADATA.GET_DDL ('USER', 'my_user') FROM dual;

  Correct Answer: D

  Reference: https://www.databasedevelop.com/article/10596168/CONNECT+ROLE+and+CREATE +SESSION

• Question 23:

  Examine these statements regarding the DBMS_RESULT_CACHE package:

  1.

  DBMS_RESULT_CACHE provides subprograms to administer the part of the shared pool used by the PL/SQL function result cache only.

  2.

  DBMS_RESULT_CACHE provides subprograms to administer the part of the shared pool used by both the PL/SQL function result cache and the SQL result cache.

  3.

  DBMS_RESULT_CACHE package is owned by the SYS user.

  4.

  RESULT_CACHE_MODE can be set using a subprogram defined in the DBMS_RESULT_CACHE package.

  Which set of statements is correct?

  A. 2, 3 and 4

  B. 1 and 4

  C. 2 and 4

  D. 1 and 3

  E. 2 and 3

  Correct Answer: C

  Reference: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_result_cache.htm

• Question 24:

  Examine the EMP table:

  CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10));

  Examine this PL/SQL procedure:

  

  Which is the correct replacement for the assignment to variable l_sql to avoid SQL injection?

  A. l_sql := 'SELECT empno FROM emp WHERE ename = ''' || SYS.DBMS_ASSERT.NOOP (p_ename) || '''';

  B. l_sql := 'SELECT empno FROM emp WHERE ename = ' || SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME (p_ename);

  C. l_sql := 'SELECT empno FROM emp WHERE ename = ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME (p_ename);

  D. l_sql := 'SELECT empno FROM emp WHERE ename = ' || SYS.DBMS_ASSERT.ENQUOTE_LITERAL (p_ename);

  E. l_sql := 'SELECT empno FROM emp WHERE ename = ''' || SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME (p_ename) || '''';

  Correct Answer: D

• Question 25:

  Examine the structure of the EMP table:

  

  Examine this code and statements:

  

  Which statement is true?

  A. The result cache gets invalidated after the ALTER FUNCTION and again after the second UPDATE statement.

  B. The result cache gets invalidated after the ALTER FUNCTION and again after each UPDATE statement.

  C. The result cache gets invalidated only after the ALTER FUNCTION statement.

  D. The result cache does not get invalidated at all since the RELIES_ON clause is not used.

  E. The result cache gets invalidated only after each UPDATE statement.

  F. The result cache gets invalidated only after the second UPDATE statement.

  Correct Answer: D

• Question 26:

  Examine this code:

  

  Assuming the default edition is ORA$BASE, what is the result of the last execution of hello?

  A. Hello, edition 1. Hello, edition 2.

  B. Hello, edition 1.

  C. Hello, edition 2.

  D. Hello, edition 2.

  Hello, edition 1.

  E. An exception is raised.

  Correct Answer: B

  Reference: https://maleshg.wordpress.com/2013/12/24/edition-based-redefinition-allows-multiple-versionsof-plsql-objects/

• Question 27:

  Examine the code:

  

  Which two blocks can be used to complete the executable section such that LIST2 is dense? (Choose two.)

  A. FOR idx IN list1.FIRST .. list1.LAST LOOP IF list1.EXISTS(idx) THEN list2(idx) := list1(idx); END IF; END LOOP;

  B. FOR idx IN list1.FIRST .. list1.LAST LOOP list2(idx) := list1(idx); END LOOP;

  C. idx:= list1.FIRST; WHILE (idx IS NOT NULL) LOOP list2(list2.COUNT + 1) := list1(idx); idx := list1.NEXT(idx); END LOOP; list1.LAST LOOP

  D. FORALL idx IN list1.FIRST .. list2(list2.COUNT + 1) := list1(idx);

  E. idx:= list1.FIRST; LOOP EXIT WHEN idx IS NULL; list2(list2.COUNT + 1) := list1(idx); idx := list1.NEXT(idx); END LOOP;

  Correct Answer: CD

  Reference https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99931 http://www.orafaq.com/forum/t/195491/

• Question 28:

  In which two situations will a result-cached function execute even though no change was made on a dependent table or view? (Choose two.)

  A. when a function was created with invoker's rights using AUTHID CURRENT_USER

  B. when the database administrator has disabled the use of the result cache

  C. when the results for the function have aged out of the cache

  D. when the RELIES_ON clause was not used while creating the function

  E. when a new session was opened to invoke the function which is already cached

  Correct Answer: BC

• Question 29:

  Examine the DBMS_LOB.LOADCLOBFROMFILE procedure: Which statement is true about DBMS_LOB.LOADCLOBFROMFILE?

  

  A. The amount parameter can be set to NULL to indicate to load to the end of the BFILE.

  B. The amount parameter can be set to DBMS_LOB.LOBMAXSIZE to indicate to load to the end of the BFILE.

  C. If any error occurs when calling the procedure then it is written to the warning parameter and DBMS_LOB.LOADCLOBFROMFILE finishes successfully.

  D. An exception will always be raised if amount plus src_offset exceeds the length of the BFILE.

  Correct Answer: B

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/ DBMS_LOB.html#GUID-1C62CBAA-DFB9-444E-9D21-54733A10E95D

• Question 30:

  Examine this code: What will be the outcome with SERVEROUTPUT enabled?

  

  A. The PL/SQL block will fail with a runtime exception.

  B. LENGTH : 100.13 EXTRA_LEN : 15.25 TOTAL LENGTH : 115.38

  C. LENGTH : 100.1254 EXTRA_LEN : 15.247 TOTAL LENGTH : 115.37

  D. LENGTH : 100.1254 EXTRA_LEN : 15.25 TOTAL LENGTH : 115.38

  E. LENGTH : 100.1254 EXTRA_LEN : 15.25 TOTAL LENGTH : 115.3754

  Correct Answer: A