SQL Server and Oracle Database Programming Quiz

DBMS_METADATA package can retrieve DDL (Data Definition Language) for all objects within a schema, making option A correct. It can also retrieve DDL for object grants on database tables, making option B correct. Options C and D describe reports that are not the primary function of DBMS_METADATA.

COMPRESSION HIGH is available for all internal LOB types (BLOB, CLOB, NCLOB), not just CLOBs, making option B correct. The DECRYPT option can remove encryption from LOB columns regardless of whether they contain data or are empty, making option D correct. Option A is incorrect because compression isn't limited to CLOBs, and option C is incorrect because the DECRYPT option works on non-empty columns too.

Subquery results cannot be stored in the query result cache because they represent intermediate results, making option A correct. Queries with SYSDATE function cannot be cached since SYSDATE returns different values on each execution, making option B correct. GROUP BY results and static DATE conditions in WHERE clauses can be cached, so options C and D are incorrect.

Using bind arguments is a primary defense against SQL injection because it separates SQL logic from data values, preventing malicious input from altering SQL structure. Option A is incorrect because package subprograms alone don't prevent injection. Option B is incorrect because concatenation actually increases vulnerability. Option D about NOT NULL constraint is irrelevant to SQL injection security.

SYS_REFCURSOR is a pre-defined weak REF CURSOR type that can be used to declare cursor variables in stored procedures and functions, making option B correct. REF CURSOR return types can be declared using %TYPE, %ROWTYPE, or user-defined records, making option C correct. Option A is incorrect because REF CURSOR types can be defined inside packages. Option D is incorrect because both weak and strong REF CURSOR types can be used as formal parameters.

For a PL/SQL function to be cached, it must not be defined in an anonymous block (which are transient), making option C correct. It must have at least one OUT or IN OUT parameter to support result caching, making option D correct. Option A is incorrect because standalone functions can also be cached. Option B is incorrect because pipelined table functions are not eligible for result caching.

Transaction activity records individual business events continuously in real-time, making it the most dynamic and frequently changing type of data. Product catalogs, organizational staff, and structural data are structural and change much less frequently.

PATINDEX, TEXTPTR, and TEXTVALID are text and image functions in SQL Server. PATINDEX finds pattern positions, TEXTPTR returns a text pointer value, and TEXTVALID checks if a text pointer is valid. Options B and C incorrectly list aggregate functions like MIN, CHECKSUM, and STDEV, which are statistical functions, not text/image functions.

To find the 2nd maximum value, we need to find the maximum value from the set of records that excludes the overall maximum. The query in option C correctly does this: the subquery gets MAX(field4) WHERE field4 is NOT IN the list containing the single maximum value. This returns the second highest value. Options A and B use MIN incorrectly in the subquery, which would give the minimum value, not the second maximum.

Correlated subqueries can often replace row-by-row cursor operations and significantly improve performance. Cursors are slow because they process one row at a time with frequent context switching. A correlated subquery executes set-based operations which are optimized by the query engine. Indexes (option B) improve data access but don't replace row-by-row logic. TOP() (option C) returns a specified number of rows but doesn't perform row-by-row operations.

SQL Server allows exactly 1 clustered index per table because the clustered index determines the physical order of data storage. A table can have up to 999 non-clustered indexes (older documentation mentioned 249 as a practical limit). Option C correctly shows 1 clustered and 249 non-clustered indexes. The other options show incorrect combinations.