Oracle 1Z0-148 Online Practice Questions and Exam Preparation

Oracle 1Z0-148 Online Questions & Answers

• Question 91:

  View Exhibit1 and examine the structure of the EMPLOYEES table.

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

  The PL/SQL block fails to execute.

  What could be the reason?

  

  

  A. Nested tables cannot be returned by a function.
  B. The NEWNAMES nested table has not been initialized.
  C. The assignment operator cannot be used to transfer all the element values from GROUP1 to GROUP2.
  D. The third element of OLDNAMES cannot be assigned to the third element of GROUP1 because they are of inconsistent data types.
  E. LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements.
  E. LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements.

• Question 92:

  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.
  A. Redundant SQL statements in PL/SQL code should be avoided.
  C. Usage of the NOT NULL constraint in PL/SQL code can degrade performance.

• Question 93:

  View the Exhibit.

  How do you reduce the chances of SQL injection for the procedure?

  

  A. Execute the SQL statement in V_STMT as dynamic SQL.
  B. Remove the default value for the arguments in the procedure.
  C. Convert the condition in the WHERE clause to be accepted from the user and concatenated.
  D. Convert the SELECT statement to static SQL, placing the value of P_EMAIL into a local variable.
  D. Convert the SELECT statement to static SQL, placing the value of P_EMAIL into a local variable.

• Question 94:

  Examine this code:

  

  Which two corrections must be applied for this anonymous block to execute successfully?

  A. Change RETURN p_tab.LAST to RETURN p_tab.COUNT.
  B. Declare the collection type inside the function.
  C. Declare the collection type at the schema level instead of the package.
  D. Define the function as stand-alone instead of in a package body.
  E. Change the INDEX BY clause from VARCHAR2 to PLS_INTEGER.
  F. Modify the function return type to return a scalar, VARCHAR2.
  E. Change the INDEX BY clause from VARCHAR2 to PLS_INTEGER.
  F. Modify the function return type to return a scalar, VARCHAR2.

• Question 95:

  Examine the structure of the PRINT_MEDIA table: Name Null? Type

  ADVT_ID NUMBER

  ADVT_SOURCE CLOB

  Examine the following PL/SQL block:

  DECLARE

  lobloc CLOB;

  buffer VARCHAR2(100);

  amount NUMBER;

  offset NUMBER :=1;

  BEGIN

  buffer :='This is the second line of a new document';

  amount := LENGTH(buffer);

  SELECT advt_source INTO lobloc FROM print_media WHERE advt_id=2 FOR UPDATE;

  DBMS_LOB.WRITE(lobloc,amount,offset,buffer);

  COMMIT;

  END;

  /

  What must be the value in the ADVT_SOURCE column for the above code to execute successfully?

  A. null
  B. an empty locator
  C. a non-NULL value
  D. either null or any non-NULL values
  C. a non-NULL value

• Question 96:

  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;
  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;
  C. CREATE PROCEDURE p2 (v IN OUT VARCHAR2) IS BEGIN EXECUTE IMMEDIATE ‘SELECT f (:b) FROM DUAL’ INTO v USING pkg.x; END p2;

• Question 97:

  Which two types of metadata can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package? (Choose two.)

  A. report of invalidated objects in a schema
  B. report of statistics of an object in the database
  C. DDL for all object grants on a table in the database
  D. data definition language (DDL) for all objects in a schema
  C. DDL for all object grants on a table in the database
  D. data definition language (DDL) for all objects in a schema

• Question 98:

  You set RESULT_CACHE_MAX_SIZE to a nonzero value to enable result caching. You executed the following command to check the status for the result cache:,

  SQL> select dbms_result_cache.status() from dual;

  You receive the following output:

  DBMS_RESULT_CACHE.STATUS()

  DISABLED

  Identify the reason for the output.

  A. The RESULT_CACHE_MODE parameter is set to FORCE.
  B. The RESULT_CACHE_MODE parameter is set to MANUAL.
  C. The database instance is not able to allocate memory for the result cache.
  D. The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.
  D. The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.

• Question 99:

  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.
  A. LAST and COUNT give different values.
  C. The four new elements that are added contain the value 33.

• Question 100:

  Examine the structure of the EMP table:

  Name

  Null?

  Type

  ——————

  ————–

  ———————

  EMPNO ENAME

  NOT

  NULL NUMBER (4)VARCHAR2 (10) SAL

  NUMBER (7, 2)

  Examine this code:

  

  Which code should be inserted to display the collection contents?

  A. 1_indx := 1_list.FIRST; WHILE (1_indx IS NOT NULL) LOOP DBMS_OUTPUT.PUT_LINE (1_indx || ‘ ’ || 1_list (1_indx)); 1_indx := 1_emp.NEXT (1_indx); END LOOP;
  B. FOR indx IN 1_list. COUNT .. -1 LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx)); END LOOP;
  C. FOR indx IN -1 .. 1_list.LIMIT LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx) ); END LOOP;
  D. FOR indx IN 1_list.FIRST . . 1_list.LAST LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx)); END LOOP;
  C. FOR indx IN -1 .. 1_list.LIMIT LOOP DBMS_OUTPUT.PUT_LINE (indx | | ‘ ’ | | 1_list (indx) ); END LOOP;