Type of Exception in Pl / Sql

CURSOR_ALREADY_OPEN ~ You tried to OPEN a cursor that was already OPEN. You must CLOSE a cursor before you try to OPEN or re-OPEN it. 

DUP_VAL_ON_INDEX ~ Your INSERT or UPDATE statement attempted to store duplicate values in a column or columns in a row which is restricted by a unique index.

INVALID_CURSOR  ~ You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.

INVALID_NUMBER ~ PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. This exception is different from the VALUE_ERROR exception, as it is raised only from within a SQL statement.

LOGIN_DENIED ~  Your program tried to log onto the Oracle RDBMS with an invalid username-password combination. This exception is usually encountered when you embed PL/SQL in a 3GL language.

NO_DATA_FOUND ~ This exception is raised in three different scenarios:
(1) You executed a SELECT INTO statement (implicit cursor) that returned no rows.
(2) You referenced an uninitialized row in a local PL/SQL table.       
(3) You read past end of file with UTL_FILE package.

NOT_LOGGED_ON  ~ Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS.

PROGRAM_ERROR ~ PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support."

STORAGE_ERROR ~ Your program ran out of memory or memory was in some way corrupted.

TIMEOUT_ON_RESOURCE ~ A timeout occurred in the RDBMS while waiting for a resource.

TOO_MANY_ROWS ~ A SELECT INTO statement returned more than one row. A SELECT INTO can return only one row; if your SQL statement returns more than one row you should place the SELECT statement in an explicit CURSOR declaration and FETCH from that cursor one row at a time.

TRANSACTION_BACKED_OUT ~ The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action.

VALUE_ERROR ~ PL/SQL raises the VALUE_ERROR whenever it encounters an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. This is a very general and common exception. If this same type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised.

ZERO_DIVIDE ~ Your program tried to divide by zero.

What is a stored procedure ?

A stored procedure is a subprogram that is stored in the database as a PCODE and thus called as a standalone schema object.

a) It can be invoked directly from any calling environments, or other subprograms.

b) It is similar to any subprogram that accepts parameters and performs an action. It may or may not return a value. This attribute differentiates it from another subprogram type called FUNCTION.

c) It can be nested

d) Is created by using the syntax CREATE OR REPLACE

e) This stored procedure can be seen as an object in the User_Objects and is different from the procedures that are created in packages.

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

Generally In triggers you can't use TCL commands.
But you can use TCL commands in Autonomous Triggers.
You can declare a trigger as Autonomous by providing PRAGMA AUTONOMOUS_TRANSACTION in the beginning of the trigger.
At a same time you have to end your trigger with commit/rollback.
You can use these type of triggers to maintain log details of a table.

What are the cursor attributes used in PL/SQL?

For Implicit Cursors:-
Found - Returns TRUE If the last DML Statement affect atleast 1 row otherwise FALSE.
NotFound - Returns TRUE If last DML Statement doesn't affect atleast 1 row otherwise FALSE.
Rowcount - Returns number of rows affected by the last DML statement.
Isopen - Returns TRUE if the cursor is open otherwise FALSE. Its always returns FALSE incase of implicit cursor. Because after executing the DML statement Oracle Server automatically close the cursor.

For Explicit Cursors:-
Found - Returns TRUE if the Last fetch returns a Row otherwise FALSE.
NotFound - Returns TRUE if the last fetch doesn't return any row otherwise FALSE.
Rowcount - Returns number of rows returned so far from the active set.
Isopen - Returns TRUE if the cursor is open otherwise FALSE.

What is a cursor ? Why Cursor is required ?

CURSOR
Whenever a sql statement is issued at that time oracle server will allocate some memory area to execute and parse that sql statement. That data area is called as cursor.

Two types : IMPLICIT EXPLICIT


IMPLICIT
-----------
(1) Called as SQL.
(2) Never user CURSOR keyword explicitly
(3) Memory allocation and program controll will done automatically
(4) Returns or updates or deletes only one record.
(5) Attributes : SQL FOUND SQL NOTFOUND

EXPLICIT
-----------
(1) called as cursors
(2) Uses CURSOR keyword explicitly
(3) Memory allocation and program control thru the cursor written.
(4) Returns or updates or deletes multiple records.
(5) Attributes : SQL FOUND SQL NOTFOUND ISOPEN ROWCOUNT

