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.
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.
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.
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?
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.)
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.)
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?
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?
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.