1z0-149 practice test

1. Question 1 · 1

   Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.)

   Choose all answers that apply.

   • PACKAGES
   • VARIABLES
   • ANONYMOUS BLOCK
   • TRIGGER BODY
   • DATABASE LINKS

2. Question 2 · 1

   Which three are valid PL/SQL variable names? (Choose three.)

   Choose all answers that apply.

   • printer_name#
   • 1to7number
   • yesterday's_date
   • leap$year
   • Number_of_days_between_March_and_April
   • #printer_name
   • v_fname

3. Question 3 · 1

   Which two are true about collections and RECORD types? (Choose two.)

   Choose all answers that apply.

   • A variable of RECORD type can contain fields of another RECORD type or any collection type.
   • Only associative arrays and nested tables can have elements of RECORD type.
   • All collections and RECORD types can be defined in PL/SQL blocks, packages, or at the schema level.
   • Collections and RECORD types are always dense.
   • All collections and RECORD types can be stored in table columns.
   • VARRAYS, nested tables and each field in %ROWTYPE type variables have a default value of null.

4. Question 4 · 1

   Examine this row of data from the EMPLOYEES table: Now, examine this block of code which executes successfully: What is the value of v_commission?

   Choose one answer.

   

   

   • 5000
   • 15000
   • 2500
   • 10000

5. Question 5 · 1

   Examine this table in the SH schema: Now, examine this code: Which two changes are required to ensure that PDT_REPORT executes successfully? (Choose two.)

   Choose all answers that apply.

   

   

   • In line 1, change IN OUT mode to IN mode.
   • In line 2, change IN OUT mode to IN mode.
   • In line 3, replace CUR_PRICE with P_PDT_PRICE in the query condition.
   • In line 1, add the default parameter DEFAULT 2000.
   • In line 6, replace P_PDT_PRICE parameter name with CUR_PRICE.
   • In line 2, add the default parameter DEFAULT 2000.

6. Question 6 · 1

   User ORA41 executes these statements successfully: Now, examine this statement which is executed successfully by user ORA61 after a successful login: EXECUTE ora41.update_emp_proc(100,25000); Which two are true? (Choose two.)

   Choose all answers that apply.

   

   • The salary will be changed for employee 100 in the EMPLOYEES table owned by ORA41.
   • No update happens even though the procedure executes successfully.
   • The salary will be changed for employee 100 in the EMPLOYEES table owned by ORA61.
   • The UPDATE privilege on ORA41.EMPLOYEES is not inherited by ORA61 through the procedure.
   • ORA61 will have been granted the UPDATE privilege explicitly on ORA41.EMPLOYEES before executing the statement.

7. Question 7 · 1

   Which two are true about exception handling? (Choose two.)

   Choose all answers that apply.

   • Internally defined exceptions can be handled only by the OTHERS exception handler.
   • All declared exceptions are raised implicitly by the runtime system.
   • User-defined exceptions can be defined in the declarative part of any PL/SQL anonymous block, subprogram, or package.
   • Only predefined exceptions and user-defined exceptions can have a user-declared name associated with them.
   • Predefined exceptions are globally declared in the standard package.

8. Question 8 · 1

   Examine these statements: Which is true?

   Choose one answer.

   

   • It will result in a compilation error for protected_proc because calling_proc does not exist.
   • It will result in a compilation error for protected_proc because calling_proc must be prefixed with the schema name.
   • It will result in a successful compilation because objects referenced in an ACCESSIBLE BY clause are not checked at compile time.
   • With adequate privileges, PROTECTED_PROC procedure can be called by other programs apart from CALLING_PROC.

9. Question 9 · 1

   Sequence S and table PRODUCTS exist in your schema. Examine the table description: Now, examine this block of code: Which two lines each result in a compilation error? (Choose two.)

   Choose all answers that apply.

   

   

   • line 1
   • line 6
   • line 8
   • line 2
   • line 3
   • line 7

10. Question 10 · 1

    Which three are true about functions and procedures? (Choose three.)

    Choose all answers that apply.

    • The ACCESSIBLE BY clause can be used only for procedures.
    • In a function, every execution path must lead to a RETURN statement.
    • Both can have only constants as actual parameters for IN mode parameters.
    • Both can be invoked from within SQL statements.
    • In a procedure the RETURN statement cannot specify an expression.
    • In a function every RETURN statement must specify an expression.

11. Question 11 · 1

    For which three SYSTEM EVENTS can triggers be created? (Choose three.)

    Choose all answers that apply.

    • DDL
    • AFTER AUDIT
    • BEFORE ANALYZE
    • SHUTDOWN
    • SERVERERROR
    • STARTUP
    • BEFORE GRANT

12. Question 12 · 1

    Which three statements are true about Implicit Cursor and Explicit Cursor? (Choose three.)

    Choose all answers that apply.

    • Implicit cursor returns only one record.
    • Explicit cursor can return more than one record.
    • %Isopen, %Rowtype, %Notfound, and %Found are the attributes of Explicit Cursor.
    • %Isopen, %Rowcount, %Notfound, and %Found are the attributes of Explicit Cursor.
    • %Isopen, %Type, %Notfound, and %Found are the attributes of Explicit Cursor.
    • %Isopen is always false in Explicit Cursor.
    • %Isopen is always false in Implicit Cursor.

13. Question 13 · 1

    Which three are true about DDL triggers? (Choose three.)

    Choose all answers that apply.

    • They cannot include the WHEN clause.
    • They must be created in an enabled state.
    • They can be fired when a table is truncated.
    • They fire only when a DDL statement is executed by the owner of the trigger.
    • They can be fired either before or after a DDL statement executes.
    • They can be fired when a privilege is granted to a user.
    • They must be created in a disabled state.

14. Question 14 · 1

    Which three are true about PL/SQL subprograms? (Choose three.)

    Choose all answers that apply.

    • Results of a subprogram can be cached in the SGA such that sessions connected to the same instance can reuse these results when available.
    • Users granted execute privilege on a procedure compiled with definer's rights require grants to access objects belonging to the definer that are referenced in the procedure.
    • Subprograms are cached by default and shared among users, thereby reducing memory requirements.
    • Reuse of parsed PL/SQL code from the shared SQL area reduces parsing overhead.
    • A subprogram's session state is retained even if any of the session’s instantiated subprograms are invalidated and revalidated.
    • Host variables can be referenced inside any PL/SQL subprogram.
    • A PL/SQL procedure can invoke an external code block written in a different programming language.

15. Question 15 · 1

    Which three PL/SQL-only data types can be used in queries and native dynamic SQL issued from PL/SQL in the server? (Choose three.)

    Choose all answers that apply.

    • a record declared in an anonymous block
    • a record declared in a procedure
    • an associative array indexed by PLS_INTEGER
    • a record declared in a package specification
    • a predefined PL/SQL-only data type like BOOLEAN
    • an associative array indexed by VARCHAR2

Paid access to all 45 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.