Pl sql interview questions

adminserver Other Technologies

Top most important pl sql interview questions and answers by Experts:

Here is a list of Top most important pl sql interview questions and answers by Experts.If you want to download pl sql interview questions pdf free ,you can register with RVH techguru. Our experts prepared these pl sql interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in pl sql ,Register for pl sql online training here.

 

1.What special operators does Oracle provide for dealing with NULLs?

NVL – Converts a NULL to another specified value, as in:

my_var := NVL (your_var, ‘Hello’);

IS NULL and IS NOT NULL

You can use this syntax to check specificaly to see if a variable’s value is NULL or NOT NULL.

2.Explain three different rules that apply to NULLs when doing comparisons?

1. For all operators except for concatenation (||), if a value in an expression is a NULL, that expression evaluates to NULL

2. NULL is never equal or not equal to another value

3. NULL is never TRUE or FALSE

3.What command would you use to encrypt a PL/SQL application?

WRAP

4.Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function has a return type in its specification and must return a value specified in that type. A procedure does not have a return type in its specification and should not return any value, but it can have a return statement that simply stops its execution and returns to the caller.

5.What steps are included in the compilation process of a PL/SQL block?

The compilation process includes syntax checking, binding, and p-code generation. Syntax checking involves checking PL/SQL code for compilation errors. After syntax errors have been corrected, a storage address is assigned to the variables that are used to hold data for Oracle. This process is called binding. Next, p-code is generated for the PL/SQL block. P-code is a list of instructions to the PL/SQL engine. For named blocks, p-code is stored in the database, and it is used the next time the program is executed.

6.How does a syntax error differ from a runtime error?

A syntax error can be detected by the PL/SQL compiler. A runtime error occurs while the program is running and cannot be detected by the PL/SQL compiler.

A misspelled keyword is an example of a syntax error. For example, this script:

BEIN
DBMS_OUTPUT.PUT_LINE (‘This is a test’);
END;

contains a syntax error. Try to find it.

A SELECT INTO statement returning no rows is an example of a runtime error. This error can be handled with the help of the exception-handling section of the PL/SQL block.

Define Commit, Rollback and Savepoint.

When a COMMIT statement is issued to the database, the transaction has ended, and the following results are true:

. All work done by the transaction becomes permanent.

. Other users can see changes in data made by the transaction.

. Any locks acquired by the transaction are released.

When a ROLLBACK statement is issued to the database, the transaction has ended, and the following results are true:

. All work done by the transaction is undone, as if it hadn’t been issued.

. Any locks acquired by the transaction are released.

The ROLLBACK statement undoes all the work done by the user in a specific transaction. With the SAVEPOINT command, however, only part of the transaction can be undone.

7.Explain Implicit and Explicit cursors

Oracle automatically declares an implicit cursor every time a SQL statement is executed. The user is unaware of this and cannot control or process the information in an implicit cursor.

The program defines an explicit cursor for any query that returns more than one row of data. This means that the programmer has declared the cursor within the PL/SQL code block. This declaration allows the application to sequentially process each row of data as the cursor returns it.

8.How an Implicit cursor works?

1. Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.
2. A cursor is automatically associated with every DML (data manipulation) statement (UPDATE, DELETE, INSERT).
3. All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
4. An INSERT statement needs a place to receive the data that is to be inserted into the database; the implicit cursor fulfills this need.
5. The most recently opened cursor is called the SQL cursor.

9.How an Explicit cursor works?

The process of working with an explicit cursor consists of the following steps:

1. Declaring the cursor. This initializes the cursor into memory.

2. Opening the cursor. The declared cursor is opened, and memory is allotted.

3. Fetching the cursor. The declared and opened cursor can now retrieve data.

4. Closing the cursor. The declared, opened, and fetched cursor must be closed to release the memory allocation.

10.What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

11.What is the maximum number of triggers, can apply to a single table?

12 triggers.

12.What is a mutating table error and how can you get around it?

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

13.What packages (if any) has Oracle provided for use by developers?

Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

Describe the use of PL/SQL tables

PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

14.When is a declare statement needed?

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

In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?

OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

15.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

16.How can you find within a PL/SQL block, if a cursor is open?

Use the %ISOPEN cursor status variable.

17.How can you generate debugging output from PL/SQL?

Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

18.What are the types of triggers?

There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.

19.How can I define a two-dimensional array of numbers in PL/SQL?

Although PL/SQL does not natively support the declaration and manipulation of multidimensional arrays, you can emulate these structures using nested collection definitions, which were first supported in Oracle9i Database Release 1.

Here is a brief example to get you started and introduce you to some of the challenges you may encounter as you use collections in this way.

First, create a collection of associative arrays.

CREATE OR REPLACE PACKAGE twodim_aa
IS
TYPE data_t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;

