Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 131:

  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 132:

  Which two statements are true about associative arrays and varrays? (Choose two.)

  A. Only varrays must start with the subscript 1.

  B. Only varrays can be used as column types in database tables.

  C. Both associative arrays and varrays must start with the subscript 1.

  D. Both associative arrays and varrays can be used as column types in database tables.

  Correct Answer: AB

• Question 133:

  View the Exhibit to examine the PL/SQL code for the GET_METADATA function. Which statement is true about the metadata gathered by the function?

  

  A. The end result is the creation of DDL for the TIMECARDS table with all instances of the HR schema changed to SCOTT.

  B. The end result is the creation of an XML document for all tables with all physical, storage, logging, and other segment attributes.

  C. The end result is the creation of DDL for all tables with all instances of the HR schema changed to SCOTT along with all physical, storage, logging, and other segment attributes.

  D. The end result is the creation of DDL for all tables and associated indexes with all instances of the HR schema changed to SCOTT along with all physical, storage, logging, and other segment attributes.

  Correct Answer: A

• Question 134:

  Which three statements are true about hierarchical profiling? (Choose three.)

  A. It provides function-level summaries.

  B. It produces an aggregated report of the SQL and PL/SQL execution times.

  C. It is useful for understanding the structure and control flow of complex programs.

  D. It can be used to identify hotspots and tuning opportunities in PL/SQL applications.

  E. It generates trace information for PL/SQL programs in the PLSQL_TRACE_EVENTS table.

  Correct Answer: ACD

• Question 135:

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

  LOB_ID NUMBER

  VIDEO_CLIP BLOB

  You create a 'DATA_FILES' directory object that contains the 'IMAGE1.GIF'operating system (OS) file.

  View the Exhibit and examine the LOAD_LOB procedure code that you execute for reading data from the

  OS file into the BLOB column.

  It is created with compilation errors.

  What is the reason?

  

  A. V_BLOB should be initialized to EMPTY_BLOB().

  B. The WRITE routine of DBMS_LOB should be used instead of LOADFROMFILE.

  C. The return type of DBMS_LOB.FILEEXISTS is not compatible with the IF statement.

  D. OPEN, CLOSE, and READONLY routines of DBMS_LOB should be used instead of FILEOPEN, FILECLOSE, and FILE_READONLY.

  Correct Answer: C

• Question 136:

  View the Exhibit and examine the procedure to create a trigger name based on the table name supplied to the procedure.

  Which three statements are appropriate for protecting the code in the procedure from SQL injection? (Choose three.)

  

  A. Explicitly validate the identifier length limit.

  B. Add AUTHID DEFINER to the definition of the procedure.

  C. Use PRAGMA RESTRICT_REFERENCES in the procedure.

  D. Filter out control characters in user-supplied identifier names.

  E. Use the object ID of the table from the data dictionary to build the trigger name.

  Correct Answer: ADE

• Question 137:

  In a user session, tracing is enabled as follows:

  SQL> EXECUTE

  DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ENABLED_LINES);

  PL/SQL procedure successfully completed.

  You executed the procedure as follows:

  SQL> EXECUTE PROC10

  PL/SQL procedure successfully completed.

  When you examine the PLSQL_TRACE_EVENTS table, you find that no trace information was written into

  it.

  View the Exhibit.

  What is the reason for this?

  

  A. The PROC10 procedure is created with the invoker's right.

  B. The PROC10 procedure is not compiled with the DEBUG option.

  C. Tracing is not enabled with the TRACE_ENABLED_CALLS option.

  D. The TRACE_ENABLED parameter is set to FALSE for the session.

  Correct Answer: B

• Question 138:

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

  DEPARTMENT_ID NOT NULL NUMBER(4)

  DEPARTMENT_NAME NOT NULL VARCHAR2(30)

  LOCATION_ID NUMBER(4)

  View the Exhibit and examine the code that you plan to use for creating a package to obtain the details of

  an employee using a host variable on the client side.

  In SQL*Plus, you plan to use the following commands:

  SQL> VARIABLE x REFCURSOR

  SQL> EXECUTE emp_data.get_emp(195,:x)

  SQL> PRINT x

  Which statement is true about the above scenario?

  

  A. The package executes successfully and passes the required data to the host variable.

  B. The package specification gives an error on compilation because cursor variable types cannot be defined in the specification.

  C. The package specification gives an error on compilation because the cursor variable parameter was specified before you defined it.

  D. The package executes successfully, but does not pass the required data to the host variable because the cursor is closed before the PRINT statement runs.

  Correct Answer: D

• Question 139:

  Which two are major approaches that can be used to reduce the SQL injection by limiting user input? (Choose two.)

  A. Restrict users accessing specified web page.

  B. Use NUMBER data type if only positive integers are needed.

  C. Use dynamic SQL and construct it through concatenation of input values.

  D. In PL/SQL API, expose only those routines that are intended for customer use.

  Correct Answer: AD

• Question 140:

  Examine the following settings for a session:

  PLSQL_CODE_TYPE = NATIVE

  View the Exhibit and examine the PL/SQL code.

  You compile the program with the following attributes:

  SQL> ALTER PROCEDURE proc1 COMPILE PLSQL_OPTIMIZE_LEVEL = 1;

  Which statement is true about the execution of the PROC1 procedure in this scenario?

  

  A. The FUNC1 function would be called inline because PRAGMA INLINE forces a specific call to be inlined.

  B. The FUNC1 function would be inlined because the value set for the PLSQL_CODE_TYPE parameter is set to NATIVE.

  C. The FUNC1 function would be called inline irrespective of the value set for the PLSQL_OPTIMIZE_LEVEL parameter.

  D. The FUNC1 function would not be called inline because the value for the PLSQL_OPTIMIZE_LEVEL parameter is set to a lower value.

  Correct Answer: D