I85PREP
⌘ K
Oracle 1Z0-183 study materials
Oracle 1Z0-183 study resources

Oracle 1Z0-183 practice questions

One question at a time. Build your Oracle 1Z0-183 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Declaring PL/SQL Variables and Writing Executable Statements

What does the %TYPE attribute do when it is used in a variable declaration?

  1. It copies the current value of the referenced column into the variable at compile time.
  2. It allows the variable to accept any data type, decided when the block runs.
  3. It converts the declared variable into a formal parameter of the enclosing subprogram.
  4. It anchors the variable's data type to that of a column or another variable. Correct answer.

Explanation

%TYPE declares an item using the data type of a referenced column or another declared variable, so the declaration stays in step with the database definition. It supplies only the data type; it never supplies a value. The declaration is resolved when the block is compiled.

Question 2Declaring PL/SQL Variables and Writing Executable Statements

Which declaration creates a record variable whose fields match all columns of the employees table?

  1. emp_rec ROWTYPE employees;
  2. emp_rec employees.ROWTYPE;
  3. emp_rec employees%TYPE;
  4. emp_rec employees%ROWTYPE; Correct answer.

Explanation

The %ROWTYPE attribute, written after the table name, declares a record with one field for every column of that table, each field taking the column's data type. %TYPE declares a single scalar anchored to one item. The percent sign precedes the attribute name in both forms.

Question 3Declaring PL/SQL Variables and Writing Executable Statements

Which statement about a CONSTANT declaration is correct?

  1. A CONSTANT declaration may omit the initial value when the data type is NUMBER.
  2. A CONSTANT must be declared in a package specification before it can be used.
  3. A CONSTANT must be initialized where it is declared and cannot be assigned again. Correct answer.
  4. A CONSTANT may be reassigned inside the same block after its first use.

Explanation

A constant is a variable whose value cannot change after it is set, so PL/SQL requires an initial value in the declaration itself and rejects any later assignment. Constants may be declared in any declarative section, including a block or a subprogram body.

Question 4Declaring PL/SQL Variables and Writing Executable Statements

Which declaration raises a compile-time error?

  1. v_num NUMBER NOT NULL; Correct answer.
  2. v_num NUMBER DEFAULT 0;
  3. v_num NUMBER := NULL;
  4. v_num NUMBER;

Explanation

A variable constrained with NOT NULL must be given an initial value in the same declaration, because PL/SQL initializes variables to NULL and NULL would violate the constraint. Providing an initial value of NULL explicitly is allowed for an unconstrained variable.

Question 5Declaring PL/SQL Variables and Writing Executable Statements

Which statement does nothing but is valid wherever PL/SQL requires an executable statement?

  1. CONTINUE;
  2. ;
  3. --
  4. NULL; Correct answer.

Explanation

The NULL statement is a placeable no-op that satisfies the syntactic requirement for at least one executable statement, for example in an empty IF branch. A semicolon alone is a terminator, not a statement. CONTINUE transfers control and is only legal inside a loop.

Question 6Declaring PL/SQL Variables and Writing Executable Statements

An inner block is nested inside an outer block. How is a variable declared in the outer block treated in the inner block?

  1. It is never visible inside the inner block, because scope ends at the outer block's END.
  2. It is visible inside the inner block until an inner declaration reuses the name. Correct answer.
  3. It is visible only if the inner block has a label declared before it.
  4. It is visible only if it is passed to the inner block as a parameter.

Explanation

An identifier declared in a block is visible in that block and in all blocks nested within it. If an inner block redeclares the same name, the inner declaration hides the outer one from that point onward.

Question 7Declaring PL/SQL Variables and Writing Executable Statements

How do the DEFAULT keyword and the := operator behave in a variable declaration?

  1. Both supply an initial value; DEFAULT is the keyword form of := Correct answer.
  2. DEFAULT can be used only in CONSTANT declarations.
  3. DEFAULT assigns on every reference; := assigns once at compile time.
  4. The := operator may be used only in a package body's initialization section.

Explanation

In a declaration, DEFAULT and := are interchangeable ways to give the variable its initial value. The expression is evaluated when the declaration is executed, not when the block is compiled. Either form may be used for constants and for ordinary variables.

Question 8Declaring PL/SQL Variables and Writing Executable Statements

