Oracle PLSQL Online Test Series 2, Oracle PLSQL Online Exam

The getOracleArray method is an Oracle-specific extension that is not specified in the standard Array interface. The getOracleArray method retrieves the element values of the array into a Datum[] array. The elements are of the oracle.sql.* data type corresponding to the SQL type of the data in the original array. For an array of structured objects, this method will use oracle.sql.STRUCT instances for the elements. Oracle also provides a getOracleArray(index,count) method to get a subset of the array elements.

A PL/SQL associative array is a collection type that associates a unique key with a value. An associative array has the following characteristics:
– An associative array type must be defined before array variables of that array type can be declared. Data manipulation occurs in the array variable.
– The array does not need to be initialized; simply assign values to array elements.
– There is no defined limit on the number of elements in the array; it grows dynamically as elements are added.
– The array can be sparse; there can be gaps in the assignment of values to keys.
– An attempt to reference an array element that has not been assigned a value results in an exception.
– Use the TYPE IS TABLE OF statement to define an associative array type.

Syntax:

>>-TYPE–assoctype–IS TABLE OF—-datatype———————>

>–INDEX BY–+-BINARY_INTEGER————–+——————->

If the TYPE statement contains an INDEX BY clause, the collection type is an associative array. A PL/SQL associative array is a collection type that associates a unique key with a value.

Remove all elements from a collection; use the DELETE method without any arguments. This form of DELETE works with all three kinds of collections.
l_names.DELETE;

A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection. Following are the collection exceptions:
1. COLLECTION_IS_NULL : You try to operate on an atomically null collection.
2. NO_DATA_FOUND : A subscript designates an element that was deleted, or a nonexistent element of an associative array.
3. SUBSCRIPT_BEYOND_COUNT : A subscript exceeds the number of elements in a collection.
4. SUBSCRIPT_OUTSIDE_LIMIT : A subscript is outside the allowed range.
5. VALUE_ERROR : A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

The %ROWTYPE attribute is used to declare a record that represents a row in a database table, without listing all the columns. The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Fields in a record and corresponding columns in a row have the same names and datatypes.

Record variables are allowed only in the following places:

1. On the right side of the SET clause in an UPDATE statement because “SET” only found in an UPDATE statement.
2. In the VALUES clause of an INSERT statement because “VALUES” only found in an INSERT statement.
3. In the INTO sub-clause of a RETURNING clause.

A nested cursor is implicitly opened when the cursor expression is evaluated. For example, if the cursor expression appears in a select list, a nested cursor will be opened for each row fetched by the query. The nested cursor is closed only when:

– The nested cursor is explicitly closed by the user.
– The parent cursor is re-executed.
– The parent cursor is closed.
– The parent cursor is cancelled.
– An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up).

The nested cursor is controlled implicitly while the parent cursor is being processed. It is opened when the containing row is fetched from the parent cursor, and it is closed either explicitly by the user or when the parent cursor is re-executed, closed, cancelled or returns an error during a fetch operation.

The following restrictions apply to CURSOR expressions:

– If the enclosing statement is not a SELECT statement, nested cursors can appear only as REF CURSOR arguments of a procedure.
– If the enclosing statement is a SELECT statement, nested cursors can also appear in the outermost select list of the query specification or in the outermost select list of another nested cursor.

– Nested cursors cannot appear in views.
– You cannot perform BIND and EXECUTE operations on nested cursors.

The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables. This clause is not supported for the user SYS. That is, queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY. Following statements are permitted in a read-only transaction:

– Subqueries–that is, SELECT statements without the for_update_clause
– LOCK TABLE
– SET ROLE
– ALTER SESSION
– ALTER SYSTEM

Collection methods cannot be called from SQL statements. Collection methods make collections easier to use, and make your applications easier to maintain. These methods include COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT, NEXT, PRIOR, and TRIM. A collection method is a built-in function or procedure that operates on collections and is called using dot notation.

FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts. If the collection is empty, FIRST and LAST return NULL. If the collection contains only one element, FIRST and LAST return the same index value.

To increase the size of a nested table or varray, use EXTEND. This procedure has three forms:
1. EXTEND appends one null element to a collection.
2. EXTEND(n) appends n null elements to a collection.
3. EXTEND(n,i) appends n copies of the ith element to a collection.
You cannot use EXTEND with index-by tables. You cannot use EXTEND to add elements to an uninitialized collection. If you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.

Record variables are not allowed in SELECT list, WHERE clause and GROUP BY clause.

EXISTS method returns TRUE if the nth element in a collection exists. It is used to avoid referencing a nonexistent element. EXISTS with DELETE can work with sparse nested tables.

The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out the common subquery, put it all together at one place and call it as many times as needed in the main query.

An index-by table, also called an associative array is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string. Using a key-value pair for the first time adds that pair to the associative array. Using the same key with a different value changes the value.

If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter. IN type parameter is used to send values to stored procedures.

TRIM procedure has two forms. TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection. If n is greater than COUNT, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT. You cannot use TRIM with index-by tables.TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally.

A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection. Following are the collection exceptions:
1. COLLECTION_IS_NULL : You try to operate on an atomically null collection.
2. NO_DATA_FOUND : A subscript designates an element that was deleted, or a nonexistent element of an associative array.
3. SUBSCRIPT_BEYOND_COUNT : A subscript exceeds the number of elements in a collection.
4. SUBSCRIPT_OUTSIDE_LIMIT : A subscript is outside the allowed range.
5. VALUE_ERROR : A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

EXTEND procedure has three forms. EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to a collection. EXTEND operates on the internal size of a collection. If EXTEND encounters deleted elements, it includes them in its tally. You cannot use EXTEND with associative arrays.

Yes, nested table can extend index-by table with SELECT, INSERT, UPDATE, DELETE operations. Nested tables are similar to index by table, but these can be stored in database columns, but index by tables cannot be stored in database columns.

Guys please improve the Quality..!! (English also)

You’re asking for removing ‘an item’ not removing ‘few/some items’. The answer for this question should be DELETE(n).
DELETE(m,n) would delete itemS between mth and nth element.

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once. Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:
– Make a complex security check on the current time or user.
– Generate a single audit record.

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements). RAISE_APPLICATION_ERROR raises an exception but does not handle it.