What is PL/SQL table ?

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array. 

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined. 

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between. 

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns. 

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -2³¹-1 to 2³¹-1, so you have an awful lot of rows with which to work

What are the datatypes a available in PL/SQL ?

Following are the datatype supported in oracle PLSQL

Scalar Types

BINARY_INTEGER
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INT
INTEGER
NATURAL
NATURALN
NUMBER
NUMERIC
PLS_INTEGER
POSITIVE
POSITIVEN
REAL
SIGNTYPE
SMALLINT
CHAR
CHARACTER
LONG
LONG RAW
NCHAR
NVARCHAR2
RAW
ROWID
STRING
UROWID
VARCHAR
VARCHAR2

DATE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE

BOOLEAN

Composite TypesRECORD
TABLE
VARRAY

LOB TypesBFILE
BLOB
CLOB
NCLOB

Reference Types
REF CURSOR
REF object_type

What is the basic structure of PL/SQL ?

A PL/SQL block has three parts:

a declarative part

an executable part

and an exception-handling part.

First comes the declarative part in which items can

be declared. Once declared items can be manipulated in the executable part.

Exceptions raised during execution can be dealt with in the exception-handling part.

Programmatic Constructs

 What are the different types of PL/SQL program units that can be defined and stored in ORACLE database?

Procedures, functions, packages and database triggers.

 What is a Procedure?

A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a Specific problem or perform a set of related tasks.

 What is difference between Procedures and Functions?

A Function returns a value to the caller where as a Procedure does not.

What is a Package?

A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

What are the advantages of having a Package?