Which of these identifiers must be enclosed in double quotation marks before it can be used as a variable name?

  1. total_sal
  2. begin Correct answer.
  3. l_amount
  4. v_total

Explanation

BEGIN is a reserved word, so it cannot be used as an ordinary PL/SQL identifier; making it a quoted identifier is the only syntactic way to use it, and even then it is discouraged. The other names are ordinary user-defined identifiers that do not collide with reserved words.

Question 9Declaring PL/SQL Variables and Writing Executable Statements

A block declares v_flag BOOLEAN and passes it to DBMS_OUTPUT.PUT_LINE(v_flag). What is the result?

  1. It prints 1 for TRUE and 0 for FALSE.
  2. It prints an empty line because BOOLEAN variables start as NULL.
  3. The block fails to compile because no PUT_LINE overload accepts BOOLEAN. Correct answer.
  4. It prints TRUE or FALSE.

Explanation

DBMS_OUTPUT.PUT_LINE is overloaded for character, numeric and date arguments, but not for BOOLEAN, and PL/SQL has no implicit conversion from BOOLEAN to those types. Passing a BOOLEAN therefore raises a compile-time error. The usual workaround is to convert the value with a conditional expression.

Question 10Declaring PL/SQL Variables and Writing Executable Statements

A variable is declared as v_num NUMBER; and no value is assigned before it is used in an arithmetic expression. What is its value?

  1. NULL Correct answer.
  2. 0
  3. The declaration raises an error, because no initial value was given
  4. The value left in memory by the previous block

Explanation

Unless a declaration supplies an initial value, PL/SQL initializes the variable to NULL. Arithmetic involving NULL yields NULL, so the expression silently evaluates to NULL rather than 0. An initial value is mandatory only for constants and for NOT NULL variables.

Question 11Declaring PL/SQL Variables and Writing Executable Statements

What happens to an ampersand substitution variable such as &dept_id inside a PL/SQL block run from SQL*Plus?

  1. It is resolved by the Oracle server each time the block executes.
  2. It is a bind variable whose value is set with the EXECUTE command.
  3. SQL*Plus substitutes the entered text before the block reaches the server. Correct answer.
  4. It can be referenced only in the declarative section of a package.

Explanation

Substitution variables are a SQL*Plus client feature: the tool prompts for or reads a value and performs a text replacement before sending the block to the server. The server never sees the ampersand reference. A bind variable, by contrast, is a placeholder resolved at runtime.

Question 12Declaring PL/SQL Variables and Writing Executable Statements

Which syntax refers to a host (bind) variable from inside a PL/SQL block?

  1. v_sal:host
  2. &&v_sal
  3. :v_sal Correct answer.
  4. v_sal

Explanation

A host variable is referenced in PL/SQL by prefixing its name with a colon, as in :v_sal. The colon tells the compiler that the identifier is defined in the host environment rather than in the block. Substitution variables use a different prefix entirely.

Question 13Declaring PL/SQL Variables and Writing Executable Statements

Which statement describes the PLS_INTEGER data type?

  1. It is a database column data type as well as a PL/SQL type.
  2. It performs arithmetic with machine integers and is often faster than NUMBER. Correct answer.
  3. It can hold more digits of precision than NUMBER.
  4. It supports up to 38 digits of decimal precision.

Explanation

PLS_INTEGER stores signed integers in the range supported by the hardware and performs arithmetic using machine operations, which usually makes it faster than NUMBER for integer work. It cannot be used as a column data type in a table. NUMBER, not PLS_INTEGER, offers up to 38 digits of precision.

Question 14Declaring PL/SQL Variables and Writing Executable Statements

Which declaration raises a compile-time error inside a PL/SQL block?

  1. v_city VARCHAR2; Correct answer.
  2. v_city CHAR(20);
  3. v_city VARCHAR2(20);
  4. v_city CLOB;

Explanation

In PL/SQL a VARCHAR2 variable declaration requires a maximum length, so the size specification may not be omitted. In SQL, by contrast, a VARCHAR2 column may be declared without a length. Character and large-object types are declared as shown.

Question 15Declaring PL/SQL Variables and Writing Executable Statements

