Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 31:

  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 1_dept IS EMPTY.

  B. Change the IF condition in the anonymous block to CARDINALITY (1_dept) IS NULL.

  C. Add BEQUEATH DEFINER to the EMP_COUNT_VW view.

  D. Add BEQUEATH CURRENT_USER to the EMP_COUNTS_VW view.

  E. IN BRANCH2 execute GRANT INHERIT PRIVILEGES ON USER branch2 TO branch1;

  F. Add AUTHID CURRENT_USER to the EMP_COUNT function.

  Correct Answer: BDE

• Question 32:

  Which three statements are true about varrays in a database table column? (Choose three.)

  A. A varray used as a column in a database table must be initialized to empty.

  B. A varray may be stored outside the table segment in which it is defined but still in the same tablespace.

  C. A varray's size can be increased beyond the maximum number of elements specified in the definition.

  D. A varray is always stored in the same segment as the table in which it is defined.

  E. The data in a single element of a varray can be changed through either PL/SQL or SQL.

  F. The data in a single element of a varray can only be changed through PL/SQL.

  G. A varray's size can be both increased and decreased from the maximum number of elements specified in the definition.

  H. A varray used as a column in a database table must be initialized to null.

  Correct Answer: BEG

  Reference: https://stackoverflow.com/questions/29787961/difference-between-nested-table-and-varray

• Question 33:

  Which two statements will raise an exception? (Choose two.)

  A. SELECT DBMS_ASSERT.SIMPLE_SQL_NAME ('"10_Employees"') FROM dual;

  B. SELECT DBMS_ASSERT.SIMPLE_SQL_NAME ('Employees@my_dblink') FROM dual;

  C. SELECT DBMS_ASSERT.SIMPLE_SQL_NAME (' Emp_Dept_information_table ') FROM dual;

  D. SELECT DBMS_ASSERT.SIMPLE_SQL_NAME ('Employee_Department_information_table') FROM dual;

  E. SELECT DBMS_ASSERT.SIMPLE_SQL_NAME ('Employees@') FROM dual;

  Correct Answer: AE

  Reference: http://psoug.org/reference/dbms_assert.html

• Question 34:

  Examine this code:

  CREATE TYPE list_typ IS TABLE OF NUMBER;

  /

  DECLARE

  l_list list_typ := list_typ ();

  Which two executable sections will display the message TRUE?

  A. BEGIN IF l_list.LIMIT IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

  B. BEGIN l_list.EXTEND; IF l_list.PRIOR (1_list.FIRST) IS NULL THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

  C. BEGIN l_list.EXTEND; IF l_list IS EMPTY THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

  D. BEGIN IF l_list.FIRST IS NULL THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

  E. BEGIN IF l_list.FIRST =1 THEN DBMS_OUTPUT.PUT_LINE (‘TRUE’); END IF; END;

  Correct Answer: BD

• Question 35:

  Examine this code (all compiled into schema HR): Execute this code:

  

  SET SERVEROUTPUT ON;

  EXEC test_pkg.do_stuff What is the correct output?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: E

• Question 36:

  You are designing and developing a complex database application and implementing fine-grained access control using security policies.

  Which statement is true with respect to attaching security policies to database objects?

  A. You can use different security policies for SELECT, INSERT, UPDATE, INDEX and DELETE statements.

  B. You can use only one security policy per database object.

  C. You implement security policies through database procedures.

  D. Column-masking policies can be applied to SELECT, INSERT, UPDATE and DELETE statements.

  Correct Answer: D

• Question 37:

  Examine this query executed as SYS and its output:

  

  Which two observations are true based on the output?

  A. The client-side result cache and the server-side result cache are enabled.

  B. All distinct query results are cached for the duration of a SYS user session.

  C. Repetitive SQL queries and PL/SQL function results are cached and automatically used from the cache across all SYS user sessions.

  D. The result cache exists but which SQL queries are cached depends on the value of the RESULT_CACHE_MODE parameter.

  E. Repetitive SQL queries executed on permanent non-dictionary objects may have faster response times.

  Correct Answer: CD

• Question 38:

  Which two statements about the PL/SQL hierarchical profiler are true?

  A. Access it using the DBMS_PROFILER package.

  B. Access it using the DBMS_HPROF package.

  C. Profiler data is recorded in tables and published in HTML reports.

  D. It is only accessible after a grant of the CREATE PROFILE privilege.

  E. It helps you identify subprograms that are causing bottlenecks in application performance.

  Correct Answer: BE

  Reference: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/tuning.htm#LNPLS01214

• Question 39:

  Which two statements are true with respect to fine-grained access control?

  A. It is implemented by end users.

  B. It can be used to implement column masking.

  C. It implements security rules through functions and associates these security rules with tables, views or synonyms.

  D. Separate policies are required for queries versus INSERT/UPDATE/DELETE statements.

  E. The DBMS_FGA package is used to set up fine-grained access control.

  Correct Answer: CD

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14220/security.htm

• Question 40:

  Examine this function header: FUNCTION calc_new_sal (emp_id NUMBER) RETURN NUMBER;

  You want to ensure that whenever this PL/SQL function is invoked with the same parameter value across active sessions, the result is not recomputed.

  If a DML statement is modifying a table which this function depends upon, the function result must be recomputed at that point in time for all sessions calling this function.

  Which two actions should you perform?

  A. Ensure RESULT_CACHE_MAX_SIZE is greater than 0.

  B. Enable the result cache by using DBMS_RESULT_CACHE.BYPASS (FALSE).

  C. Add the deterministic clause to the function definition.

  D. Add the RELIES_ON clause to the function definition.

  E. Add the RESULT_CACHE clause to the function definition.

  Correct Answer: AC