Test Flashcards

When using explicit cursors, list the correct command sequence to process a batch of records.

Answer: OPEN, FETCH, CLOSE

When working with explicit cursors in PL/SQL to process a batch of records, the correct command sequence after declaring the cursor is `OPEN`, `FETCH`, and then `CLOSE`. The `OPEN` statement executes the query and identifies the active set of rows, `FETCH` retrieves rows one by one from this active set, and `CLOSE` releases the resources associated with the cursor.

Choose variables that aren't valid.

Answer: VARCHAR1

In Oracle SQL, `NUMBER`, `CHAR`, and `INTEGER` are all valid data types used for storing numeric, fixed-length character, and integer values, respectively. However, `VARCHAR1` is not a valid data type. The correct variable-length character data type in Oracle SQL is `VARCHAR2`, which is used to store character strings of varying lengths.

Which of the following functions does not work as a grouping function?

Answer: DISTINCT

Grouping functions, also known as aggregate functions, perform a calculation on a set of rows and return a single summary value. Examples include `MIN` (minimum value), `SUM` (total sum), and `COUNT` (number of rows). `DISTINCT`, however, is a keyword used to eliminate duplicate values from a result set or within an aggregate function, but it is not an aggregate function itself.

In the PL/SQL language, where do you declare an explicit cursor?

Answer: In the PL/SQL declaration section.

In PL/SQL, an explicit cursor must be declared in the `DECLARE` section of a PL/SQL block. This section is where all variables, constants, and cursors are defined before they can be used within the executable `BEGIN...END` block. Declaring it here makes the cursor known to the program for subsequent `OPEN`, `FETCH`, and `CLOSE` operations.

Select the PL/SQL looping construct that isn't valid.

Answer: LOOP ... UNTIL ;END LOOP;

PL/SQL supports several looping constructs, including the basic `LOOP...EXIT WHEN...END LOOP;`, `WHILE LOOP...END LOOP;`, and `FOR` loops (both numeric and cursor-based). The `LOOP...UNTIL...END LOOP;` construct is not a valid looping syntax in PL/SQL. While `UNTIL` is used in some programming languages, PL/SQL uses `EXIT WHEN` for conditional termination within a basic loop.

Which of the following is not a DML function in Oracle?

Answer: TRUNCATE

DML (Data Manipulation Language) commands are used to manage data within schema objects, such as INSERT, UPDATE, DELETE, MERGE, CALL, and LOCK TABLE. Functions like NVL, TO_CHAR, and DECODE are used to manipulate or convert data values. TRUNCATE, however, is a DDL (Data Definition Language) command, used to remove all rows from a table and deallocate the space, making it a structural operation rather than a data manipulation one.

Choose the most appropriate response from the options provided below. What comprises a package?

Answer: Specification and body

An Oracle PL/SQL package is a schema object that groups related PL/SQL types, items, and subprograms. It is composed of two distinct parts: the package specification and the package body. The specification declares the public items and subprograms, while the body provides the detailed implementation for the subprograms declared in the specification.

Which of the following Oracle PL/SQL exceptions is not valid?

Answer: TWO_MANY_ROWS ORA-01422

Oracle PL/SQL provides several predefined exceptions for common error conditions. DUP_VAL_ON_INDEX (ORA-00001) and NO_DATA_FOUND (ORA-01403) are valid predefined exceptions. However, 'TWO_MANY_ROWS' is not a valid predefined exception; the correct predefined exception for a SELECT INTO statement that returns more than one row is `TOO_MANY_ROWS` (with two 'O's).

Choose the incorrect variable declarations.

Answer: Foo_text varchar2(10) := 'hello world';

In Oracle PL/SQL, when declaring a `VARCHAR2` variable, the specified length defines its maximum capacity. The literal string 'hello world' contains 11 characters. Declaring `Foo_text varchar2(10)` means it can hold a maximum of 10 characters, so assigning an 11-character string to it will result in a `VALUE_ERROR` at runtime or a compilation error, making this an incorrect declaration.