PL/SQL Quick Notes

Define PL/SQL:

PL/SQL is a procedural language and it is an extension to Standard SQL. It enables us to execute procedural logic on the database. We use PlSql to perform processing on the server. We also use plsql to create stored procedure and Function.

Advantages of PL/SQL:

PL/SQL is a development tool and it is an extension to Standard SQL. We can also use condition branching, checking and looping in plsql. In Plsql we can send a block of statement to server which reduces network traffic. PlSql provides set of error handling mechanism. PlSql support portability, which means code written in DOS version can run on Unix version.

Explain Exception:

An Exception occurs when unwanted situation arises. The situation can be exceptional to normal function of the program. It can occur due to system error, user error and application error. In PlSql we can anticipate and trap these errors by means of Exception handling codes.

Type of Exception and Pre-defined Oracle Exception and User defined Exception.

Define User Defined Exception:

We use user defined exceptions only when oracle doesn't raise its own exception. In this procedure we raise an exception by using the RAISE command.

What is a Cursor. Define Implicit and Explicit cursors

Oracle engine open a work area for each SQL operation for its internal processinig in order to execute SQL statements. This area is private to SQL operations and it is called a cursor. 

Implicit Cursor: If Oracle enginer has open a cursor for its internal processing then its an Implicit Cursor.

Explicit Cursor: Also known as user defined cursors. When a user open a cursor for processing of the data then it is explicit cursor.

Explain about cursor attribute:

Each cursor or cursor variable has four attributes. FOUND, ISOPEN, NOTFOUND and ROWCOUNT. When appended to the cursor these attributes return useful information about the execution of the data manipulation statement.

What are the restiction of using cursor variables?

PlSql table cannot store cursor variable and a remote subprogram cannot return the value of a cursor variable.

What is a trigger.

A Trigger is a PlSql block that executes when a event occurs. It fires implicitly when a triggering event happens. A trigger never accept arguments. It cannot be used for a SELECT Statement. 

What are the trigger supported in Oracle:

DML Trigger, INSTEAD OF Triggers, DDL Trigger and Database Event Triggers.

DML Trigger: It is defined on a table and fires in response to the event like when a row is inserted to the table, when a row is updated and when a row is deleted.

INSTEAD OF Triggers: This trigeers are created on Views. You can either use INSERT OR UPDATE OR DELETE or all three actions. 

What are triggering attributes:

Triggering attributes are used to catch event when you want to identify to perform certain actions. They are as follows: INSERTING, UPDATING, DELETING.

List the difference between a function and procedure in oracle.

A Function always return a value back to the calling block. 

Explain the difference between GRANT and REVOKE command.

GRANT command is used to allow a user to perform an activity on the database. The REVOKE command disallow a user from perform certain activity.

Explain the difference between ROLLBACK and COMMIT command:

The COMMIT command is used to save the modification done to the database value by the DML commands. 

ROLLBACK command is to undo the changes made by DML commands. This assure value prior to changes is achieved.

Define Row Level Trigger:

Row level trigger is fired each time a row is affected by a DML statement like INSERT, UPDATE OR DELETE. In the case when now row affected the Trigger is not executed at all.

Statement level Trigger:

Statement level Trigger is fired when Statement affect rows in a table but the procesing required is completely independent of the number of rows affected.

Define Join and Its Type:

A join is a query that extract corresponding rows from two or more tables, views or snapshots.

Types: Equijoin, Non-Equijoin, Self Join and Outer Join. 

Equijoin: Information from two or more tables are retrieved by using equality condition. 

Self Join is a join that relate to itself. 

Outer Join: Fetch the row from two tables which matches the join condition and the rows the do not matches the join condition.

What are Packages:

A package is encapsulated collection of related schema objects. It is compiled and then stored in the data dictionary as a schema object. These objects can be procedures, functions, variables, constraints, cursors and exceptions. 

What are VARRAYS:

Variable length array is quite similar to array of c++ or java. It has fixed upper bound size that has to be specified when it is declated. The maximum length is defined in the declaration itself. This can only be used when you know in advance the maximum number of items to be stored.

Explain the Constraints that can be applied to oracle table:

Oracle Constraints are mainly used to maintain integrity of data. The different type of constraints includes CHECK Constraints, NULL constraints, PRIMARY KEY constraints, UNIQUE Constraints. 

Check Constraint: This constraints specify the condition for a row. 

NOT NULL Constraint: This specifies that a column cannot accept NULL values.

PRIMARY KEY Constraints: This constraint ensures that each row is identified by a unique key. 

UNIQUE Constraints: This constraint ensure no column value has repeatative values. 

List out advantages of Views:

Complex queries that need to be executed often can be saved in a view. Views is extremely useful when query uses multiple tables. If tables are changed only the view definition need to be changed. 

Views are visually virtual and occupy no space. They prevent undesire access by providing security as the data that not of interest to user can be left out of the view. A view displays different data for different types of users.

List out guidelines to create a view:

The views column names are same as tables column names. New column name has to be explicitly listed in the CREATE VIEW. New column name should be used while selecting column for a view. ORDER BY clause cannot be used in the definition of a view.

What are LOB Data Type:

The LOB Data Types such as BLOB, CLOB, NCLOB and BFILE can store large block of unstructured data such as graphics, image, video Clip etc. They provide random and piece wise access to the data.

What is the difference between PlSql Triggers and Stored procedures:
A stored procedure can accept parameter while a trigger cannot. A trigger cannot return any value while a Stored Procedure can. A trigger is executed automatically on some event while stored procedure need to be explicitly called. Trigger are used for insertion, updation and deletion on database, while stored procedure are often work independently on the database. 
A Trigger cannot be written in a stored procedure however the reverse is not possible. 

Why do we use cursor variables: 
Cursor variable allow you to pass them as argument to a function which in turn enable you to share results. They allow you to assign content of one cursor to another variable. Using a single cursor variable you can fetch from different result set.

Delete Vs TRUNCATE Vs DROP

• DELETE command is used to remove some or all rows from a table. After performing a DELETE operation you need to perform COMMIT or ROLLBACK a transaction to make the change permanent or undo it. It causes all delete trigger on table to fire. 
• TRUNCATE removes all rows from a table. The operation cannot be rollbacked and no trigger is fired. TRUNCATE is faster and doesn't use as much space as delete.
• DROP command removes a table from the database. All the tables to indexes and privileges will also be removed. No DML Trigger will be fired. The operation cannot be rollbacked.

Q. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
-> Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

Q. What is CYCLE/NO CYCLE in a Sequence?-> CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

Q. What are the advantages of VIEW?- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

Q. Can a view be updated/inserted/deleted? If Yes - under what conditions?
->A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible. Also View should not have GROUP BY, DISTINCT and START WITH clauses in it, for it to be updatable.

Q. What are the data types allowed in a table?
-> CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

Q. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?-> CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

Q. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?->Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.