PL/SQL Interview Questions Part 1

PL/SQL Interview Questions Part 1

1.) What is PL/SQL?
 

PL/SQL stands for procedural language extension to SQL. It supports procedural features of programming language and SQL both. It was developed by Oracle Corporation in early of 90's to enhance the capabilities of SQL.

2.) What is PL/SQL table? Why it is used?
 

Objects of type tables are called PL/SQL tables that are modeled as database table. We can also say that PL/SQL tables are a way to providing arrays. Arrays are like temporary tables in memory that are processed very quickly. PL/SQL tables are used to move bulk data. They simplifies moving collections of data.

3.) Mention what PL/SQL package consists of?
 

A PL/SQL package consists of

• PL/SQL table and record TYPE statements
• Procedures and Functions
• Cursors
• Variables ( tables, scalars, records, etc.) and constants
• Exception names and pragmas for relating an error number with an exception
• Cursors

4.) Mention what are the benefits of PL/SQL packages?
 

It provides several benefits like

• Enforced Information Hiding: It OFFERS the liberty to choose whether to keep data private or public
• Top-down design: You can design the interface to the code hidden in the package before you actually implemented the   modules themselves
• Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in   the application. You can modify the package in one module and then reference those changes to another module
• Object oriented design: The package gives developers strong hold over how the modules and data structures inside the   package can be used
• Guaranteeing transaction integrity: It provides a level of transaction integrity
• Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and   enhance the performance of packages.

5.) Explain uses of cursor.
 

Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.

6.) Explain the uses of database trigger.
 

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for:

• Audit data modifications.
• Log events transparently.
• Enforce complex business rules.
• Maintain replica tables
• Derive column values
• Implement Complex security authorizations

7.) What are the data types available in PL/SQL?
 

There are two types of data types in PL/SQL:
• Scalar data types Example are NUMBER, VARCHAR2, DATE, CHAR, and LONG, BOOLEAN etc.
• Composite data types Example are RECORD, TABLE etc.

8.) What is the basic structure of PL/SQL?
 

PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.
PL/SQL block contains 3 sections.

• The Declaration Section (optional)
• The Execution Section (mandatory)
• The Exception handling Section (optional)

9.) What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
 

• Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a   return type in its specification and must return a value specified in that type.
• Procedure: A procedure does not have a return type and should not return any value but it can have a return statement   that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it   is generally similar to a function.
• Package: A package is schema object which groups logically related PL/SQL types, items and subprograms. You can also   say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to   this facility it aids application development. It is used to hide information from unauthorized users.

10.) What is exception? What are the types of exceptions?
 

Error handling part of PL/SQL block is called Exception. They have two types: user_defined and predefined.

11.) How exception is different from error?
 

Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.

12.) What is the main reason behind using an index?
 

Faster access of data blocks in the table.

13.) What is the maximum number of triggers, you can apply on a single table?
 

A maximum of 12 triggers can be applied to one table.

14.) How many types of triggers exist in PL/SQL?
 

There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, and INSERT, UPDATE, DELETE and ALL keywords.

• BEFORE ALL ROW INSERT
• AFTER ALL ROW INSERT
• BEFORE INSERT
• AFTER INSERT etc.

15.) How is a process of PL/SQL compiled?
 

Compilation process includes syntax check, bind and p-code generation processes.

Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.

16.) Differentiate between Syntax and runtime errors?
 

A syntax error can be easily detected by a PL/SQL compiler. For e.g., incorrect spelling.

A runtime error is handled with the help of exception-handling section in an PL/SQL block. For e.g., SELECT INTO statement, which does not return any rows.

17.) Explain Commit, Rollback and Save point?
 

For a COMMIT statement, the following is true:

• Other users can see the data changes made by the transaction.
• The locks acquired by the transaction are released.
• The work done by the transaction becomes permanent.

A ROLLBACK statement gets issued when the transaction ends, and the following is true.

• The work done in a transition is undone as if it was never issued.
• All locks acquired by transaction are released.

It undoes all the work done by the user in a transaction.With SAVEPOINT, only part of transaction can be undone.

18.) Differentiate between % ROWTYPE and TYPE RECORD?
 

• % ROWTYPE is used when a query returns an entire row of a table or view.
• TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.
 

19.) Define Implicit and Explicit Cursors?
 

A cursor is implicit by default. The user cannot control or process the information in this cursor.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.

20.) Explain mutating table error?
 

It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.

21.) When is a declare statement required?
 

DECLARE statement is used by PL/SQL anonymous blocks such as with stand-alone, non-stored procedures. If it is used, it must come first in a stand-alone file.

22.) What is stored Procedure?
 

A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.

23.) What are the advantages of stored procedure?
 

Modularity, extensibility, reusability, Maintainability and one time compilation.

24.) What are the cursor attributes used in PL/SQL?
 

• %ISOPEN: it checks whether the cursor is open or not.
• %ROWCOUNT: returns the number of rows affected by DML operations: INSERT, DELETE, UPDATE, SELECT.
• %FOUND: it checks whether cursor has fetched any row. If yes - TRUE.
• %NOTFOUND: it checks whether cursor has fetched any row. If no - TRUE.

25.) What is consistency?
 

Consistency simply means that each user sees the consistent view of the data.

Consider an example: there are two users A and B. A transfers MONEY to B's account. Here the changes are updated in A's account (debit) but until it will be updated to B's account (credit), till then other users can't see the debit of A's account. After the debit of A and credit of B, one can see the updates. That’s consistency.

26.) What is the importance of SQLCODE and SQLERRM?
 

SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.

27.) If a cursor is open, how can we find in a PL/SQL Block?
 

The %ISOPEN cursor status variable can be used.

28.) What packages are available to PL/SQL developers?
 

DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.

29.) Explain polymorphism in PL/SQL?
 

Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package. Unambiguous logic must be avoided whilst overloading is being done.

30.) Mention what are different methods to trace the PL/SQL code?
 

Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes

• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION and DBMS_MONITOR
• trcsess and tkproof utilities

--------------------------------
Prepared by: V. Raga Malika

0 comments:

Post a Comment