Home » Interviews questions » oracle PLSQL interview questions

oracle PLSQL interview questions

Here is the compilation of the Most commonly asked oracle plsql interview questions. These will for sure help you in the interview. The questions are from basic to advanced levels and cover most of the aspects of the PLSQL programming. I hope you find this useful

oracle plsql interview questions

Question (1) 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


Question(2) What are the components of a PL/SQL Block?
Answer
Declarative part, Executable part, and Exception part.

Question (3) What are the data types 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.

Question (4) What is a cursor? Why Cursor is required?
Answer
The 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.

Question (5) What is the difference between Procedure and Function?
Answer

FunctionProcedure
The function must return a valueProcedure 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;
The function can be used in SQL with some restrictionThe procedure cannot be called directly from SQL.

Question (6) What is the difference between DELETE and TRUNCATE?
Answer

DeleteTruncate
DELETE is a DML commandTRUNCATE is a DDL command.
Syntax 
Delete * from table
Commit;
Syntax
Truncate table <table name>;
It does not change the high-water markTRUNCATE re-set the memory blocks after execution i.e. it sets the high-water mark of the object
It can be rollbackIt cannot be rollback
Delete trigger are firedNo triggers will be fired
Rollback segments are usedFewer rollback segments are used and it is much faster

Question (7) What is Implicit Cursor and Explicit Cursor?
Answer

See also  What is DB time ,DB cpu Average Active sessions, Active session in oracle
Implicit CursorExplicit 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 cursorExplicit cursor is open, fetch and close

Question (8) What are rowid and rownum?
Answer
Rowid is a hexadecimal unique number for each row of a table that is used for searching.
Rownum is a unique integer number for each row of a table that can be used for sorting or analysis.

Question (9) 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 stored in compiled form in the database
– Functions can be called anywhere in a 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 stored in compiled form in the database
-Procedures are called as stand-alone executable statements:my_proc(parameter1,parameter2…);
Packages-Packages contain functions, 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 the package is persistent for the duration of the user’s session. The data in the 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 of data types of the parameters.

Question (10)  Define Commit, Rollback, and Save-point.

See also  How to use Decode in Oracle

Answer.

CommitCommit is used to make the transaction permanent
Following things happen when a commit is executed
a)All work done by the transaction becomes permanent.
b)Other users can see changes in data made by the transaction.
c)Any locks acquired by the transaction are released.
RollbackRollback is used to undo the transaction.
Following things happen when a rollback is executed
a)All work done by the transaction is undone as if it hadn’t been issued.
b) Any locks acquired by the transaction are released.
SavepointA SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

Question (11) 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.

Question (12) How can you find within a PL/SQL block, if a cursor is open?
Answer
Use the %ISOPEN cursor status variable.

Question (13) 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.

Question (14) 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.

Question (15) What is the maximum number of triggers, you can apply on a single table?
Answer
12 triggers.

See also  All about Apache(Oracle HTTP Server) in EBS 11i/R12

Question (16) What are the two parts of the 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.

Question (17) What is the Overloading of procedures?
Answer
Repeating of same procedure name with a different parameter list

Question (18) What are the modes of parameters that can be passed to a procedure?
Answer
IN, OUT, IN-OUT parameters.

Question (19)What command would you use to encrypt a PL/SQL application?
Answer
WRAP

Question (20) How many types of triggers exist in PL/SQL?
Answer
There are 12 types of triggers in PL/SQL that contain 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.

Question (21) 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.

Question (22) What is stored Procedure?
Answer
A stored procedure is a sequence of statements or a named PL/SQL block that 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 a schema object. It can be nested, invoked, and parameterized.

Question (23) How to execute a stored procedure?
Answer
There is two way to execute a stored procedure.
From the SQL prompt, write EXECUTE or EXEC followed by procedure_name

Question (24) What is Raise_application_error?
Answer
Raise_application_error is a procedure of package DBMS_STANDARD which allows issuing user_defined error messages from stored sub-program or database trigger.

Question (25)  What is Pragma EXECPTION_INIT? Explain the usage?
Answer
The PRAGMA EXCEPTION_INIT tells the compiler 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 questions in the future

Related links

Oracle apps interview questions and answer

oracle dba interview questions

Oracle RAC interview Questions

Weblogic Interview questions

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top