Programming Solutions

Exception Handling:- PL/SQL provides a features to handle the exceptions which occurs in a PL/SQL block known as exception handling using exception handling we can test the code and avoid it from exiting abruptly It’s is called Exception handling. When an exception occurs a messages which explain its cause is received PL/SQL exception massage consists of two parts. Types of exception 1) System exception 2) User-defined exception Syntax:- EXCEPTION When exceptionname then --code to handle exception. 1) System Exception: ==> System Exception can be further divided into two parts: 1. Named exceptions 2. Numbered exceptions 1) Named exception: ==> Particular name given to some common system exceptions is known as Named Exceptions. ==> Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer. ==> Oracle has def...

Cursor :- ==> A cursor is a temporary work area created in the system memory when a SQL statement is executed. ==> The Data that is Stored in the cursor is called the Active Data Set. As this data is stored due to execution of some SQL statement, it is also called Result set. Types of Cursors:- 1. Implicit Cursor 2. Explicit Cursor 1. Implicit Cursor:- These 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 Oracle performs following operation to manage an implicit cursor ==> Reserves an area in memory to store data required to execute SQL Statement. ==> Occupy this area with required data. ==> Processes data. ==> Frees memory area by closes a cursor, When processing is completed. The syntax to use attributes of implicit cursor:- SQL%AttributeName The Value of the cursor attribute always refers to the SQL...

Index :- An index is an ordered list of contents of the column (or a group of column) of a table. An index is similar to a table. It contains at-least two columns: 1. A column having sorted data on which an index is created, and 2. A column representing ROWID for each row in a table. Types of Indexes: 1. Duplicate Indexes. 2. Unique Indexes. 3. Simple Indexes. 4. Composite Indexes. Unique Index Duplicate Index 1. An index that does not allow duplicate values for indexed column, is called a Unique Index. 1. An index that allows duplicate values for indexed column is called Duplicate Index. 2. A Unique Index created automatically for a table, if contains a primary key or Unique key. 2. A Duplicate Index is not created on primary key and Unique key because it contains Duplicate values. Simple Indexes: An index created on single column of a table is called a Simple Index. Composite Indexes: An index created on more than one column is called Comp...

Authentication Authentication means verifying the identity of a user, device, or other entity who wants to use data, resources, or applications. Validating this identity establishes a trust relationship for further interactions. Authentication also enables accountability by making it possible to link access and actions to specific identities. After authentication, authorization processes can allow or limit the levels of access and action permitted to that entity. You can authenticate both database and non database users for an Oracle database. For simplicity, the same authentication method is generally used for all database users, but Oracle Database allows a single database instance to use any or all methods. Oracle Database requires special authentication procedures for database administrators, because they perform special database operations. Oracle Database also encrypts passwords during transmission to ensure the security of network authentication. Minimum Requirements for...

Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table. Constraints can be divided into the following two types, 1. Column level constraints: Limits only column data. 2. Table level constraints: Limits whole table data. Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table. 1. NOT NULL 2. UNIQUE 3. PRIMARY KEY 4. FOREIGN KEY 5. CHECK 6. DEFAULT 1. NOT NULL Constraint NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value. Note: - One important point to note about this constraint is that it cannot be defined at table level. 2. UNIQUE Constraint UNIQUE constraint ensures that a field or column will only have unique values. A ...

Reports are designed in SQL *PLUS to display data other than table format and they are prepared using a select statement. 1. TTITLE – The top title of your report – Can span several lines, depending on how complex you make it. Syntax – TTITLE [left]{text} [right]{text} [center]{text} Here in above syntax [] represent optional value i.e. alignment of text, by default it is left alignment and {} represent compulsory value. i.e. text to be displayed. 2. BTITLE – The bottom title of your report. Syntax – BTITLE [left]{text} [right]{text} [center]{text} Here in above syntax [] represent optional value i.e. alignment of text, by default it is left alignment and {} represent compulsory value. i.e. text to be displayed. 3. COLUMN - Specify display attributes for each column. Syntax – COLUMN {columnname} [Format] {format instruction} [Justify] {justification} [Heading] {text} Here in above syntax Format option useful ...