Which statement correctly retrieves a single row from the employees table into previously declared variables?

  1. SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = 7369; Correct answer.
  2. FETCH ename, sal INTO v_ename, v_sal;
  3. SELECT ename, sal FROM emp WHERE empno = 7369;
  4. EXECUTE SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = 7369;

Explanation

In PL/SQL a SELECT that returns exactly one row must include an INTO clause placing the selected columns into variables before the FROM clause. A SELECT without INTO is not legal as a standalone PL/SQL statement. FETCH belongs to an explicit cursor, not to a standalone query.

Question 16Declaring PL/SQL Variables and Writing Executable Statements

An inner block declares a variable named v_flag, and the outer block has already declared a variable with the same name. How can the inner block refer to the outer v_flag?

  1. By redeclaring it with the %TYPE attribute
  2. It cannot be done, because the outer variable is hidden permanently.
  3. By writing schema.v_flag
  4. By qualifying the name with the label of the outer block. Correct answer.

Explanation

When an inner declaration hides an outer identifier of the same name, the outer item remains reachable by prefixing its name with the outer block's label, as in outer_block.v_flag. Labels therefore serve both as documentation and as a way to resolve shadowing.

Question 17Declaring PL/SQL Variables and Writing Executable Statements

A block declares a local variable named sal and then runs SELECT sal INTO v_result FROM emp WHERE empno = 7369; Which sal does the query read?

  1. The local variable, because PL/SQL identifiers shadow column names everywhere.
  2. The local variable, but only when it is declared with a NOT NULL constraint.
  3. The emp.sal column, because columns take precedence in SQL statements. Correct answer.
  4. The statement fails to compile because the reference is ambiguous.

Explanation

Inside a SQL statement embedded in PL/SQL, an identifier that matches both a column and a local variable resolves to the column. To make the statement use the PL/SQL variable, the variable must be qualified with the block label. This precedence rule is the reason many shops prefix local variables.

Question 18Declaring PL/SQL Variables and Writing Executable Statements

Which declaration causes a compile-time error?

  1. c_rate CONSTANT NUMBER(3,2) DEFAULT 0.08;
  2. c_rate CONSTANT NUMBER(3,2); Correct answer.
  3. v_rate NUMBER(3,2) NOT NULL DEFAULT 0.08;
  4. v_rate NUMBER(3,2) DEFAULT 0.08;

Explanation

A constant must receive its value in the declaration, so omitting the initializer is a compile-time error regardless of the data type. DEFAULT and := are both acceptable initializers, and a NOT NULL variable is likewise satisfied by an initializer.

Question 19Declaring PL/SQL Variables and Writing Executable Statements

A block declares v_start DATE := SYSDATE; and the block is executed twice, ten minutes apart, in the same session. What is the value of v_start during the second execution?

  1. NULL, because variables are re-created empty on each execution
  2. The SYSDATE at the moment the second execution begins Correct answer.
  3. The SYSDATE captured during the first execution
  4. A compile-time constant recorded when the block was stored

Explanation

Variable declarations are executed each time the block runs, and the initializing expression is evaluated at that moment. Because SYSDATE is a function call, it returns the current date and time of the second run. Values persist across executions only for variables declared at package level, not for block locals.

Question 20Declaring PL/SQL Variables and Writing Executable Statements

A variable v_flag BOOLEAN is declared in a PL/SQL block. In which context may it be used?

  1. As a value stored into a VARCHAR2 column after implicit conversion
  2. As a column value in an INSERT statement
  3. As an actual argument to a subprogram whose formal parameter is BOOLEAN Correct answer.
  4. In the WHERE clause of a SQL statement

Explanation

BOOLEAN is a PL/SQL-only data type: it is valid in procedural code and can be passed to and returned from subprograms that declare BOOLEAN parameters. It has no equivalent in SQL, so it cannot appear in SQL statements and is never implicitly converted to character data.

Question 21Interacting with the Oracle Database Server: SQL Statements in PL/SQL

An UPDATE statement written directly in a PL/SQL block changes four rows. What does SQL%ROWCOUNT return immediately after that statement?

  1. NULL, because the attribute applies only to explicit cursors
  2. 4, the number of rows the UPDATE affected Correct answer.
  3. 0, because the attribute is reset when the statement finishes
  4. 1, because the attribute tracks only the latest row touched

Explanation

