PL/SQL interview questions & material

1)What is the basic structure of PL/SQL?

Ans :PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
 
2)Explain the two types of Cursors?

Ans :There are two types of cursors, Implict Cursor and Explicit Cursor.PL/SQL uses Implict Cursors for queries.User defined cursors are called Explicit Cursors. They can be declared and used.

3)What are the datatypes available in PL/SQL?

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

4)What is the difference between a procedure and a function ?

Ans: Functions return a single variable by value whereas procedures do not return any variable by value.Rather they return multiple variables by passing variables by reference through their OUT parameter.

5)What are the various types of database triggers ?

Ans: There are 12 types of triggers, they are combination of :

Insert, Delete and Update Triggers. 
Before and After Triggers. 
Row and Statement Triggers.

6)What is the significance of the & and && operators in PL SQL ?

Ans: The & operator means that the PL SQL block requires user input for a variable.The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable

7)Differentiate between % ROWTYPE and TYPE RECORD.

Ans :% 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.

Eg.  TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)

e_rec smp ROWTYPE

Cursor c1 is select smpno,dept from smp;

e_rec c1 %ROWTYPE

8)Explain the uses of database trigger.

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

1)Audit data modifications.

2)Log events transparently.

3)Enforce complex business rules.

4)Maintain replica tables

5)Derive column values

6)Implement Complex security authorizations

9)Show how functions and procedures are called in a PL/SQL block.

Ans : Function is called as a part of an expression.

total:=calculate_sal(‘b644’)

Procedure is called  as a statement in PL/SQL.

calculate_bonus(‘b644’);

10)How is a process of PL/SQL compiled?

Ans : 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.

11)Show the cursor attributes of PL/SQL.

Ans : 
%ISOPEN : Checks if the cursor is open or not

%ROWCOUNT : The number of rows that are updated, deleted or fetched.

%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched

%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.

12)Differ between DECODE and CASE.

Ans : DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.

select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);

This statement returns an error.

CASE is directly used in PL/SQL, but DECODE is used in PL/SQL through SQL only.

13)Explain polymorphism in PL/SQL.

Ans : 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.

14)Explain SPOOL

Ans : Spool command can print the output of sql statements in a file.

spool/tmp/sql_outtxt

select smp_name, smp_id from smp where dept=’accounts’;

spool off;

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

Ans : 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.

16)Explain how exception handling is done in advance PL/SQL?

Ans : For exception handling PL/SQl provides an effective plugin PLVexc.  PLVexc supports four different exception handling actions.
Continue processing
Record and then continue
Halt processing
Record and then halt processing
For those exceptions that re-occurs you can use the RAISE statement.

17)Explain how can you save or place your msg in a table?

Ans : To save msg in a table, you can do it in two ways
Load individual messages with calls to the add_text procedure
Load sets of messages from a database table with the load_from_dbms procedure
 
18)Define Joins and its types.

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

Types:
  • Equi-joins 
  • Non-equi joins
  • Self joins
  • Outer joins
Equi-join - information from two or more tables are retrieved by using equality conditions.

Self joins - Self join is a join that relates to itself.

Outer joins - Outer join fetch the rows from two tables which matches the join condition and the rows which don't match the join condition.

19)What is the difference between PRIMARY KEY and UNIQUE KEY constraints ?

Ans :
  • UNIQUE KEY columns can have null values but PRIMARY KEY column cannot accept null values.
  •  A table can have only one PRIMARY KEY column but many UNIQUE KEY columns allowed.

20)What is the difference between DELETE and TRUNCATE ?

Ans:

a) DELETE is a DML command and TRUNCATE is a DDL command.

b) TRUNCATE re-set the memory blocks after execution and much faster than DELETE in most of the circumstances.

21)When do you use Ref Cursors? 

Ans :We base a query on a ref cursor when you want to: 
  • More easily administer SQL 
  • Avoid the use of lexical parameters in your reports 
  • Share data sources with other applications, such as Form Builder 
  • Increase control and security 
  • Encapsulate logic within a subprogram 

22)What are mutating tables? And what is mutating error? 

Ans : A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. 
A mutating error occurs when a trigger which fires when updation/deletion/insertion is done on a table A performs insertion/updation/deletion on the same table A. This error results in an infinite loop which is termed as a mutating error

23)What is Commit, Rollback and Save point? 

Ans :
Commit : Makes changes to the current transaction permanent. It Erases the savepoints and releases the transaction locks. 
Savepoint :Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions. 
Rollback :This statement is used to undo work. 

24)What are Exceptions? How many types of Exceptions are there? 

Ans :Exceptions are conditions that cause the termination of a block. There are two types of exceptions 
Pre-Defined : Predefined by PL/SQL and are associated with specific error codes. 
User-Defined : Declared by the users and are rose on deliberate request. (Breaking a condition etc.) 
Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised. 

25)What is SQL Tuning? How does it work? 

Ans :  SQL tuning is the process of ensuring that the SQL statements that an application will issue will run in the fastest possible time

26)What are ORACLE PRECOMPILERS?

Ans : Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,... This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql statements into calls to the precompiler runtime library. The output must be compiled and linked with this library to creator an executable.

27)What is the purpose of a cluster?

Ans : Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

28)What is OCI. What are its uses?

Ans : Oracle Call Interface is a method of accesing database from a 3GL program. Uses--No precompiler is required,PL/SQL blocks are executed like other DML statements.
The OCI library provides
  • functions to parse SQL statemets
  • bind input variables
  • bind output variables
  • execute statements
  • fetch the results

29)Difference between NO DATA FOUND and %NOTFOUND?

Ans : NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.



SelectionFile type iconFile nameDescriptionSizeRevisionTimeUser
Ċ
View Download
PL/SQL Material, ORACLE pl/sql material PDF   247k v. 1 Mar 13, 2015, 6:51 AM Suresh Reddy
Ċ
View Download
PL/SQL Material, ORACLE pl/sql material PDF   310k v. 2 Mar 13, 2015, 6:52 AM Suresh Reddy
Ċ
View Download
PL/SQL Material, ORACLE pl/sql material PDF   587k v. 1 Mar 13, 2015, 6:52 AM Suresh Reddy
Ċ
View Download
PL/SQL Material, ORACLE pl/sql material PDF   541k v. 1 Mar 13, 2015, 6:52 AM Suresh Reddy
Ċ
View Download
PL/SQL Material, ORACLE pl/sql material PDF   613k v. 1 Mar 13, 2015, 6:52 AM Suresh Reddy
Ċ
View Download
PL/SQL Material, ORACLE pl/sql material PDF   529k v. 1 Mar 13, 2015, 6:52 AM Suresh Reddy