Oracle PL/SQL Programming Quiz

In Oracle, a table can have only one LONG column. The LONG datatype is deprecated in favor of LOB datatypes (CLOB, BLOB), but the restriction of one LONG column per table still applies. This is a well-known Oracle limitation.

Materialized views store actual data physically, unlike regular views which are just stored queries. When base tables are dropped, the materialized view's data remains in storage. The view may become invalid or unusable, but the data is not automatically deleted. The statement is false.

When a base table is dropped from the database, views that reference that table become invalid and cease to function properly. The view object definition may still exist in the data dictionary, but the view is not usable. The statement 'Does the view exist' is asking about usability - a view dependent on a dropped table does not exist as a functional object. The claimed answer (B - False) is correct.

RowID is a pseudo-column that uniquely identifies each row in a table. Once assigned when a row is inserted, it remains stable during normal update operations and does not change. The statement incorrectly suggests RowID changes on every insert/update.

Native compilation provides better performance for procedures with looping structures because it compiles directly to machine code, eliminating interpretation overhead. Interpreted compilation translates line-by-line at runtime, which is slower for repeated loop iterations.

You cannot execute a direct COMMIT statement inside a database trigger. However, you can commit changes by using a workaround like declaring the trigger as an autonomous transaction via PRAGMA AUTONOMOUS_TRANSACTION.

In Oracle PL/SQL procedure definitions, IS and AS are completely interchangeable keywords. Both work identically - you can use either 'IS' or 'AS' when declaring a procedure. This is a legacy compatibility feature from Oracle's PL/SQL evolution.

In Oracle PL/SQL, subprogram overloading (defining multiple procedures with the same name but different parameters) is only supported within packages or as local subprograms, making it a packaged procedure feature here.

VARRAY (Varying Array) is the most performance-friendly collection type in Oracle because it's stored inline with the table data, has a fixed maximum size, and uses contiguous memory allocation. PL/SQL tables (associative arrays) and nested tables have more memory overhead.

The ASCII() function typically returns the ASCII value of the FIRST character of a string, not the entire string. It's a single-character function - passing a multi-character string only processes the first character. The question is misleading about displaying 'ASCII code of the string'.

In program overloading, procedures are distinguished by their parameter signatures (number, order, and data types). Simply interchanging parameter positions doesn't create a unique signature - 'proc(a,b)' and 'proc(b,a)' where both are the same data type are ambiguous. You need different data types, counts, or names.

A PL/SQL cursor FOR loop automatically manages opening, fetching, and closing the cursor. You do not need to issue explicit OPEN, FETCH, or CLOSE statements.

In Oracle 9i, you cannot specify the firing order of triggers on a table. Triggers fire in an undefined order unless you use a single trigger with ordered logic. Oracle 10g introduced the FOLLOWS clause to control trigger execution order.

In PL/SQL, RAISE_APPLICATION_ERROR is the built-in procedure used to create user-defined, customized error messages with error codes between -20000 and -20999.

Packages DO allow privilege control - you can grant EXECUTE privileges on the package, which controls access to all packaged objects. The other statements are true benefits: packages simplify dependencies, load once into memory, and encapsulate logic.