Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 161:

  Examine the structure of the PRODUCTS table. Name Null? Type

  PRODUCT_ID NOT NULL NUMBER(6) PRODUCT_NAME VARCHAR2(50) CATEGORY_ID NUMBER(2) SUPPLIER_ID NUMBER(6) LIST_PRICE NUMBER(8,2)

  View the Exhibit and examine the PL/SQL block.

  On execution, the PL/SQL block generates the following error:

  ORA-01001: invalid cursor

  What could be the reason?

  

  A. Both the cursor variable arguments should have been passed in IN mode.

  B. The contents of one cursor variable cannot be assigned to another cursor variable using the := operator.

  C. The CLOSE statement closes both the cursor variables, therefore the last FETCH statement cannot execute.

  D. The name of the cursor variables defined and the name of the cursor variables passed as arguments must be the same.

  Correct Answer: C

• Question 162:

  View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

  Examine the PL/SQL block that you execute to find the average salary for employees in the 'Sales'

  department:

  DECLARE

  TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20);

  v_emp_sal emp_sal;

  PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS

  BEGIN

  SELECT AVG(salary) INTO p_arr(p_dept_name)

  FROM employees WHERE department_id=

  (SELECT department_id FROM departments

  WHERE department_name=p_dept_name);

  END get_sal;

  BEGIN

  get_sal('Sales',v_emp_sal);

  DBMS_OUTPUT.PUT_LINE( v_emp_sal('Sales'));

  END;

  /

  What is the outcome?

  

  A. It executes successfully and gives the correct output.

  B. It generates an error because the associative array definition is not valid.

  C. It generates an error because an associative array cannot be passed to a procedure in OUT mode.

  D. It generates an error because an associative array cannot be used with the SELECT INTO statement.

  Correct Answer: A

• Question 163:

  View the Exhibit and examine the output.

  Which statement is an inference from the output?

  

  A. The class file is pinned into the Java pool.

  B. The class file has been loaded into the shared library.

  C. Java class methods in the Java class file have been published.

  D. The loadjava command has been executed to load the Java source and class files.

  Correct Answer: D

• Question 164:

  Examine the code in the following PL/SQL block:

  DECLARE

  TYPE NumList IS TABLE OF INTEGER;

  List1 NumList := NumList(11,22,33,44);

  BEGIN

  List1.DELETE(2);

  DBMS_OUTPUT.PUT_LINE

  ( 'The last element# in List1 is ' || List1.LAST ||

  ' and total of elements is '||List1.COUNT);

  List1.EXTEND(4,3);

  END;

  /

  Which two statements are true about the above code? (Choose two.)

  A. LAST and COUNT give different values.

  B. LAST and COUNT give the same values.

  C. The four new elements that are added contain the value 33.

  D. The four new elements that are added contain the value 44.

  Correct Answer: AC

• Question 165:

  Which two statements are true about the working of fine-grained access? (Choose two.)

  A. Security policies can be associated only with tables, but not with views.

  B. Different policies can be used for SELECT, INSERT, UPDATE, and DELETE statements.

  C. User statements are dynamically modified by the Oracle server through a security policy function.

  D. Fine-grained access control policies always remain in effect until they are dropped from a table or view.

  Correct Answer: BC

• Question 166:

  View the Exhibit and examine the code in the PL/SQL block.

  The PL/SQL block generates an error on execution. What is the reason?

  

  A. The DELETE(n) method cannot be used with varrays.

  B. The DELETE(n) method cannot be used with nested tables.

  C. The NEXT method cannot be used with an associative array with VARCHAR2 key values.

  D. The NEXT method cannot be used with a nested table from which an element has been deleted.

  Correct Answer: A

• Question 167:

  Which two statements are true about the tuning of PL/SQL code? (Choose two.)

  A. Redundant SQL statements in PL/SQL code should be avoided.

  B. Implicit data type conversion in PL/SQL code can improve performance.

  C. Usage of the NOT NULL constraint in PL/SQL code can degrade performance.

  D. If you have one PL/SQL program unit instead of multiple smaller executable sections, performance can be improved.

  Correct Answer: AC

• Question 168:

  You issue this command to create a table called LOB_STORE:

  CREATE TABLE lob_store

  (lob_id NUMBER(3),

  photo BLOB DEFAULT EMPTY_BLOB(),

  cv CLOB DEFAULT NULL,

  ext_file BFILE DEFAULT NULL)

  /

  What is the outcome?

  A. The table is created successfully.

  B. It generates an error because DEFAULT cannot be set to EMPTY_BLOB() during table creation.

  C. It generates an error because DEFAULT cannot be set to null for a CLOB column during table creation.

  D. It generates an error because DEFAULT cannot be set to null for a BFILE column during table creation.

  Correct Answer: A

• Question 169:

  Which two statements are true about the migration of BasicFile to the SecureFile format by using the DBMS_REDEFINITION package? (Choose two.)

  A. It can be performed only on tables with a single LOB column.

  B. It automatically creates an interim table during the migration process.

  C. It allows the table that is migrated to be accessed throughout the migration process.

  D. It requires free space that is at least equal to the space used by the table that is migrated.

  E. It requires all constraints defined on the original table to be re-created manually after the migration.

  Correct Answer: CD

• Question 170:

  You executed this command to gather information about the memory allocation for storing query results:

  SQL> execute dbms_result_cache.memory_report

  View the Exhibit and examine the output for the execution of the

  DBMS_RESULT_CACHE.MEMORY_REPORT procedure.

  Which two statements are true about the output in the Exhibit? (Choose two.)

  

  A. In total, four blocks are used by the result cache.

  B. Currently 52 KB is allocated to the result cache.

  C. Currently 32 KB is allocated to the result cache.

  D. The result cache can be increased by 65628 bytes.

  Correct Answer: CD