Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 151:

  View the Exhibit and examine the settings for the PLSQL_CODE_TYPE parameter.

  After sometime, the user recompiles the procedure DISPLAY_SAL_INFO by issuing the following command:

  SQL> ALTER PROCEDURE display_sal_info COMPILE;

  Which statement would be true in this scenario?

  

  A. The procedure would be invalidated.

  B. The procedure would remain as NATIVE code type.

  C. The procedure would be changed to INTERPRETED code type.

  D. The command would produce an error and the procedure must be compiled using the PLSQL_CODE_TYPE attribute with value INTERPRETED.

  Correct Answer: C

• Question 152:

  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.

  Correct Answer: E

• Question 153:

  In which two situations is the body of a result-cached function executed? (Choose two.)

  A. if the memory allocated for the result cache is increased

  B. if a session on this database instance invokes the function with the same parameter values

  C. if the first time a session on this database instance invokes the function with a parameter value

  D. if a session executes a data manipulation language (DML) statement on a table or view that was specified in the RELIES_ON clause of a result-cached function

  Correct Answer: CD

• Question 154:

  View the Exhibit and examine the structure of the EMPLOYEES table.

  Examine the following PL/SQL block for storing the salary of all sales representatives from the

  EMPLOYEES table in an associative array:

  1 DECLARE

  2 emp_cv SYS_REFCURSOR;

  3 TYPE list IS TABLE OF emp_cv;

  4 sals list;

  5 BEGIN

  6 OPEN emp_cv FOR SELECT salary FROM employees

  7 WHERE job_id = 'SA_REP';

  8 FETCH emp_cv BULK COLLECT INTO sals;

  9 CLOSE emp_cv;

  10 END;

  What should you correct in the above code to ensure that it executes successfully?

  

  A. Replace EMP_CV in line 3 with employees.salary%TYPE.

  B. Replace line 2 with TYPE refcur IS REF CURSOR; emp_cv refcur;.

  C. Replace BULK COLLECT in line 8 with the OPEN, FETCH, LOOP, and CLOSE statements.

  D. Replace line 2 with TYPE refcur IS REF CURSOR RETURN employees.salary%TYPE; emp_cv refcur;.

  Correct Answer: A

• Question 155:

  Which two statements correctly describe the features of SecureFiles? (Choose two.)

  A. Compression does not entail table or index compression and vice-versa.

  B. Encryption stores the encryption keys for the LOB columns inside the database.

  C. Encryption stores the encryption keys for the LOB columns outside the database.

  D. Compression stores identical data occurring two or more times in the same LOB column as a single copy for the table.

  Correct Answer: AC

• Question 156:

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

  TEXT_ID NUMBER

  DOC1 CLOB

  DOC2 CLOB

  You issue the following INSERT commands:

  INSERT INTO text_tab VALUES (1, 'This is line 1',null);

  INSERT INTO text_tab VALUES (2, 'This is line 1','This is line 2');

  Then you execute the following block of the PL/SQL code:

  DECLARE

  vc1 VARCHAR2(1000):= 'This is the preface';

  lb1 CLOB;

  lb2 CLOB;

  BEGIN

  SELECT doc1 INTO lb1 FROM text_tab WHERE text_id=1;

  SELECT doc1 || doc2 INTO lb1 FROM text_tab WHERE text_id=2;

  lb2 := vc1|| lb1;

  UPDATE text_tab SET doc2 = lb2 WHERE text_id = 1;

  END;

  /

  What is the outcome?

  A. It executes successfully.

  B. It gives an error because VARCHAR2 should be explicitly converted to CLOB.

  C. It gives an error because CLOB variables should be initialized to EMPTY_CLOB().

  D. It gives an error because the concatenation operator cannot be used with the CLOB data type.

  Correct Answer: A

• Question 157:

  You have an OE_ACCESS_ORDERS_POLICY security policy implemented on the ORDERS table in the OE schema. The user sessions are able to access only the desired rows. The database administrator (DBA) uses the following command:

  SQL> EXECUTE DBMS_RLS.ENABLE_POLICY('OE','ORDERS','OE_ORDERS_ACCESS_POLICY',FALSE );

  Which statement is true about user sessions that are connected currently?

  A. The security policy remains in effect till the end of the current session.

  B. The subsequent queries on the ORDERS table produce an ORA-01031: insufficient privileges error.

  C. The subsequent queries on the ORDERS table within the current session are not controlled by the security policy.

  D. The subsequent queries on the ORDERS table produce an ORA-28112: failed to execute policy function error.

  Correct Answer: C

• Question 158:

  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.

  Correct Answer: D

• Question 159:

  The user OE is working on an application that needs to call an external C program multiple times in a single session. However, the extproc.exe file on the server gets accidentally deleted after the OE user connected and made calls to the external C program. Which statement is true about the current session by the OE user?

  A. The session can continue calling the external C program.

  B. The session can call the external C program after republishing it.

  C. The session receives an error for the next call to the external C program.

  D. The session terminates during the subsequent call to the external C program.

  Correct Answer: A

• Question 160:

  Which two statements are true about REF CURSOR types? (Choose two.)

  A. REF CURSOR types cannot be defined inside a package.

  B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.

  C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a user- defined record.

  D. Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function.

  Correct Answer: BC