Oracle PL/SQL Practice Quiz: Test Yourself!

The declaration foo_text number(10) is incorrect because the name suggests a text-based variable while the datatype is numeric. Oracle does not forbid mismatched naming, but it is considered logically incorrect and misleading. A clear naming–datatype match improves code readability, prevents confusion, and avoids maintenance errors.

The declaration foo_text number(10) is incorrect because the name suggests a text-based variable while the datatype is numeric. Oracle does not forbid mismatched naming, but it is considered logically incorrect and misleading. A clear naming–datatype match improves code readability, prevents confusion, and avoids maintenance errors.

VARCHAR1 is invalid because Oracle supports VARCHAR2 for variable-length character data, not VARCHAR1. CHAR, VARCHAR2, INTEGER, and NUMBER are valid datatypes. Oracle historically reserved VARCHAR for future use, but VARCHAR1 has never existed. Therefore, it is the only incorrect datatype in the list.

The correct cursor-processing sequence is OPEN, FETCH, CLOSE. Opening initializes the pointer and allocates context memory. Fetching retrieves rows one at a time into declared variables. Closing releases the cursor resources. This is the only logically and syntactically correct three-step sequence among the choices.

The missing keyword is CLOSE because after opening and fetching from an explicit cursor, the final required step is always to close it. Closing releases memory, locks, and cursor execution context. Failing to close explicit cursors can lead to resource leaks and ORA-1000 “maximum open cursors exceeded.”

The identifiers p_foo_text and p_foo_number are parameters. Their modes specify how data flows: IN for read-only input and IN OUT for bidirectional modification. They are not variables or signatures by themselves; rather, they are the arguments provided by callers when invoking the procedure.

The package specification is valid because Oracle supports overloading procedures, allowing multiple subprograms with the same name as long as their parameter lists differ in number, order, or datatype. Overloading improves API usability, letting developers write intuitive, consistent procedure names with varied inputs.

The correct attribute is %TYPE, which copies the datatype of a specific table column. This ensures perfect datatype matching and prevents errors during schema changes. Using %TYPE improves maintainability because changing the column automatically updates all PL/SQL variables referencing it.

The correct attribute is %ROWTYPE, which creates a composite record matching an entire table row. It automatically includes all columns with their exact datatypes. This reduces manual variable declarations and keeps PL/SQL synchronized with table structure, even after schema modifications.

The correct keyword is ELSIF, not ELSE IF. ELSIF is Oracle’s official syntax for additional conditional branches within an IF structure. ELSE IF would terminate the original IF block and begin a separate IF, producing compilation errors. ELSIF preserves a single structured decision chain.

The missing keyword is INTO, required when a SELECT retrieves values into PL/SQL variables. SELECT…INTO enforces a single-row result, raising exceptions if zero or multiple rows return. It is mandatory in PL/SQL blocks because they cannot process result sets without INTO.

The SQL returns employees who are analysts, earn more than 20000, and belong to either Finance or Sales. Because both DEPARTMENT and EMPLOYEE tables appear without a join condition, the query produces a Cartesian product, filtered afterward by WHERE predicates. This yields the listed matching rows.

Explicit cursors must be declared in the PL/SQL declaration section. This section precedes the executable block and is dedicated to defining variables, constants, types, exceptions, and cursors. Only after being declared can the cursor be opened and processed inside the executable section.

The correct keyword is CREATE or CREATE OR REPLACE. CREATE builds a new stored procedure, while CREATE OR REPLACE updates it if it already exists. Procedures cannot begin with DECLARE or BEGIN because they require a stored-object creation command.

The COMMIT fires 10 times because the loop executes 1000 iterations, and COMMIT executes only when MOD(i,100)=0. That occurs at 100, 200, …, 1000—exactly ten events. MOD ensures the commit is aligned to multiples of 100, reducing transaction frequency.

The operator || performs string concatenation. It joins character expressions to form a single output string. Oracle evaluates each operand, converts them to strings if necessary, and merges them from left to right. It is the standard concatenation operator in Oracle SQL.

TRUNCATE is not a DML function. It is a DDL command that instantly removes all rows from a table without logging individual row deletions. DML includes INSERT, UPDATE, DELETE, and SELECT—but TRUNCATE alters table structure behavior and cannot be rolled back.

