Oracle 1Z0-148 Online Practice Questions and Exam Preparation

Oracle 1Z0-148 Online Questions & Answers

• Question 1:

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

  TEST_ID NUMBER

  DESCRIPTION CLOB

  DESCRIPTION data was entered earlier and saved for TEST_ID 12.

  You execute this PL/SQL block to add data to the end of the existing data in the DESCRIPTION column for TEST_ID 12:

  DECLARE

  clob_loc CLOB;

  buf CHAR(12);

  BEGIN

  SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;

  buf := '0123456789';

  DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf);

  COMMIT;

  END;

  /

  It generates an error on execution.

  What correction should you do to achieve the required result?

  A. WRITEAPPEND must be replaced with APPEND.
  B. The BUF variable data type must be changed to CLOB.
  C. FOR UPDATE must be added to the SELECT statement.
  D. The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND.
  C. FOR UPDATE must be added to the SELECT statement.

• Question 2:

  Which two statements are true about cursor variables? (Choose two.)

  A. Cursor variables can be parameterized like cursors.
  B. The query associated with a cursor variable cannot reference host variables and PL/SQL variables.
  C. The FETCH statement executes the query associated with a cursor variable and identifies the result set.
  D. Cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) can be applied to a cursor variable.
  E. The OPEN FOR statement executes the query associated with a cursor variable and identifies the result set.
  D. Cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) can be applied to a cursor variable.
  E. The OPEN FOR statement executes the query associated with a cursor variable and identifies the result set.

• Question 3:

  Select a valid reason for using VARRAYS.

  A. When the amount of data to be held in the collection is widely variable.
  B. As a column in a table when you want to retrieve the collection data for certain rows by ranges of values.
  C. When you want to delete elements from the middle of the collection.
  D. As a column in a table when you want to store no more than 10 elements in each row's collection.
  B. As a column in a table when you want to retrieve the collection data for certain rows by ranges of values.

  ---

  Reference https://www.go4expert.com/articles/oracle-nested-tables-varrays-t20494/

• Question 4:

  Which two statements are true about the context of an application? (Choose two.)

  A. It is attached to a session.
  B. It is owned by the user SYS.
  C. A user can change the context of his or her application.
  D. The PL/SQL package associated with the context must exist before the context is created.
  E. The predefined attributes in the USERENV application context can be changed as per the requirements.
  A. It is attached to a session.
  B. It is owned by the user SYS.

• Question 5:

  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.
  A. The end result is the creation of DDL for the TIMECARDS table with all instances of the HR schema changed to SCOTT.

• Question 6:

  You created a procedure as follows:

  CREATE OR REPLACE PROCEDURE query_prod(twhr VARCHAR2)

  IS

  stmt VARCHAR2(100);

  pname VARCHAR2(20);

  BEGIN

  stmt:='SELECT product_name FROM products WHERE product_id=:2';

  EXECUTE IMMEDIATE stmt INTO pname USING twhr;

  DBMS_OUTPUT.PUT_LINE(pname);

  END;

  /

  View the Exhibit to examine the structure of PRODUCTS table.

  Which statement is true about the procedure?

  

  A. It produces an error when invoked.
  B. It can be invoked only from a PL/SQL block.
  C. It reduces the chances of SQL injection by using bind arguments.
  D. The values for bind arguments remain persistent in the session after the execution of the procedure.
  C. It reduces the chances of SQL injection by using bind arguments.

• Question 7:

  Examine this statement:

  

  Which result is expected after executing this anonymous block?

  A. PLS-00316: PL/SQL TABLEs must use a single index
  B. 60
  C. PLS-00382: expression is of wrong type
  D. 30
  E. ORA-06533: Subscript beyond count
  A. PLS-00316: PL/SQL TABLEs must use a single index

  ---

  

• Question 8:

  Your company's executive team voted to give a one-time special bonus, the amount of which is to be based on number of years of service. The bonus table will be updated using a procedure called update_bonus(). Examine this PL/SQL block which shows the logic to calculate the bonus:

  

  Consider these possible optimization actions:

  1.

  Remove the service_days calculation statement.

  2.

  Move bonus1 and bonus2 initializations out of the loop.

  3.

  Inline the update_bonus() subprogram.

  4.

  Call update_bonus(), passing the literal bonus amount instead of the bonus1 or bonus2 variables. Which set of optimizations will be performed if this block is compiled with PLSQL_OPTIMIZE_LEVEL = 2?

  A. 3 and 4 only
  B. 1, 2, 3, and 4
  C. 1, 2, and 4 only
  D. 1 and 2 only
  E. 1 and 3 only
  D. 1 and 2 only

• Question 9:

  Examine the structure of the EMPLOYEES table in the SCOTT schema. Name Null? Type

  EMPLOYEE_ID NOT NULL NUMBER(6)

  FIRST_NAME VARCHAR2(20)

  LAST_NAME NOT NULL VARCHAR2(25)

  SALARY NOT NULL NUMBER(8,2)

  COMMISSION_PCT NUMBER(2,2)

  DEPARTMENT_ID NUMBER(4)

  View the Exhibit and examine the code for the EMP_TOTSAL procedure created by user SCOTT.

  Which statement is true regarding the EMP_TOTSAL procedure?

  

  A. It is created successfully, but displays the correct output message only for existent employee IDs.
  B. It is created successfully and displays the correct output message for both existent and nonexistent employee IDs.
  C. It generates an error because the %NOTFOUND attribute cannot be used in combination with a SELECT INTO statement.
  D. It generates an error because a user-defined exception has to be included whenever the %NOTFOUND attribute is used in combination with a SELECT INTO statement.
  A. It is created successfully, but displays the correct output message only for existent employee IDs.

• Question 10:

  Examine this code executed as SYS:

  

  Examine this code executed as SPIDER and the error message received upon execution:

  

  What is the reason for this error?

  A. The procedure needs to be granted the DYNAMIC_TABLE_ROLE role.
  B. The EXECUTE IMMEDIATE clause is not supported with roles.
  C. Privileges granted through roles are never in effect when running definer's rights procedures.
  D. The user SPIDER needs to be granted the CREATE TABLE privilege and the procedure needs to be granted the DYNAMIC_TABLE_ROLE.
  C. Privileges granted through roles are never in effect when running definer's rights procedures.