Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 191:

  Which two guidelines should be considered when designing and using cursors in a PL/SQL block? (Choose two.)

  A. When fetching from a cursor, fetch into a record.

  B. When fetching from a cursor, fetch required values into individually declared variables.

  C. Whenever possible, explicitly declare the cursor and use the OPEN, FETCH, and CLOSE statements to manipulate the cursor instead of using the cursor FOR loop.

  D. Whenever possible, use the cursor FOR loop instead of explicitly declaring the cursor and using the OPEN, FETCH, and CLOSE statements to manipulate the cursor.

  Correct Answer: AD

• Question 192:

  Examine the code snippet from the declarative section of a PL/SQL block:

  DECLARE

  TYPE va1 IS VARRAY(10) OF VARCHAR2(20);

  SUBTYPE scale IS NUMBER(1,0);

  TYPE tb1 IS TABLE OF departments.department_name%TYPE INDEX BY departments.department_id%

  TYPE;

  TYPE tb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;

  TYPE tb3 IS TABLE OF scale INDEX BY VARCHAR2(10);

  TYPE tb4 IS TABLE OF DATE INDEX BY DATE;

  TYPE tb5 IS TABLE OF NUMBER INDEX BY CHAR(2);

  ....

  Which of the above are valid definitions for associative arrays? (Choose all that apply.)

  A. tb1

  B. tb2

  C. tb3

  D. tb4

  E. tb5

  Correct Answer: BC

• Question 193:

  You designed a CardValidation.java Java source file. You also have the corresponding CardValidation.class file. As part of invoking a Java class method, you executed this command at the command prompt:

  loadjava -user oe/oe CardValidation.java

  Which statement is true about the command?

  A. It loads the Java code into the database.

  B. It publishes Java methods in CardValidation.java.

  C. It loads the metadata related to the Java class file into the database.

  D. It loads the Java class file into the Java pool in the database instance.

  Correct Answer: A

• Question 194:

  DATA_FILES is a directory object that contains the DETAILS.TXT text file.

  You have the required permissions to access the directory object.

  You create a table using the following command:

  CREATE TABLE clob_tab(col2 CLOB);

  View the Exhibit and examine the PL/SQL block that you execute for loading the external text file into the

  table that currently has no rows. The PL/SQL block results in an error.

  What correction must be done to ensure the PL/SQL block executes successfully?

  

  A. The L_OUT variable must be initialized to an empty locator.

  B. The L_OUT variable has to be declared as a temporary LOB.

  C. The A_CLOB variable has to be declared as a temporary LOB.

  D. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.

  Correct Answer: D

• Question 195:

  A procedure is created in the SYS schema to allow users to change the password as follows:

  CREATE OR REPLACE

  PROCEDURE change_password(p_username VARCHAR2 DEFAULT NULL,

  p_new_password VARCHAR2 DEFAULT NULL)

  IS

  v_sql_stmt VARCHAR2(500);

  BEGIN

  v_sql_stmt := 'ALTER USER '||p_username ||' IDENTIFIED BY '

  || p_new_password;

  EXECUTE IMMEDIATE v_sql_stmt;

  END change_password;

  The SYS user has granted EXECUTE privilege on the procedure to the OE user. But OE is able to change

  the password for SYS by using this procedure. How would you protect this?

  A. by using the procedure as part of a PL/SQL package

  B. by using a bind argument with dynamic SQL in the procedure

  C. by using AUTHID DEFINER in the procedure to implement the definer's right

  D. by using AUTHID CURRENT_USER in the procedure to implement the invoker's right

  Correct Answer: D

• Question 196:

  Identify two factors that you consider for compiling a PL/SQL program unit for interpreted mode. (Choose two.)

  A. a PL/SQL program which needs to be recompiled frequently

  B. a PL/SQL program that spends most of the time executing SQL

  C. a PL/SQL program, which performs computation-intensive procedural operations and is recompiled rarely

  D. a PL/SQL program, which is called frequently with the same parameter values by multiple sessions and is recompiled rarely

  Correct Answer: AB

• Question 197:

  Examine the section of code taken from a PL/SQL program:

  PROCEDURE p1 (x PLS_INTEGER) IS

  ... ...

  PRAGMA INLINE (p1, 'NO'); x:= p1(1) + p1(2) + 17; -- Call 1 ... x:= p1(3) + p1(4) + 17; -- Call 2 Call 1 and Call 2 are the comments for distinguishing the code. The PLSQL_OPTIMIZE_LEVEL parameter

  is set to 3. Which two statements are true in this scenario? (Choose two.)

  A. The calls to the P1 procedure are not inlined in the section commented as Call 1.

  B. The calls to the P1 procedure might be inlined in the section commented as Call 2.

  C. The calls to the P1 procedure are inlined in both the sections commented as Call 1 and Call 2.

  D. The calls to the P1 procedure are never inlined in both the sections commented as Call 1 and Call 2.

  Correct Answer: AB

• Question 198:

  Which two types of query results cannot be stored in the query result cache? (Choose two.)

  A. subquery results

  B. results of a query having the SYSDATE function

  C. results of a query having the GROUP BY clause

  D. results of a query having the DATE data type in the WHERE clause

  Correct Answer: AB

• Question 199:

  You execute the following command in the user session: SQL> ALTER SESSION SET PLSQL_DEBUG=true; Which statement is true about the effect of the command?

  A. All PL/SQL blocks that are executed subsequently in the session are traced.

  B. It enables all PL/SQL blocks that are compiled subsequently in the session for tracing.

  C. Only anonymous PL/SQL blocks that are executed subsequently in the session are traced.

  D. It enables only named PL/SQL blocks that are executed subsequently in the session for tracing.

  Correct Answer: B

• Question 200:

  Which statements are true about temporary LOBs? (Choose all that apply.)

  A. They can be created only for CLOB and NCLOB data.

  B. They can be accessed only by the user who creates them.

  C. They generate more redo information than persistent LOBs.

  D. They exist for the duration of the session in which they are created.

  E. They are stored temporarily in the default tablespace of the user who creates them.

  Correct Answer: BD