SQL%ROWCOUNT is an implicit cursor attribute that reports how many rows the most recently executed DML statement (or SELECT INTO) processed. After the four-row UPDATE it therefore holds 4.

Question 22Interacting with the Oracle Database Server: SQL Statements in PL/SQL

A SELECT INTO statement in a PL/SQL block finds no rows matching its WHERE clause. Which predefined exception is raised?

  1. INVALID_CURSOR
  2. NO_DATA_FOUND Correct answer.
  3. CURSOR_ALREADY_OPEN
  4. TOO_MANY_ROWS

Explanation

A SELECT INTO that returns zero rows raises NO_DATA_FOUND, while one that returns more than one row raises TOO_MANY_ROWS. Handlers must account for both whenever a query is not guaranteed to return exactly one row.

Question 23Interacting with the Oracle Database Server: SQL Statements in PL/SQL

A dynamic SELECT statement returns exactly one row, and its column values must end up in scalar PL/SQL variables. Which EXECUTE IMMEDIATE clause carries those values out of the dynamic statement?

  1. INTO Correct answer.
  2. RETURNING INTO
  3. USING
  4. BULK COLLECT INTO

Explanation

The INTO clause of EXECUTE IMMEDIATE receives the select-list values of a single-row dynamic query, mirroring SELECT INTO in static SQL. USING supplies input binds, and BULK COLLECT INTO is used only when several rows must be retrieved.

Question 24Interacting with the Oracle Database Server: SQL Statements in PL/SQL

A SELECT INTO statement inside a PL/SQL block returns three rows. What happens at run time?

  1. The block continues with SQL%ROWCOUNT set to 3.
  2. The first row is assigned and the other two are discarded.
  3. NO_DATA_FOUND is raised.
  4. TOO_MANY_ROWS is raised. Correct answer.

Explanation

SELECT INTO requires exactly one row; more than one row raises the predefined exception TOO_MANY_ROWS and no value is assigned. A cursor or BULK COLLECT must be used when several rows are expected.

Question 25Interacting with the Oracle Database Server: SQL Statements in PL/SQL

Which statement about the text string passed to EXECUTE IMMEDIATE is true?

  1. It may hold several SQL statements separated by semicolons.
  2. It may refer to PL/SQL variables by name without any bind clause.
  3. It must end with a semicolon, just as static SQL inside a block does.
  4. It may be a DDL statement such as CREATE TABLE. Correct answer.

Explanation

EXECUTE IMMEDIATE accepts exactly one SQL statement, and that statement may be DDL or DML, or one anonymous PL/SQL block. A SQL statement in the string is not terminated with a semicolon, and PL/SQL variables must be passed with USING.

Question 26Interacting with the Oracle Database Server: SQL Statements in PL/SQL

The variable sql_text holds 'SELECT * FROM employees WHERE department_id = :1' and dept holds a department ID. The variable cv is declared as SYS_REFCURSOR. Which statement opens cv for that query?

  1. EXECUTE IMMEDIATE sql_text INTO cv USING dept;
  2. cv := sql_text USING dept;
  3. OPEN cv FOR sql_text USING dept; Correct answer.
  4. OPEN cv FOR sql_text INTO dept;

Explanation

OPEN FOR associates a cursor variable with a dynamically supplied query string, and USING supplies the value for each placeholder. The rows are then retrieved with FETCH and the cursor released with CLOSE.

Question 27Interacting with the Oracle Database Server: SQL Statements in PL/SQL

A dynamically built query returns an unknown number of rows that must be processed one at a time in a loop. Which approach fits native dynamic SQL?

  1. A static cursor declared with a variable in its WHERE clause
  2. EXECUTE IMMEDIATE with an INTO clause executed once per loop iteration
  3. OPEN FOR with a cursor variable, then FETCH and CLOSE Correct answer.
  4. EXECUTE IMMEDIATE with a RETURNING INTO clause inside the loop

Explanation

Native dynamic SQL processes multi-row queries through a cursor variable: OPEN FOR parses the string and attaches the result set, FETCH retrieves rows one at a time, and CLOSE releases the cursor.

Question 28Interacting with the Oracle Database Server: SQL Statements in PL/SQL

An EXECUTE IMMEDIATE call runs a DML statement that both takes an input bind and returns modified column values. In what order must the clauses appear?

  1. Only INTO is allowed with DML; RETURNING is not supported
  2. USING first, then RETURNING INTO Correct answer.
  3. RETURNING INTO first, then USING
  4. Either order works because the compiler reorders them

