| What is Savepoint? |
| Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction. |
| What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ? |
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package. |
| What are the various types of Exceptions ? |
User defined and Predefined/System defined Exceptions. |
| What are the various types of database triggers ? |
| There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers. Before and After Triggers. Row and Statement Triggers. |
| What are the advantages/disadvantages of clusters ? |
| advantages : Access time reduced for joins. disadvantages : The time for Insert increases. |
| What is a forward declaration ? What is its use ? |
| PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declaration consists of a subprogram specification terminated by a semicolon. |
| What are the types of Notation ? |
| Position, Named, Mixed and Restrictions. |
| If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ? |
| Yes |
What is the advantage of using packages for storing PL/SQL objects? |
Packages offer several advantages when you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. |
Define exception and How will you raise an exception in PL/SQL procedure? |
In PL/SQL, a error condition is called an exception. Exceptions can be system defined or user defined. Examples of system defined exceptions include division by zero and out of memory. Some common system exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The following example illustrates the scope rules: DECLARE due EXCEPTION; BEGIN RAISE due; EXCEPTION WHEN due THEN Null; END; |
What is the difference between Procedure and Function? |
Differences between Functions and Procedures -Functions are normally used for computations where as procedures are normally used for executing business logic. -Functions must return a value (using the RETURN keyword), but for stored procedures this is not compulsory. Procedures can use RETURN keyword but without any value being passed. -Functions could be used in SELECT statements, but they should don't do any data manipulation. However, procedures cannot be included in SELECT statements. -Functions could be used in creating "Function Based Indexes" to improve the performance ,but procedure not. |