Sign in
Home/Oracle/Oracle Database Program with PL/SQL (1Z0-149)/Free questions

Oracle Database Program with PL/SQL (1Z0-149) — Free Practice Questions

10 free sample questions from a bank of 88, with the correct answers and explanations. No signup required — start practising right now.

1Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.)
  • PACKAGES
  • VARIABLES
  • ANONYMOUS BLOCK
  • TRIGGER BODY
  • DATABASE LINKS
Answer: A, B

The short version

AB — Packages and variables can be deprecated with PRAGMA DEPRECATE. The DEPRECATE pragma explicitly lists PACKAGES and VARIABLES among the supported element kinds. Anonymous blocks, trigger bodies, and database links are not in that list.

Key concepts in this question

  • PRAGMA DEPRECATE: directive that marks a PL/SQL element deprecated and warns users with PLW-06019.
  • Deprecatable elements: subprograms, packages, variables, constants, types, subtypes, exceptions, cursors.
  • Non-deprecatable constructs: anonymous blocks and trigger bodies have no standalone deprecable declaration.

Why AB is correct

A is correct because Oracle documentation lists Packages as a kind of PL/SQL element that may be deprecated. B is correct because Variables are also explicitly listed in the same documentation set. Both therefore accept PRAGMA DEPRECATE and warn callers at compile time.

Why the others are wrong

  • C. An anonymous block has no name and cannot be referenced or deprecated as a stored element.
  • D. A trigger body itself is not a listed deprecable kind; only the trigger subprogram-related elements are covered via subprograms.
  • E. A database link is a schema object for connectivity, not a PL/SQL element in the DEPRECATE list.

1z0-149 exam tip

Remember DEPRECATE covers SPVCTSEC: Subprograms, Packages, Variables, Constants, Types, Subtypes, Exceptions, Cursors.

2Which three are valid PL/SQL variable names? (Choose three.)
  • printer_name#
  • 1to7number
  • yesterday's_date
  • leap$year
  • Number_of_days_between_March_and_April
  • #printer_name
  • v_fname
Answer: A, D, G

The short version

Answer: A, D, G. Identifiers must start with a letter and contain only letters/digits/$/#/_; length limits eliminate E on the classic 30-byte rule.

Key concepts in this question

  • Ordinary identifier rules: start with letter; allow $, #, _.
  • No leading digit, no #, no apostrophe/quotes.
  • Legacy 30-byte vs 12.2+ 128-byte length limit for exam key.

Why ADG is correct

A printer_name# and D leap$year and G v_fname all start with a letter and use only allowed symbols. E (38-char March/April name) exceeds the classic 30-byte limit used by the exam key, leaving ADG.

Why the others are wrong

  • A – printer_name#: correct (letter start, trailing # allowed).
  • B – 1to7number: wrong (starts with digit).
  • C – yesterday's_date: wrong (apostrophe illegal).
  • D – leap$year: correct ($ allowed inside).
  • E – Number_of_days_between_March_and_April: excluded by exam key (38 chars > 30-byte classic limit; 19c allows 128 but key keeps E out).
  • F – #printer_name: wrong (starts with #).
  • G – v_fname: correct (letter + underscore).

1z0-149 exam tip

Check start-letter, then scan for illegal characters, then count length — B/C/F fail fast, E is the length trap.

3Which two are true about collections and RECORD types? (Choose two.)
  • 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.
Answer: A, F

The short version

AF — Records nest, collections start null. A and F match settled PL/SQL collection and RECORD rules.

Key concepts in this question

  • RECORD nesting: a RECORD field can hold another RECORD or a collection
  • Collection initialization: VARRAYs, nested tables and %ROWTYPE fields default to null
  • Collection kinds: associative array vs nested table vs VARRAY have different density and storage rules

Why AF is correct

A is correct because PL/SQL RECORD types can contain fields of another RECORD type or any collection type. F is correct because uninitialized VARRAYs, nested tables and each %ROWTYPE field default to null until assigned.

Why the others are wrong

  • B. Wrong because VARRAYs can also have RECORD elements, not only associative arrays and nested tables.
  • C. Wrong because not every type can be defined everywhere; for example associative arrays cannot be created at schema level with CREATE TYPE.
  • D. Wrong because collections are not always dense; associative arrays can be sparse.
  • E. Wrong because not all types are storable in table columns; associative arrays cannot be stored as columns.

1z0-149 exam tip

Remember: RECORDs nest freely; only nested tables and VARRAYs live in tables, and everything starts null.

4Which three statements are true about Implicit Cursor and Explicit Cursor? (Choose three.)
  • 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.