Explanation

The syntax of EXECUTE IMMEDIATE places USING before RETURNING INTO: input binds are supplied first, then the returning clause names the variables that receive the modified values.

Question 29Interacting with the Oracle Database Server: SQL Statements in PL/SQL

A dynamic query held in a string returns an unknown number of rows that must all be placed in a collection. Which EXECUTE IMMEDIATE clause does this?

  1. USING, which binds the collection to the query's placeholders
  2. BULK COLLECT INTO, which fetches all rows into the collection at once Correct answer.
  3. INTO, which fetches only the first row and discards the rest
  4. RETURNING INTO, which sends rows back to the caller

Explanation

BULK COLLECT INTO on EXECUTE IMMEDIATE retrieves every row of the dynamic query into one or more collections in a single fetch operation. A plain INTO would require the query to return exactly one row.

Question 30Interacting with the Oracle Database Server: SQL Statements in PL/SQL

Ten element values sit in the collection ids and must be inserted by dynamic SQL. Which construct sends the whole set with the fewest context switches?

  1. A BULK COLLECT INTO clause on the EXECUTE IMMEDIATE that performs the inserts
  2. A FOR loop that runs EXECUTE IMMEDIATE once per element, each time with its own USING clause
  3. One EXECUTE IMMEDIATE whose USING clause passes the whole collection ids
  4. FORALL i IN 1..10 EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:1)' USING ids(i); Correct answer.

Explanation

FORALL is a declarative statement that binds the collection as a whole and sends all ten DML executions to the SQL engine in a single round trip. A row-by-row FOR loop with EXECUTE IMMEDIATE works but pays one context switch per element.

Question 31Interacting with the Oracle Database Server: SQL Statements in PL/SQL

How does Oracle treat a static SQL statement in a PL/SQL unit differently from a dynamic one?

  1. Static SQL records no dependency on the objects it references.
  2. Static SQL must be issued through DBMS_SQL, whereas dynamic SQL uses EXECUTE IMMEDIATE.
  3. Static SQL is compiled and its dependencies recorded; dynamic SQL is parsed at run time. Correct answer.
  4. Static SQL is parsed only when the block runs, and dynamic SQL is parsed when the block is compiled.

Explanation

Static SQL is validated when the PL/SQL unit is compiled, and the referenced objects become recorded dependencies, which is why invalidating a table can invalidate the unit. Dynamic SQL text is unknown at compile time and is parsed only when the statement executes.

Question 32Interacting with the Oracle Database Server: SQL Statements in PL/SQL

A procedure must create a table. Which statement is true?

  1. CREATE TABLE can be coded as static SQL because PL/SQL supports all SQL statements.
  2. The CREATE TABLE must run through EXECUTE IMMEDIATE and stays uncommitted until you commit.
  3. CREATE TABLE can be coded as static SQL provided the table does not exist yet.
  4. The CREATE TABLE must run through EXECUTE IMMEDIATE and it issues an implicit commit. Correct answer.

Explanation

Static SQL in PL/SQL covers only DML and transaction control, so DDL must be issued dynamically with EXECUTE IMMEDIATE or DBMS_SQL. Oracle performs an implicit commit before and after DDL, so the transaction cannot be rolled back past it.

Question 33Interacting with the Oracle Database Server: SQL Statements in PL/SQL

Why is it better to pass values to a dynamic statement through USING rather than concatenating them into the string?

  1. Concatenation is not permitted when the value is a number or a date.
  2. USING lets a single dynamic string hold more than one SQL statement.
  3. Binds allow shared cursors and prevent SQL injection. Correct answer.
  4. USING removes the need for an INTO clause when the statement is a query.

Explanation

A statement whose values arrive as binds can be reused from the shared pool for many executions and cannot have malicious text injected into its structure. Concatenated literals produce new statement text for every distinct value and open the door to SQL injection.

Question 34Interacting with the Oracle Database Server: SQL Statements in PL/SQL

A procedure runs: EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.1 WHERE department_id = :1' USING dept; Which expression reports how many rows that dynamic UPDATE changed?

  1. SQL%BULK_ROWCOUNT(1)
  2. SQL%FOUND
  3. DBMS_SQL.LAST_ROW_COUNT, because the statement was dynamic
  4. SQL%ROWCOUNT Correct answer.

