Oracle Database and SQL Fundamentals

For bulk data operations in PL/SQL, a table of records (declared as TYPE t_tab IS TABLE OF table_name%ROWTYPE) is the most efficient mechanism. It allows storing entire rows from a table in a PL/SQL collection variable. A cursor only points to rows, varchar2 stores single values, a record holds one row, and a PL/SQL table (index-by table) is less efficient than a table of records for bulk operations.

Oracle PL/SQL packages do not have a hard limit on the number of subprograms (procedures and functions) that can be included. The limit is governed by practical considerations like memory, compilation time, and manageability rather than a fixed numerical constraint.

In older Oracle Database versions, the maximum parsed size of a stored PL/SQL block is limited to 64K. This limit applies to the size of the DIANA (Descriptive Intermediate Attributed Notation for Ada) compiler code generated for the block, making 64K the correct historical limit.

The WRAP command is used to encrypt PL/SQL code, converting source code into a format that obscures the logic while preserving functionality. COMPILE DEBUG adds debugging information but doesn't encrypt. ENCRYPT is not a valid PL/SQL command - WRAP is Oracle's native obfuscation utility.

Oracle allows up to 12 distinct triggers per table: 4 timing levels (BEFORE/AFTER) × 2 events (INSERT/UPDATE/DELETE) × 2 levels (statement/row) for UPDATE, plus INSERT and DELETE at row and statement levels. This gives INSERT (2), UPDATE (4), DELETE (2), and 4 more variations totaling 12.

Oracle allows a maximum of 1000 columns per table. This limit has been consistent across recent Oracle versions. The other options (256, 128, 512) are all below the actual limit and would be incorrect.

Oracle indexes can include up to 32 columns. This is a hard limit in Oracle database architecture. The other options (12, 256, 128) are incorrect - 12 is too low for complex indexing needs while 256 and 128 exceed the actual limit.

TRUNCATE is a DDL (Data Definition Language) operation because it removes all rows and resets storage, performing an implicit commit that cannot be rolled back. MERGE is DML (combines INSERT/UPDATE/DELETE). REVOKE and GRANT are DCL (Data Control Language) statements for permissions.

Option B is incorrect because it uses ORDER BY C2 without C2 being in the SELECT list, which is invalid Oracle SQL. The other options are valid: DISTINCT can be used with * or specific columns, arithmetic expressions in SELECT are allowed, and column aliases can be repeated though it's poor practice.

MERGE is a DML (Data Manipulation Language) statement that combines INSERT and UPDATE operations based on a condition. TRUNCATE is DDL (removes data, resets storage). REVOKE and GRANT are DCL (Data Control Language) for managing permissions.

This statement is TRUE in Oracle SQL. You can ORDER BY columns that are not in the SELECT list - the sorting is performed before the final projection. This allows sorting by attributes you don't need to display, which is valid SQL syntax.

USER_OBJECTS is the correct Oracle system view that shows all objects owned by the current user, including views and their status (VALID or INVALID). USER_VIEWS contains view text/definitions but not status. USER_TAB_COLUMNS describes column information, and USER_TABLES only lists tables.

In Oracle, readers never block writers and writers never block readers due to multiversion read consistency. When rows are locked by DML operations, other sessions can still query those rows using the before-image from undo segments. This prevents SELECT statements from waiting for row locks to be released.

Oracle allows reference to SYSDATE, NEXTVAL, and CURRVAL in constraint definitions. These pseudocolumns and sequence functions can be used in DEFAULT, CHECK constraints and other constraint definitions. For example, a DEFAULT SYSDATE constraint or a check constraint using CURRVAL is valid.

When you define a UNIQUE KEY or PRIMARY KEY constraint, Oracle automatically creates a unique index to enforce uniqueness. This index may be explicitly named or system-generated. Without a unique index, the database could not efficiently prevent duplicate values.