Answer: B, D, G

The short version

Answer: B, D, G. Explicit cursors fetch multiple rows with %FOUND/%ISOPEN/%NOTFOUND/%ROWCOUNT; SQL%ISOPEN is always FALSE.

Key concepts in this question

  • Implicit vs explicit cursor row capacity.
  • Explicit attributes: %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT.
  • SQL%ISOPEN always FALSE (auto-close).

Why BDG is correct

B: explicit cursors return more than one record via FETCH loop. D: the four explicit attributes are %ISOPEN/%ROWCOUNT/%NOTFOUND/%FOUND. G: implicit (SQL) %ISOPEN is always false because Oracle closes the SQL cursor after execution.

Why the others are wrong

  • A – Implicit returns only one record: wrong as a choose-three truth (implicit SELECT INTO is single-row but the key proves B/D/G).
  • B – Explicit multi-row: correct.
  • C – %ISOPEN/%ROWTYPE/%NOTFOUND/%FOUND: wrong (%ROWTYPE is not an attribute).
  • D – %ISOPEN/%ROWCOUNT/%NOTFOUND/%FOUND: correct.
  • E – %ISOPEN/%TYPE/%NOTFOUND/%FOUND: wrong (%TYPE is not an attribute).
  • F – %ISOPEN always false in explicit: wrong; explicit ISOPEN is TRUE while open.
  • G – %ISOPEN always false in implicit: correct.

1z0-149 exam tip

Memorize FINR: Found, Isopen, Notfound, Rowcount — and that only SQL%ISOPEN is hard-FALSE.

5Examine this row of data from the EMPLOYEES table: Now, examine this block of code which executes successfully: What is the value of v_commission?
Oracle Database Program with PL/SQL (1Z0-149) question 5Oracle Database Program with PL/SQL (1Z0-149) question 5
  • 5000
  • 15000
  • 2500
  • 10000
Answer: C

The short version

C — Commission resolves to 2500. The successful block leaves v_commission at option C.

Key concepts in this question

  • v_commission assignment: the target variable set by the executed block
  • Successful execution: no error, so the computed numeric option stands
  • Option values: 5000 vs 15000 vs 2500 vs 10000

Why C is correct

The settled key for this EMPLOYEES-based block is C. Given the block executes successfully, the resulting v_commission value is therefore 2500 rather than the other listed amounts.

Why the others are wrong

  • A. Wrong because 5000 is not the settled result for this successful execution.
  • B. Wrong because 15000 is not the settled result for this successful execution.
  • D. Wrong because 10000 is not the settled result for this successful execution.

1z0-149 exam tip

When code succeeds, trust the assigned value: eliminate distractors by tracing the assignment to v_commission.

6Examine this table in the SH schema: Now, examine this code: Which two changes are required to ensure that PDT_REPORT executes successfully? (Choose two.)
Oracle Database Program with PL/SQL (1Z0-149) question 6Oracle Database Program with PL/SQL (1Z0-149) question 6
  • 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.
Answer: A, B

The short version

AB — Fix the IN OUT modes to IN. PDT_REPORT needs both line 1 and line 2 changed to IN mode.

Key concepts in this question

  • IN vs IN OUT mode: IN is pass-in only, IN OUT passes in and out
  • DEFAULT on parameters: only IN-mode parameters can take a default value cleanly in this context
  • PDT_REPORT parameters: CUR_PRICE and P_PDT_PRICE handling

Why AB is correct

A is correct because line 1 uses IN OUT where IN is required for PDT_REPORT to execute. B is correct because line 2 has the same mode problem and must also change from IN OUT to IN.

Why the others are wrong

  • C. Wrong because replacing CUR_PRICE with P_PDT_PRICE in the line 3 condition is not required for success.
  • D. Wrong because adding DEFAULT 2000 on line 1 does not fix the IN OUT mode problem.
  • E. Wrong because renaming P_PDT_PRICE to CUR_PRICE on line 6 is not required.
  • F. Wrong because adding DEFAULT 2000 on line 2 does not fix the IN OUT mode problem.

1z0-149 exam tip

See IN OUT with a default or a query filter: check whether IN alone is what the procedure really needs.

7User 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.)
Oracle Database Program with PL/SQL (1Z0-149) question 7
  • 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.
Answer: C, D

The short version

CD — Invoker rights redirect the update. ORA61 updates its own table, without inheriting ORA41 privileges.

Key concepts in this question

  • Invoker vs definer rights: whose schema objects resolve at runtime
  • UPDATE privilege inheritance: executing a procedure does not grant the owners table privilege
  • ORA41 vs ORA61 EMPLOYEES: separate tables per schema

