Oracle PL/SQL Programming

Simple Loop is called an infinite loop because it has no built-in termination condition. It continues indefinitely until explicitly exited using an EXIT or EXIT WHEN statement. Unlike For Loops (with counter limits) or While Loops (with conditions), Simple Loop relies entirely on manual exit logic.

Implicit cursors are automatically created by PL/SQL for all single-row DML operations (INSERT, UPDATE, DELETE) and single-row SELECT...INTO statements. Multi-row SELECT statements require explicit cursors with loops to process multiple rows.

Explicit cursors are explicitly declared and controlled by the programmer to process multi-row SELECT statements. They provide detailed control over row-by-row processing using OPEN, FETCH, and CLOSE operations, which implicit cursors cannot handle.

In PL/SQL cursor FOR loops, the OPEN, FETCH, and CLOSE operations are handled automatically by the loop construct. The loop opens the cursor when entered, fetches each row iteratively, and closes the cursor when the loop completes or exits. The cursor must still be explicitly DECLARED, and the PROCESS step (loop body logic) is written by the developer.

The %ROWCOUNT attribute returns the number of rows fetched so far by an open cursor or affected by DML statements. In contrast, %FOUND and %NOTFOUND are boolean attributes indicating whether a row was fetched, and %ISOPEN returns a boolean indicating whether the cursor is open or closed.

The %FOUND cursor attribute evaluates to TRUE if the most recent FETCH statement returned at least one row. %NOTFOUND evaluates to TRUE if no rows were returned, %ROWCOUNT returns the number of rows, and %ISOPEN checks if the cursor is open.

%NOTFOUND evaluates to TRUE when a FETCH operation fails to return a row (no more rows), and FALSE when at least one row is successfully returned. This attribute is commonly used to check if a cursor has exhausted its rows after a fetch operation. %FOUND does the opposite (TRUE when a row is found).

%ISOPEN is a cursor attribute that returns TRUE if the cursor is currently open and FALSE if it is closed. This is useful for checking cursor state before attempting operations that require an open cursor. The other attributes track row existence (%FOUND/%NOTFOUND) or row count (%ROWCOUNT).

In Oracle SQL, the default date format is DD-MON-RR (e.g., 07-JUL-26). This uses the RR format which automatically handles century crossing for years 0-99. MON represents the three-letter month abbreviation in uppercase. Other formats like DD-MM-YYYY or DD-Mon-YY must be explicitly specified.

The RPAD function right-pads a string to a specified length. RPAD('$', 3, '') takes the string '$' which is already 3 characters long, so no padding occurs. The result is simply '$*'. The function would only add asterisks if the original string were shorter than 3 characters.

SUBSTR('Oracle Basics', -5, 3) starts 5 characters from the end, giving 'sics' starting from position -5, then extracts 3 characters: 'asi'. Negative positions count from the string's end. So position -5 is 's', -4 is 'i', -3 is 'c', -2 is 's', -1 is the last character. Starting at -5, three characters gives 'asi'.

The MOD function returns the remainder when the first number is divided by the second. Here, 1600 divided by 300 equals 5 with a remainder of 100 (since 300 × 5 = 1500). Option B correctly shows 100 as the remainder. Options A and C (300 and 500) are the divisor and a multiple respectively, while D (000) is just a formatting variation of zero.

Grouping (aggregate) functions operate on sets of values and return a single result. AVG, COUNT, and MIN are all standard SQL aggregate functions used with GROUP BY. ABS is a single-row numeric function that returns the absolute value of one number - it does NOT group or aggregate data, making it the correct answer.

Oracle SQL has ROUND, TRUNC, and INITCAP as built-in functions for numeric rounding, truncation, and string capitalization respectively. 'MAXIMUM' is NOT a valid Oracle function - the correct aggregate function is MAX (not MAXIMUM). This is a common trick question testing knowledge of exact function names.

The COUNT() function is specifically designed to return the number of rows in a result set or table. SUM adds numeric values, num_rows is not a standard SQL function (it may exist in specific programming interfaces), and 'total' is not a built-in SQL function. COUNT is the universal SQL method for row counting.

In Oracle and PostgreSQL SQL, the || (double pipe) operator is the standard concatenation operator that joins two strings together. The CONCAT() function does the same thing. Single operators like &, |, and && are bitwise operators, not string concatenation in SQL.

In PL/SQL exception handling, OTHERS is indeed a catch-all exception handler that encompasses all predefined exceptions (like NO_DATA_FOUND, TOO_MANY_ROWS, etc.) as well as user-defined exceptions. When you use EXCEPTION WHEN OTHERS, it will catch any exception that hasn't been handled by more specific exception handlers above it. This makes it the most general exception type.