Most commonly asked 25 oracle plsql interview questions

Here  is the compilation of Most commonly asked oracle plsql interview questions

oracle plsql interview questions

Q1) What is Oracle plsql?

Answer
PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural programming language
Q2) What are the components of a PL/SQL Block?
Answer
Declarative part, Executable part and Exception part.

Q3) What are the datatypes a available in PL/SQL?

Answer

Some scalar data types such as
NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.

Q4) What is a cursor? Why Cursor is required?

Answer
Cursor is a named private SQL area from where information can be accessed.
Cursors are required to process rows individually for queries returning multiple rows.

Q5) What is the difference between Procedure and Function ?

Answer

Function Procedure
Function must return a value Procedure need not
Syntax 

FUNCTION name (argument list …..) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;

 

 

Syntax

PROCEDURE name (parameter list…..)
is
local variable declarations

BEGIN
Executable statements.
Exception.
exception handlers

end;

Function can be used in SQL with some restriction Procedure cannot be called directly from SQL.

Q6) What is the difference between DELETE and TRUNCATE ?

Answer

Delete Truncate
DELETE is a DML command TRUNCATE is a DDL command.
Syntax 

Delete * from table

Commit;

 

Syntax

Truncate table <table name>;

It does not change the high-water mark TRUNCATE re-set the memory blocks after execution i.e. it sets the high-water mark of the object
It can be rollback It cannot be rollback
Delete trigger are fired No triggers will be fired
Rollback segments are used Less rollback segments are used and it is much faster

Q7) What is Implicit Cursor and Explicit Cursor ?

Answer

Implicit Cursor Explicit Cursor
Implicit Cursor is defined and controlled by Oracle Internally. Explicit Cursor is defined and controlled programatically.
Syntax 

declare

v_dept varchar2(50);

begin

select dept into v_dept from dept where location = ‘XYZ’;

end;

Syntax

declare

v_dept varchar2(50);

Cursor Cur_dept is select dept into v_dept from dept where location = ‘XYZ’;

begin

Open Cur_dept;

Fetch Cur_dept into v_dept;

Close Cur_dept;

end;

All select/update query used in PL/SQL block is an implicit cursor Explicit cursor are open, fetch and closed

 

Q8) What is rowid and rownum?

Answer

Rowid is a hexa decimal unique number for each row of a table which is used for searching.

Rownum is a unique integer number for each row of a table which can be used for sorting or analysis.

Q9) Explain the difference between a FUNCTION, PROCEDURE and PACKAGE

Answer

Function -A function has a return type in its specification and must return a value specified in that type.

-They are are stored in compiled form in database

 

– Functions can be called anywhere in an valid expression

Select fnd_profile.value(‘xyz)  from dual;

 

Procedure -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

 

-They are are stored in compiled form in database

-Procedures are called as stand-alone executable statements:

 

my_proc(parameter1,parameter2…);

Packages -Packages contain function, procedures and other data structures.

– The advantages of packages are Modularity, Easier Application Design, and Information.

Hiding, Reusability and Better Performance.

 

-There are a number of differences between packaged and non-packaged PL/SQL programs.

Package The data in package is persistent for the duration of the user’s session. The data in package thus exists across commits in the session.

 

-If you grant execute privilege on a package, it is for all functions and procedures and data structures in the package specification. You cannot grant privileges on only one procedure or function within a package.

-You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct program to be called is decided at runtime, based on the number or datatypes of the parameters.

Q10)  Define Commit, Rollback and Save-point.

Answer.

Commit Commit is used to make the transaction permanent

 

Following things happens when commit is executed

 

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

Rollback Rollback is used to undo the transaction.

Following things happens when rollback is executed

 

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

 

. Any locks acquired by the transaction are released.

Save point A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

 

Q11) What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

Answer

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.

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

Answer

Use the %ISOPEN cursor status variable.

Q13) How can you generate debugging output from PL/SQL?

Answer

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.
Q14) What are PL/SQL exceptions?
Answer

Some of them are

1.Too_many_rows
2.No_Data_Found
3.Value_error
4.Zero_error etc.
Q15) What is the maximum number of triggers, you can apply on a single table?

Answer

12 triggers.

Q16) What are two parts of package ?

Answer

The two parts are package specification and package body

They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures. The package body contains the declaration, executable, and exception handling sections of all the bundled procedures and functions.
Q17) What is Overloading of procedures ?

Answer

Repeating of same procedure name with different parameter list
Q18) What are the modes of parameters that can be passed to a procedure ?

Answer

IN, OUT, IN-OUT parameters.

Q19)What command would you use to encrypt a PL/SQL application?

Answer

WRAP
Q20) How many types of triggers exist in PL/SQL?

Answer

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.

Q21) Name the tables where characteristics of Package, procedure and functions are stored?
Answer

dba_objects, dba_Source and dba_error.

or

user_objects,user_Source and user_error.
Q22) What is stored Procedure?

Answer

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.
Q23) How to execute a stored procedure?

Answer

There are two way to execute a stored procedure.

From the SQL prompt, write EXECUTE or EXEC followed by procedure_name

Q24) What is Raise_application_error?

Answer

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

Q25)  What is Pragma EXECPTION_INIT? Explain the usage?
Answer

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Hope you like this compilation of Oracle PlSQL interview questions. I will be posting more such question in the future

Related links

29 Oracle apps interview questions and answer

Top 40 oracle dba interview questions and answers

19 Oracle Apps Technical Interview question

40 Important Weblogic Interview questions

Hope you like the compilation of oracle plsql interview questions. Please do provide the feedback