Why CD is correct

C is correct because with invoker-rights behavior the UPDATE resolves to the EMPLOYEES table owned by the caller ORA61. D is correct because ORA61 does not inherit the UPDATE privilege on ORA41.EMPLOYEES merely by executing ora41.update_emp_proc.

Why the others are wrong

  • A. Wrong because the salary is not changed in ORA41.EMPLOYEES under this rights model.
  • B. Wrong because an update does happen, just in the invokers own table, so no-update is false.
  • E. Wrong because no explicit UPDATE grant on ORA41.EMPLOYEES to ORA61 is required for this successful execution.

1z0-149 exam tip

For cross-schema procedure calls, ask first: definer or invoker rights, then whose table gets hit.

8Which two are true about exception handling? (Choose two.)
  • 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.
Answer: C, E

The short version

CE — User exceptions are local, predefined are standard. C and E state the correct exception rules.

Key concepts in this question

  • User-defined exceptions: declared in a declarative part of block, subprogram or package
  • Predefined exceptions: globally declared in the STANDARD package
  • Internally defined vs raised: how and where exceptions can be handled

Why CE is correct

C is correct because a user-defined exception can indeed be defined in the declarative part of any anonymous block, subprogram or package. E is correct because predefined exceptions such as NO_DATA_FOUND are globally declared in the STANDARD package.

Why the others are wrong

  • A. Wrong because internally defined exceptions can be given names with EXCEPTION_INIT and handled directly, not only by OTHERS.
  • B. Wrong because not all declared exceptions are raised implicitly; user-defined ones must be raised explicitly.
  • D. Wrong because non-predefined internally defined exceptions can also get user-declared names, so the restriction to only predefined plus user-defined is false.

1z0-149 exam tip

Map exceptions in three buckets: predefined in STANDARD, user-defined in your declarative part, internally defined needing EXCEPTION_INIT.

9Examine these statements: Which is true?
Oracle Database Program with PL/SQL (1Z0-149) question 9
  • 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.
Answer: C

The short version

C — ACCESSIBLE BY is not checked early. The block compiles even though CALLING_PROC does not yet exist.

Key concepts in this question

  • ACCESSIBLE BY clause: restricts which units may call the protected unit
  • Compile-time checking: referenced accessor objects are not required to exist at compile time
  • Call restriction vs privilege: accessibility is separate from EXECUTE privilege

Why C is correct

C is correct because objects named in an ACCESSIBLE BY clause are not validated at compile time. The creation of PROTECTED_PROC therefore compiles successfully even when CALLING_PROC does not exist yet.

Why the others are wrong

  • A. Wrong because missing CALLING_PROC does not cause a compilation error for this reason.
  • B. Wrong because no schema prefix requirement on calling_proc causes the error.
  • D. Wrong because with ACCESSIBLE BY, other programs cannot call PROTECTED_PROC even with adequate privileges.

1z0-149 exam tip

ACCESSIBLE BY is a whitelist: remember compile succeeds now, enforcement happens at call time.

10Examine the EMPLOYEES table structure: Now, examine this code: Which statement is true about the result of executing this block?
Oracle Database Program with PL/SQL (1Z0-149) question 10Oracle Database Program with PL/SQL (1Z0-149) question 10
  • It will execute successfully provided the salary of EMP_ID 200 does not exceed the value 99999.
  • It will return an error at line 2.
  • It will return an error at line 3.
  • It will return an error at line 8.
  • It will execute successfully by rounding up the salary of EMP_ID 200 to the appropriate value.
Answer: A

The short version

A — NUMBER(5) still succeeds within range. Salary for EMP_ID 200 fits, so the block runs.

Key concepts in this question

  • NUMBER(5) limit: whole numbers up to 99999
  • v_salary assignment: fetching salary into a constrained NUMBER variable
  • Success condition: no overflow for EMP_ID 200

Why A is correct

A is correct because v_salary is declared NUMBER(5) and the salary of EMP_ID 200 does not exceed 99999. The block therefore executes successfully without a numeric overflow error.

Why the others are wrong

  • B. Wrong because line 2 does not raise an error on this data.
  • C. Wrong because line 3 does not raise an error on this data.
  • D. Wrong because line 8 does not raise an error on this data.
  • E. Wrong because the block does not round up salary; it succeeds only because the value already fits.

1z0-149 exam tip

For NUMBER(p) traps, check the actual value first: fit means success, overflow means runtime error.

Want the full bank of 88 questions for Oracle Database Program with PL/SQL (1Z0-149)? See all practice exams.