The correct function is TO_CHAR, which converts a DATE value into a formatted character string. Oracle uses format models like ‘MM/DD/YYYY’ to represent dates as readable text. TO_CHAR is essential for display, reporting, and string-based comparisons involving date values.

LOOP … UNTIL is invalid because Oracle has no built-in REPEAT UNTIL construct. However, developers may simulate it using EXIT WHEN. WHILE loops, numeric FOR loops, and EXIT WHEN loops are valid PL/SQL constructs, but UNTIL is not recognized.

All listed attributes—%NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN—are valid cursor attributes. They supply execution-state information: whether rows were found, whether the cursor is open, and how many rows were fetched. Hence none are invalid, making “None of the above” correct.

DISTINCT is not a grouping function; it is a qualifier used inside functions like COUNT or SUM to influence whether duplicates are considered. GROUP functions include COUNT, SUM, AVG, MIN, MAX. DISTINCT modifies, not performs, aggregation.

The correct word is CASE. The CASE expression enables conditional logic inside SQL queries, functioning similarly to IF-THEN-ELSE in PL/SQL. It evaluates conditions sequentially and returns corresponding results, offering powerful in-line logic without procedural code.

All listed exceptions—NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, and OTHERS—are valid PL/SQL exceptions. NO_DATA_FOUND and TOO_MANY_ROWS relate to SELECT INTO behavior, DUP_VAL_ON_INDEX relates to unique constraint violations, and OTHERS is a universal catch-all handler.

l_child_number is local to the inner block. When referenced outside the inner block, it is out of scope, causing an exception. The exception handler assigns zero and prints it. Therefore the printed and final value is 0.

The correct SQL*Plus command is SHOW ERRORS, which displays compilation errors for stored procedures, triggers, views, and functions. Other variations like DISPLAY ERRORS or SHOW MISTAKES do not exist. SHOW ERRORS is the standard diagnostic command.

A package consists of two components: specification and body. The specification lists procedures, functions, types, and declarations available to users. The body contains the executable code. This separation supports public interfaces and hidden implementation details.

Both queries perform a self-join between players and their managers but use opposite join directions. They produce identical row sets because they describe the same relationship but display column orders differently. Neither query fails; only the presentation changes.

DROP TABLE permanently removes both table structure and data without the possibility of rollback after commit-level finalization. Unlike DELETE, it does not log row-by-row changes. This makes recovery impossible unless the table is restored from backup.

A private synonym removes schema qualification only for the user who created it. Without the PUBLIC keyword, other users still require schema prefixes or their own synonyms. Therefore, the synonym benefits only the creator.

UPDATE is not an SQLPlus command; it is a SQL DML statement. SQLPlus commands include DESCRIBE, LIST, and CHANGE. SQL*Plus commands affect the client environment, not database data, distinguishing them from SQL statements like UPDATE.

An outer join allows rows from one table to appear even when matching rows in the related table do not exist. This is necessary when NULLs represent missing region values but employee rows must still display.

Two conditions raise exceptions: mismatched SELECT INTO datatypes and SELECT INTO returning more than one row. Both cause runtime errors. GROUP BY and absence of WHERE clauses do not inherently trigger exceptions in PL/SQL.

TRUNCATE TABLE removes all data instantly while preserving the table structure. It is non-recoverable and faster than DELETE. DROP TABLE removes the structure entirely, so it is not appropriate when the table must be reused.

A new user cannot connect to the database until granted CREATE SESSION. This privilege authorizes establishing a connection. Tablespaces and profiles do not provide connection rights. CREATE SESSION is the minimum requirement for login.

ALL_OBJECTS shows all objects accessible to the current user, making it appropriate for determining how many tables the user can access. USER_OBJECTS shows only owned objects; DBA_* requires special privileges.

User-defined exceptions are raised in the executable section using RAISE. The declarative section only declares them, and the exception section handles them. Only executable logic can explicitly trigger them.

V_VALUE = 250 satisfies the first condition (>100), but ELSIF conditions are skipped because the first true condition prevents subsequent branch evaluation. Therefore 2 × 250 is not used; instead, the third condition (

Updating the graduation date to ‘15-AUG-2000’ triggers a foreign key error because that date does not exist in the GRADDATE parent table. Oracle enforces referential integrity, so invalid parent key references are rejected.

The SELECT fails when more than one employee is named SCOTT because SELECT INTO requires exactly one row. Multiple matches raise TOO_MANY_ROWS. Lowercase names, NULL, or zero commission do not cause this specific failure.