Oracle PL/SQL Programming Quiz

The WHEN OTHERS exception handler is a catch-all block that handles any exceptions not explicitly caught by preceding handlers. Because it catches everything, it must be the last handler in a PL/SQL exception block.

For processing salary raises for employees meeting a grade criteria, a Stored Procedure encapsulates the business logic, a Package organizes related procedures/functions, a Cursor iterates through employee rows, and a View can filter employees by grade C2 and above. Stored Functions return values (not ideal for DML operations), Triggers fire automatically (not for scheduled processing). The correct answers are A, B, C, E.

The requirement needs notification WITHOUT aborting the transaction, which means AFTER ROW trigger (not BEFORE, which could validate/abort). AFTER ROW fires after the row is successfully updated but before the transaction commits, allowing notification logic to run. The restriction WHERE CAT_NAME='CONTAINER' AND NEW.TOTAL_COUNT<100 filters to only the relevant category and threshold. Option F is correct: AFTER ROW trigger with proper restriction.

Exception propagation checks the current block first. If unhandled, it bubbles up to the enclosing block, and eventually to the calling environment. Once handled, execution continues in the enclosing block or calling environment.

PL/SQL provides three boolean predicates for trigger context: INSERTING (true if trigger fired due to INSERT), UPDATING (true if due to UPDATE), and DELETING (true if due to DELETE). These help write logic that behaves differently based on the triggering event. CREATING, SELECTING, and ALTERING are NOT valid PL/SQL boolean trigger variables.

In PL/SQL, functions SQLCODE and SQLERRM cannot be referenced directly within SQL DML statements. They must first be assigned to local PL/SQL variables, which can then be safely used inside SQL queries or statements.

The fundamental difference is that a stored function MUST return a single value, while a stored procedure does NOT return a value (it can have OUT parameters but no RETURN statement). Option A captures this precisely. Option C is misleading because stored functions always return a value - they cannot optionally return or not return.

Cascading in database triggers refers to the domino effect where one trigger's action fires another trigger, which may fire yet another, creating a chain reaction. Option A correctly defines this phenomenon. Options B, C, and D describe normal database operations (updates, procedure calls, drops) rather than the specific cascading behavior of trigger chains.

A stored procedure can have ZERO parameters - it is entirely valid to create a procedure that takes no arguments and performs fixed operations. Many utility procedures are parameterless. The statement 'must have at least one argument' is false, making Option B correct.

IN is the default parameter mode in stored procedures. IN parameters pass values into the procedure but cannot be modified within the procedure body. OUT and IN OUT are explicit modes that must be declared when parameters need to return values to the caller.

Functions must return a value using an explicit RETURN statement in SQL. Stored procedures can modify data and return values through OUT parameters, but they are not required to return a value. This is a fundamental distinction in database programming.

DDL statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) are NOT allowed in database triggers because triggers execute within the context of a transaction and DDL operations issue implicit commits. This would break the transactional integrity. Only DML and DCL are permitted.

In SQLPlus, the SHOW ERRORS (or SHOW ERROR) command displays compilation errors for the most recently compiled PL/SQL block or database object. Commands like FIX, TROUBLESHOOT, INVESTIGATE, or ANALYZE error are not valid SQLPlus instructions.

RAISE_APPLICATION_ERROR can be used in stored procedures, functions, and packages - not just triggers. It's a built-in procedure for raising user-defined exceptions with an error code and message. It's available anywhere PL/SQL code executes.

A database trigger consists of TRIGGER EVENT (INSERT/UPDATE/DELETE), TRIGGER TYPE (BEFORE/AFTER/INSTEAD OF, statement/row level), TRIGGER RESTRICTION (WHEN condition), and TRIGGER ACTION (the PL/SQL block executed). TRIGGER SPECIFICATION and TRIGGER BODY are not standard terminology components.

When a trigger is in DISABLED state, it does not fire even if the triggering event occurs. The trigger must be explicitly ENABLED before it will execute its TRIGGER ACTION. This is useful for temporarily suspending trigger logic without dropping it.

AFTER STATEMENT triggers fire once per SQL statement regardless of how many rows are affected - even zero rows. The trigger will execute once, recording Eric as user, SYSDATE, and 0 for affected rows. This is statement-level trigger behavior in Oracle.

RANGE partitions data based on value ranges (e.g., dates or numeric ranges). LIST partitions data based on discrete value lists. HASH distributes data uniformly using a hash algorithm. INTERVAL is an extension of RANGE, not a parent type. VIRTUAL and REFERENCE are not partitioning schemes.

HASH partitioning distributes rows uniformly across partitions using a hash algorithm on the partitioning key. It's ideal when access patterns are unknown or when even data distribution is desired. RANGE requires logical ranges and LIST requires known discrete values.

To answer this question, the user needs to know what string functions are and be familiar with some commonly used string functions.

String functions are functions that operate on a string and return a modified string or information about the string. Some examples of string functions include substring, upper/lower case, length, concat, and trim.

Now, let's go through each option and determine which one is not a string function:

A. concat: This is a string function that concatenates two or more strings together.

B. initcap: This is a string function that capitalizes the first letter of each word in a string.

C. round: This is not a string function. It is a mathematical function that rounds a number to a specified number of decimal places.

D. length: This is a string function that returns the length of a string.

Therefore, the answer is:

The Answer is: C