Database and SQL

PL/SQL automatically raises NO_DATA_FOUND exception when a SELECT INTO statement returns no rows. This is a predefined exception that triggers when a single-row query fails to find any matching data. Unlike explicit error checking, this built-in mechanism handles empty result sets gracefully.

In PL/SQL, exceptions have scope similar to variables. You can declare an exception with the same name in two different blocks (nested or separate). The exception declared in the inner block will hide the one in the outer block, or they will simply exist independently in their respective scopes.

ISNULL(@variable,'5') returns '5' when @variable is NULL, otherwise returns @variable itself. COALESCE(@variable,'5') does the same by returning the first non-NULL value from its arguments. NULLIF returns NULL if two values are equal - the opposite behavior needed here, and NONNULL is not a valid SQL function.

DENSE_RANK() assigns consecutive ranks (1,2,2,2,3) without gaps after tied values, making it ideal for the requested pattern. ROW_NUMBER() always produces unique sequential numbers (1,2,3,4,5), while RANK() leaves gaps after ties (1,2,2,2,5). NTILE() divides rows into specified number of groups, producing a different pattern entirely.

A Group Left report is a specific layout style where the master (grouping) column is placed to the left of the detail columns. This prevents the master value from repeating for every row of detail data, creating a clean, hierarchical view of the information.

Lexical references are placeholders for text embedded in SELECT statements in reporting tools. Parameters pass values at runtime, fields display data, and lexical references allow dynamic text substitution in queries.

The statement 'A cursor is static' is false - cursors can be dynamic depending on the database and configuration. Matrix objects do define relationships between repeating frames, and non-linkable queries contain columns preventing column-to-column linking. Therefore B is NOT true.

In the Function window, you specify the PL/SQL function using the format . in the Function Name field. This fully qualified name identifies the stored procedure within its package. The Display Name is just for human-readable labels, not the actual function specification.

The Oracle Workflow Engine passes four standard parameters (item type, item key, activity ID, and command mode) to stored procedures called for function activities. The procedure can return a result status that the engine uses to determine workflow progression. This parameter passing is part of the standard PL/SQL API for workflow functions.

In PeopleSoft Application Engine, Call Section and SQL actions cannot coexist in the same step because they represent different execution paradigms - Call Section transfers control to another section, while SQL executes database statements directly. Do Select and PeopleCode can both be used in the same step with Call Section or SQL.

FIRSTDUP(n) is the correct DFSORT/ICETOOL operator that selects n records after removing duplicates. DUPLICATE(n) is not a valid operator name, REMOVDUP doesn't exist in this syntax, and NONE is incorrect.

The WHERE clause filters individual rows before grouping, then GROUP BY aggregates those rows into groups, and finally HAVING filters the aggregated groups based on group-level conditions. This order ensures efficient query execution.

The first INSERT succeeds because no rows exist. The second INSERT fails because it violates the Unique Primary Index on column A - row (1,2) already exists with A=1. The UPDATE would affect 0 rows since the second INSERT failed.

SUBSTRING('RALPH JOHNSON' FROM 6 FOR 5) extracts 5 characters starting at position 6, which yields ' JOHN'. The INDEX function then searches for 'OHN' within ' JOHN'. 'OHN' starts at the 3rd character of ' JOHN' (space is 1, J is 2, O is 3), returning 3.