Explanation

Implicit cursor attributes describe the most recent SQL statement executed in the session, and that includes a statement run with EXECUTE IMMEDIATE. After the UPDATE, SQL%ROWCOUNT therefore holds the number of rows changed.

Question 35Interacting with the Oracle Database Server: SQL Statements in PL/SQL

Which task requires DBMS_SQL rather than native dynamic SQL?

  1. Executing a dynamic statement many times with different bind values
  2. Processing a query whose columns and binds are unknown until run time Correct answer.
  3. Opening a cursor variable for a query that returns an unknown number of rows
  4. Executing a DDL statement whose text is built at run time

Explanation

Native dynamic SQL requires the number of placeholders and the receiving variables to be fixed in the code, whereas DBMS_SQL can parse, describe columns, and bind or fetch in a program-driven loop. That flexibility is needed when the shape of the statement is unknown until execution.

Question 36Interacting with the Oracle Database Server: SQL Statements in PL/SQL

EXECUTE IMMEDIATE runs an UPDATE that changes several rows, and the new salary of every changed row must be captured. Which EXECUTE IMMEDIATE clause does this?

  1. INTO salaries
  2. RETURNING INTO salaries
  3. RETURNING BULK COLLECT INTO salaries Correct answer.
  4. BULK COLLECT INTO salaries

Explanation

RETURNING INTO expects at most one returned row, so a multi-row DML needs RETURNING BULK COLLECT INTO, which places every returned value in a collection. The dynamic string must also contain the statement's own RETURNING clause naming the returned columns.

Question 37Writing Control Structures

Which clause of an IF statement lets you test an additional condition when the initial condition evaluates to FALSE?

  1. ELSE
  2. ELSIF Correct answer.
  3. LOOP
  4. THEN

Explanation

An IF statement evaluates its first condition, and each ELSIF clause supplies a further condition that is tested only after all earlier conditions proved FALSE. ELSE has no condition of its own, and THEN and END IF are merely delimiters of the statement.

Question 38Writing Control Structures

How many ELSE clauses may a single IF statement contain?

  1. One for each ELSIF clause
  2. At most one Correct answer.
  3. Exactly two
  4. Unlimited

Explanation

A single IF statement may contain any number of ELSIF clauses, but at most one ELSE clause, and that ELSE clause is optional. When present, ELSE must appear last, immediately before END IF.

Question 39Writing Control Structures

Which loop type repeats its body indefinitely until an EXIT or EXIT WHEN statement is executed?

  1. WHILE loop
  2. Basic LOOP Correct answer.
  3. Cursor FOR loop
  4. Numeric FOR loop

Explanation

A basic LOOP has no terminating condition of its own; the body repeats until control leaves it through EXIT or EXIT WHEN. WHILE and FOR loops stop on their own condition or on reaching the end of their fixed range.

Question 40Writing Control Structures

In a WHILE loop, when is the controlling condition evaluated?

  1. Only when the loop body executes an EXIT statement
  2. After the loop body executes, so the body always runs at least once
  3. Only once, before the loop begins
  4. Before each iteration, including the first Correct answer.

Explanation

A WHILE loop tests its condition before every iteration, including the very first one. If the condition is FALSE on entry the body never executes, so a WHILE loop can run zero times.

Question 41Writing Control Structures

Which statement about a simple CASE statement is true?

  1. It compares the selector to each WHEN value using equality Correct answer.
  2. Its WHEN clauses may hold any Boolean expression, not just values
  3. It must contain an ELSE clause
  4. It evaluates all WHEN operands before choosing a match

Explanation

A simple CASE statement evaluates a selector expression and compares it with each WHEN operand using the equality operator, running the first branch that matches. The WHEN operands in a simple CASE are values, and the ELSE clause is optional.

Question 42Writing Control Structures

You replace a chain of ELSIF clauses with IF statements nested inside an ELSE branch. What must you add?

  1. No additional END IF is needed
  2. A label for the outer IF statement
  3. An additional END IF only when the nested IF contains ELSIF
  4. An additional END IF for each nested IF Correct answer.

Explanation

