Oracle PLSQL Online Exam Series 3, Oracle PLSQL Online Test

A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.

Yes, nested transactions are rolled back in stored procedure. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables.

SAVEPOINT savepoint_names marks the current point in the processing of a transaction. Savepoints let you rollback part of a transaction instead of the whole transaction.

Oracle is transaction oriented; that is, it uses transactions to ensure data integrity. A transaction is a series of one or more logically related SQL statements, you define to accomplish some task. Oracle treats the series of SQL statements as a unit so that all the changes brought about by the statements are either committed (made permanent) or rolled back (undone) at the same time. If your application program fails in the middle of a transaction, the database is automatically restored to its former (pre-transaction) state.

PL/SQL program execution into the Oracle engine so we always required to get ‘set serveroutput on’ command to display in the screen otherwise result can’t be displayed.

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

To create a trigger on a pluggable database (PDB), you must be connected to that PDB and have the ADMINISTER DATABASE TRIGGER system privilege. ADMINISTER DATABASE TRIGGER is used to allow creating or altering a system trigger on the database.

USER_TRIGGERS describes the triggers owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TRIGGERS.

The DBMS_PIPE package provides services that allow Oracle sessions connected to the same instance to communicate messages with each other without the need for a COMMIT. Sessions use DBMS_PIPE programs to pack data into a message buffer and then send the message to a memory area in the Oracle shared pool (the pipe ), where another session can receive it and unpack the message data into local variables.

The system-defined constructor, also known as the attribute-value constructor, requires you to pass the constructor a value for each attribute of the type. If there is no constructor defined within a class, system will create its own constructor and assigns default values into the data fields. This constructor created by the system is known as system-defined constructor.

The SET DESCRIBE setting controls the depth to which an object such as an object type or object table is described. It is used to describe more complex picture of object table.
Syntax:
SET DESCRIBE [DEPTH {levels | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]

where,
DEPTH {levels | ALL} : It specifies the depth to which to recursively describe an object. The default is to describe only the top level of columns, attributes, or parameters.

LINENUM {ON | OFF} : It adds or removes line numbers from the object’s description. The default is LINENUM OFF.

INDENT {ON | OFF} : It indents nested descriptions. The default is INDENT ON.

The %type attribute is used to declare variables based on definitions of columns in table. It provides the datatype of a variable or table column. This is particularly useful when declaring variables that will hold values of a table column.

A record type is a programming structure that mirrors a single row in a table. A record provides the means of defining a programming structure. A programming structure is a set of variable types. They are grouped together and managed as a unit. Record types map to a stored definition of the structure.

MULTISET EXCEPT returns the elements of the first set that are not present in the second set. MULTISET EXCEPT takes as arguments two nested tables and returns a nested table whose elements are in the first nested table but not in the second nested table. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

The ALL keyword instructs Oracle to return all elements in nested_table1 that are not in nested_table2. For example, if a particular element occurs m times in nested_table1 and n times in nested_table2, then the result will have (m-n) occurrences of the element if m >n and 0 occurrences if m

The above statement is true. MAP and ORDER methods cannot be defined in the same object. Map methods return values that can be used for comparing and sorting. Generally, map methods perform calculations on the attributes of the object to produce the return value. Map methods are called automatically to evaluate such comparisons. Comparisons implied by the DISTINCT, GROUP BY, UNION, and ORDER BY clauses which require sorting of rows.

Order methods make direct one-to-one object comparisons. Unlike map methods, they cannot determine the order of a number of objects. They simply tell you that the current object is less than, equal to, or greater than the object, that it is being compared to, based on the criterion used. An order method is a function for an object (SELF), with one declared parameter that is an object of the same type. As with map methods, an order method, if one is defined, is called automatically whenever two objects of that type need to be compared. Order methods are useful where comparison semantics may be too complex to use a map method.

Only one Order method is allowed per object. Order methods make direct one-to-one object comparisons. They cannot determine the order of a number of objects. They simply tell you that the current object is less than, equal to, or greater than the object, that it is being compared to, based on the criterion used.

MAP allows only SELF parameter. An object type can contain only one MAP method. It accepts the built-in parameter SELF and returns one of the following scalar types: DATE, NUMBER, VARCHAR2, or an ANSI SQL type such as CHARACTER or REAL.

ORDER method is one of the member methods used in the Oracle Object Relational Model to order variables of an object type. Based on a comparative condition it returns numeric values. It compares the values of two objects based on criteria you specify.

MAP method returns scalar. This method will convert the entire set of attribute as a scalar type in a single operation.

PCTVERSION indicates the percent of total LOB blocks that can contain old LOB data. PCTVERSION is the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. As soon as old versions of BasicFiles LOB data pages start to occupy more than the PCTVERSION amount of used BasicFiles LOB space, Oracle Database tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used BasicFiles LOB data blocks that is available for versioning old BasicFiles LOB data.

To display messages in PL/SQL, the SERVEROUTPUT should be set ON. The SQL*Plus command ‘set serveroutput on’ causes SQL*Plus to retrieve and display the buffer.

PL/SQL supports two comment styles: single-line and multi-line. Single-line comments begin with a double hyphen (- -) anywhere on a line and extend to the end of the line. Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines.

Using ROLLBACK without the TO SAVEPOINT clause performs the following operations:
– Ends the transaction.
– Undoes all changes in the current transaction.
– Erases all savepoints in the transaction.
– Releases any transaction locks.
Specify the savepoint to which you want to roll back the current transaction. If you omit TO SAVEPOINT clause, the ROLLBACK statement rolls back the entire transaction.

The oracle engine uses a work area for its internal processing in order to execute an SQL statement. The work area is private to SQL’s statements and is called a cursor.

Implicit cursors are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row. Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

The range operator(..) is used as a shorthand way to set up arrays. When used with arrays, the range operator simplifies the process of creating arrays with contiguous sequences of numbers and letters.