Increased functionality (for example, global package variables can be declared and used by any procedure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

 What is Database Trigger?

A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.

 What are the uses of Database Trigger?

Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

What are the differences between Database Trigger and Integrity constraints?

A declarative integrity constraint is a statement about the database that is always true. A constraint applies to existing data in the table and any statement that manipulates the table. A trigger does not apply to data loaded before the definition of the trigger; therefore, it does not guarantee all data in a table conforms to the rules established by an associated trigger. A trigger can be used to enforce transitional constraints where as a declarative integrity constraint cannot be used.

PL/SQL Interview Question

• Data types are NUMBER, CHAR/VARCHAR2, DATE & BOOLEAN.

• Arrays are not allowed & only one identifier per line is allowed.

• Attributes of PL/SQL objects are %TYPE, %ROWTYPE.

• PL/SQL Block is a standard PL/SQL code segment. Block consists of three parts.

• Declarative Section for variables, constants & exceptions. This section is optional.

• Executable Section which is mandatory.

• Exception Handlers which is optional.

• PL/SQL supports only DML i.e. INSERT, UPDATE, DELETE & SELECT...INTO.

• SQL Functions can be referenced within a SQL statement i.e. Numeric (SQRT,ROUND,POWER),

• Character (LENGTH,UPPER), DATE (ADD_MONTHS,MONTHS_BETWEEN) &Group (AVG,MAX,COUNT). Most SQL functions are available outside SQL statement except for group functions.

Code Simple Loops repeats a sequence of statements multiple times.
Syntax : LOOP
<Sequence of Statements>
END LOOP;
Code Numeric FOR Loops repeat a sequence of statements a fixed number of times.
Syntax : FOR <index> IN [[ REVERSE ]] <integer>..<integer> LOOP
<sequence of statements>
END LOOP;
<index> is implicitly of type number. Defined only within the loop & Value can be referenced in an expression, but a new value cannot be assigned to the index within the loop.
Code While Loops repeats a sequence of statements until a specific condition is no longer TRUE.
Syntax : WHILE <condition> LOOP
<sequence of statements>
END LOOP;
<condition> can be any legal PL/SQL condition & statements will be repeated as long as condition evaluates to TRUE.

Code GOTO Statements jumps to a different place in the PL/SQL block.
Syntax : GOTO label_name;
Legally use a GOTO a statement that is in the same sequence of statements as the GOTO.
In the sequence of statements that encloses the GOTO statement (outer block).
Labels can label any statement. Used as targets for GOTO statements, use labels for blocks and loops, Label a block to allow referencing of DECLAREd objects that would otherwise not be visible because of scoping rules, Label a block to allow a variable to be referenced that might be hidden by a column name, Label a loop to allow an object to be reference that would otherwise not be visible because of scoping rules & Label an EXIT as a convenient way to specify exits from outer loops.

Cursors are associated with every SQL DML statement processed by PL/SQL. Two types are Explicit i.e. Multiple row SELECT statements & Implicit i.e. INSERT, UPDATE, DELETE & SELECT...INTO statements. Implicit cursor is called the SQL cursor-it stores info concerning the processing of the last SQL statement not associated with an explicit cursor. OPEN, FETCH & CLOSE do not apply. All cursor attributes apply.

Cursor has to be explicitly defined when a query returns multiple rows to process beyond the first row returned by the query & to keep track of which row is currently being processed.

Declare the cursor to associate its name with a SELECT statement.
Syntax : DECLARE
CURSOR <cursor_name>
IS <regular_select_statement>;
Open the cursor to process the SELECT statement and store the returned rows in the cursor.
Syntax : OPEN <cursor_name>;
Fetch data from the cursor and store it in specified variables.
Syntax : FETCH <cursor_name> INTO <var1, var2...>;
Close the cursor to free up resources. Cursors must be closed before they can be reopened.
Syntax : CLOSE <cursor_name>
Explicit Cursor Attributes are %NOTFOUND, %FOUND, %ROWCOUNT & %ISOPEN.
Reference the current cursor row with the WHERE CURRENT OF statement. The cursor must be declared with a FOR UPDATE OF clause.

Syntax : WHERE CURRENT OF <cursor_name>
Reference Cursors FOR Loops to specify a sequence of statements to be repeated once for each row that is returned by the cursor with the Cursor FOR Loop.
Syntax : FOR <record_name> IN <cursor_name> LOOP
--statements to be repeated go here
END LOOP;

Cursor FOR loops (CFL) are similar to Numeric For Loops(NFL). CFL specify a set of rows from a table using the cursor's name. NFL specify an integer range. CFL record takes on vales of each row. NFL index takes on each value in the range. Record_name is implicitly declared as
record_name cursor_name%ROWTYPE

When a CFL is initiated, an implicit OPEN cursor_name is initiated.

For each row that satisfies the query associated with the cursor, an implicit FETCH is executed into the components of record_name.

When there are no more rows left to FETCH, an implicit CLOSE cursor_name is executed and the loop is exited.
Declare cursors to use parameters
Syntax : DECLARE
CURSOR <cursor_name> [[(param_name param_type)]]
IS <regular select statement>;

Exception Handlers : In PL/SQL, errors are called exceptions. When an exception is raised, processing jumps to the exception handlers. An exception handler is a sequence of statements to be processed when a certain exception occurs. When an exception handler is complete, processing of the block terminates. Two types are Predefined Internal Exceptions which corresponds to approximately 20 common ORACLE errors & Raised automatically by PL/SQL in response to an ORACLE error.

Eg.too_many_rows,no_data_found,invalid_cursor,value_errori.e. arithmetic,numeric,string,conversion or constraint error occurred, zero_divide, dup_val_on_index,cursor_already_open etc.

User-Defined Exceptions must be declared & must be RAISEd explicitly.

Only one handler per block may be active at a time & If an exception is raised in a handler, the search for a handler for the new exception begins in the enclosing block of the current block.

Exception-Init : Exceptions may only be handled by name not ORACLE error number. So, name an ORACLE error so that a handler can be provided specifically for that error.

Syntax : PRAGMA EXCEPTION_INIT (<user_defined_exception_name>, <ORACLE_error_number>);
SQLCODE & SQLERRM provides info on the exception currently being handled & especially useful in the OTHERS handler.

• SQLCODE returns the ORACLE error number of the exception, or 1 if it was a user-defined exception.

• SQLERRM returns the ORACLE error message associated with the current value of SQLCODE & can also use any ORACLE error number as an argument.

• SQLCODE & SQLERRM cannot be used within a SQL statement. If no exception is active SQLCODE = 0 & SQLERRM = 'normal, successful completion'.