Every IF statement is a separate construct and must be closed by its own END IF, no matter where it is nested. An ELSIF chain avoids the extra terminators because all the branches belong to one single IF statement.

Question 43Writing Control Structures

A CASE statement contains several WHEN clauses but no ELSE clause, and at run time no WHEN matches. What happens?

  1. Control passes to the statement after END CASE
  2. CASE_NOT_FOUND is raised Correct answer.
  3. The selector value is returned as NULL
  4. A compilation error occurs

Explanation

When a CASE statement finds no matching WHEN and has no ELSE clause, Oracle raises the predefined exception CASE_NOT_FOUND. This is a run-time condition; the block compiles successfully because ELSE is optional.

Question 44Writing Control Structures

Consider this block: DECLARE n NUMBER := 3; BEGIN FOR i IN 1..n LOOP n := 10; END LOOP; END; How many times does the loop body execute?

  1. Zero times
  2. Ten times
  3. Thirteen times
  4. Three times Correct answer.

Explanation

The lower and upper bounds of a numeric FOR loop are evaluated once, when the loop is entered, and the iteration count is fixed at that moment. Assigning a new value to n inside the body therefore has no effect on the number of iterations.

Question 45Writing Control Structures

What sequence of index values does the loop header FOR i IN REVERSE 1..3 LOOP produce?

  1. 3, 2, 1 Correct answer.
  2. No values, because the loop body never executes
  3. 0, 1, 2
  4. 1, 2, 3

Explanation

The REVERSE keyword makes a numeric FOR loop count downward, starting from the upper bound and ending at the lower bound. With REVERSE 1..3 the index therefore takes the values 3, 2, and 1.

Question 46Writing Control Structures

Which action is permitted on the index variable of a numeric FOR loop inside the loop body?

  1. Assigning it a new numeric value
  2. Referencing it in an expression Correct answer.
  3. Passing it as an IN OUT parameter to a procedure
  4. Declaring a variable with the same name in the enclosing block so the loop modifies that variable

Explanation

The index of a numeric FOR loop is an implicitly declared local constant whose scope is limited to the loop. It can be read in expressions, but it cannot be assigned a new value or used as an OUT or IN OUT actual parameter.

Question 47Writing Control Structures

What does the CONTINUE statement do when it executes inside a loop body?

  1. Restarts the loop with the index reset to its initial value
  2. Exits the current block and passes control to its exception section
  3. Skips the rest of the current iteration and starts the next Correct answer.
  4. Exits the enclosing loop immediately

Explanation

CONTINUE transfers control to the next iteration of the loop, abandoning any statements that follow it in the current iteration. It does not terminate the loop, and the loop's index continues its normal progression.

Question 48Writing Control Structures

Two loops are nested, the outer one labeled outer_loop. Inside the inner loop, the statement EXIT outer_loop; executes. What is the result?

  1. The entire procedure terminates immediately
  2. Only the innermost loop terminates
  3. Only the inner loop terminates and control resumes in the outer loop body
  4. Both loops terminate; control resumes after the outer END LOOP Correct answer.

Explanation

EXIT with a label terminates the labeled loop and every loop nested inside it, and control resumes at the statement following that labeled loop's END LOOP. An unlabeled EXIT, by contrast, leaves only the innermost enclosing loop.

Question 49Writing Control Structures

Which GOTO target is permitted in PL/SQL?

  1. A statement in the exception section, from the executable section of the same block
  2. A statement inside a loop body, from outside that loop
  3. A statement in an enclosing block, from within a nested block Correct answer.
  4. A statement inside an IF statement, from outside that IF

Explanation

A GOTO may branch only to a label in the same block or in an enclosing block, provided the target is an executable statement. Branching into a nested structure such as an IF statement, a loop body, or an exception handler is prohibited.

Question 50Writing Control Structures

Two nested numeric FOR loops both declare an index named i, and the outer loop carries the label outer_loop. How can the inner loop body reference the outer loop's index?

  1. outer_loop.i Correct answer.
  2. It cannot be referenced because the inner index hides it
  3. i, because the outer index takes precedence
  4. outer_loop(i)

Explanation

When an inner loop reuses an index name, the outer index becomes hidden and a bare reference to i resolves to the inner index. Qualifying the name with the outer loop's label, as in outer_loop.i, still reaches the outer index.