PL/SQL

Question 1

What is the DECLARE keyword?

Answer 1

It is an optional section and defines all variables, cursors, subprograms, and other elements to be used in the program.

Question 2

What is the keywords BEGIN and END?

Answer 2

keywords BEGIN and END and it is a
mandatory section. It consists of the executable PL/SQL statements of the
program. It should have at least one executable line of code, which may be just
a NULL command to indicate that nothing should be executed.

Question 3

What is Scalar Datatype?

Answer 3

Single values with no internal components, such as a NUMBER, DATE, or BOOLEAN.

Question 4

What is Numeric Datatype?

Answer 4

Data items that have internal components that can be accessed individually. For example, collections and records.

Question 5

What is the VARCHAR2 Datatype?

Answer 5

Variable-length character string with maximum size of 32,767 bytes

Question 6

What is a record?

Answer 6

A record is a data structure that can hold data items of different kinds. Records consist of different fields, similar to a row of a database table.

Question 7

What is the context area for?

Answer 7

for processing an SQL statement, which contains all the

information needed for processing the statement; for example, the number of rows processed, etc.

Question 8

A cursor is

Answer 8

A cursor is a pointer to this context area.

Question 9

PL/SQL controls the context area through a

[Blank[

Answer 9

cursor.

Question 10

A cursor holds the [Blank] (one or more) returned by a SQL statement.

Answer 10

rows

Question 11

What is an Implicit Cursors?

Answer 11

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is
associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

Question 12

What is an Explicit Cursors?

Answer 12

Explicit cursors are programmer-defined cursors for gaining more control over the context
area. An explicit cursor should be defined in the declaration section of the PL/SQL Block.
It is created on a SELECT Statement which returns more than one row.

CURSOR cursor_name IS select_statement;

Question 13

What are Triggers?

Answer 13

Triggers are stored programs, which
are automatically executed or fired when some events occur. Triggers are, in fact, written
to be executed in response to any of the following events:
 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
 A database definition (DDL) statement (CREATE, ALTER, or DROP).
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is
associated.

Question 14

How do you create a Trigger?

Answer 14

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements

Question 15

What is a database TRANSACTION?

Answer 15

A database transaction is an atomic unit of work that may consist of one or more related SQL statements. It is called atomic because the database modifications brought about by the SQL statements that constitute a transaction can collectively be either committed, i.e., made permanent to the database or rolled back (undone) from the database

Question 16

A transaction ends when one of the following events take place:

Answer 16

 A COMMIT or a ROLLBACK statement is issued.
 A DDL statement, such as CREATE TABLE statement, is issued; because in that
case a COMMIT is automatically performed.
 A DCL statement, such as a GRANT statement, is issued; because in that case a
COMMIT is automatically performed.
 User disconnects from the database.
 User exits from SQLPLUS by issuing the EXIT command, a COMMIT is
automatically performed.
 SQLPlus terminates abnormally, a ROLLBACK is automatically performed.
 A DML statement fails; in that case a ROLLBACK is automatically performed for
undoing that DML statement.

Question 17

Rolling Back Transactions

Answer 17

Changes made to the database without COMMIT could be undone using the ROLLBACK command.

ROLLBACK [TO SAVEPOINT < savepoint_name>];

Question 18

When a transaction is aborted due to some unprecedented situation, like system failure,
the entire transaction since a commit is automatically [Blank].

Answer 18

rolled back

Question 19

If you are not using savepoint, then simply use the following statement to rollback all the changes:

Answer 19

Rollback;

Question 20

What are SavePoints and how do you call it?

Answer 20

Savepoints are sort of markers that help in splitting a long transaction into smaller units
by setting some checkpoints. By setting savepoints within a long transaction, you can roll
back to a checkpoint if required. This is done by issuing the SAVEPOINT command.

SAVEPOINT < savepoint_name >;

Question 21

To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment variable as:

Answer 21

SET AUTOCOMMIT ON;