TYPE array_t IS TABLE OF data_t
INDEX BY PLS_INTEGER;
END twodim_aa;
/

The first, inner collection—data_t—contains the data for each cell in the two-dimensional array. Each row in the outer collection—array_t—contains a collection of the first type.

Now declare a variable based on that outer collection type —array_t—, which will serve as a two-dimensional array. In the following script, I declare such a collection—

DECLARE
l_2d_grid twodim_aa.array_t;

—and then assign values to three cells: (1,1), (1,2), and (200,206). Notice that the syntax is different from that used in traditional array cell specification, namely: (1)(1), (1)(2), and (200)(206). Also, since I am using associative arrays to define my two-dimensional array, I do not have to specify a size for this two-dimensional array.

DECLARE
l_2d_grid twodim_aa.array_t;
BEGIN
l_2d_grid (1) (1) := 100;
l_2d_grid (1) (2) := 120;
l_2d_grid (200) (206) := 200;

IF l_2d_grid (1)(2)

20.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.

21. 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.

22. What are the datatypes available in PL/SQL?
There are two types of datatypes in PL/SQL:
1. Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
2. Composite datatypes Example are RECORD, TABLE etc
23. 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.
1. The Declaration Section (optional)
2. The Execution Section (mandatory)
3. The Exception handling Section (optional)

24. 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.

25.What is exception? What are the types of exceptions?
Exception is an error handling part of PL/SQL. There are two type of exceptions: pre_defined exception and user_defined exception.
26.How exception is different from error?
Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.

27.What is the main reason behind using an index?
Faster access of data blocks in the table.

28. What are PL/SQL exceptions? Tell me any three.
1. Too_many_rows
2. No_Data_Found
3. Value_error
4. Zero_error etc.

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

30.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, INSERT, UPDATE, DELETE and ALL keywords.
• BEFORE ALL ROW INSERT
• AFTER ALL ROW INSERT
• BEFORE INSERT
• AFTER INSERT etc.

31. 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.

32.How to execute a stored procedure?
There are two way to execute a stored procedure.
From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.
1. EXECUTE or [EXEC] procedure_name;
Simply use the procedure name
1. procedure_name;

33. What are the advantages of stored procedure?
Modularity, extensibility, reusability, Maintainability and one time compilation.

34. 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.

35. 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.

36. What is cursor and why it is required?
A cursor is a temporary work area created in a system memory when an SQL statement is executed.
A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.

37. How many types of cursors are available in PL/SQL?
There are two types of cursors in PL/SQL.
1. Implicit cursor, and
2. explicit cursor

38.What is a cursor? Define explicit and implicit cursor.
The oracle engine opens a work area for each SQL’s operations for its internal processing in order to execute SQL statements. This area is private to SQL’s operations and is called as a cursor.

Implicit cursor – If the oracle engine has opened a cursor for its internal processing, then it is implicit cursor.

Explicit cursor – It is also known as user defined cursor. When a user opens a cursor for processing data, the cursor is explicit cursor.
39.Explain about the cursor attributes.
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 a data manipulation statement.
40.What are the restrictions of using cursor variables?
-PL/SQL tables cannot store cursor variables.
-Remote subprogram cannot return the value of a cursor variable.
41.What is a trigger in PLSQL?
A trigger is a PLSQL block that is executed whenever an event occurs. It fires implicitly whenever the triggering event happens, a trigger never accepts argument. A trigger cannot be used for a SELECT statement.
42.What are the triggers supported in oracle?
-DML triggers
-Instead of triggers
-DDL triggers
-Database event triggers

DML triggers

It is defined on a table and fires in response to an event like

– When a row is inserted to a table
– When a row is updated
– When a row is deleted

Instead of trigger

This trigger is 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 or to perform certain actions.

They are as follows:

Inserting
Updating
Deleting
43.What is the difference between a function and a procedure in oracle?
A function always returns a value back to the calling block.
44.What are packages?
A package is an encapsulated collection of related schema objects. A package is compiled and then stored in the database’s data dictionary as a schema objects. These objects can be procedure, functions, variables, constants, cursors and exceptions.
45.Explain the difference between GRANT and REVOKE command.
GRANT command is used to allow a user to perform certain activities on the database. The REVOKE command disallows the user from performing certain activities.
Explain the difference between ROLLBACK and COMMIT commands.
The COMMIT command is used to save the modifications done to the database values by the DML commands.

ROLLBACK command is used to undo the changes made by the DML commands. This ensures the values that existed prior to the changes can be achieved.
46.Define Row level trigger.
Row level trigger is fired each time a row is affected by DML statements like Insert, Update and Delete. When no rows affected, the trigger is not executed at all.
47.Define Statement level triggers.
It is fired when statement affects rows in a table but the processing required is completely independent of the number of rows affected.
48.Define Joins and its types.
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.