Here is the compilation of Most commonly asked oracle plsql interview questions. These will for sure help you in the interview. The questions are from basic to advanced level and covers most of the aspects of the PLSQL programming. I hope you find this useful
Question (1) What is Oracle PLSQL?
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?
Declarative part, Executable part and Exception part.
Question (3) What are the datatypes a available in PL/SQL?
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?
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 ?
|Function must return a value||Procedure need not|
FUNCTION name (argument list …..) Return datatype is
local variable declarations
PROCEDURE name (parameter list…..)
local variable declarations
|Function can be used in SQL with some restriction||Procedure cannot be called directly from SQL.|
Question (6) What is the difference between DELETE and TRUNCATE ?
|DELETE is a DML command||TRUNCATE is a DDL command.|
Delete * from table
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|
Question (7) What is Implicit Cursor and Explicit Cursor ?
|Implicit Cursor||Explicit Cursor|
|Implicit Cursor is defined and controlled by Oracle Internally.||Explicit Cursor is defined and controlled programatically.|
select dept into v_dept from dept where location = ‘XYZ’;
Cursor Cur_dept is select dept into v_dept from dept where location = ‘XYZ’;
Fetch Cur_dept into v_dept;
|All select/update query used in PL/SQL block is an implicit cursor||Explicit cursor are open, fetch and closed|
Question (8) What is rowid and rownum?
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.
Question (9) Explain the difference between a FUNCTION, PROCEDURE and PACKAGE
|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.
Question (10) Define Commit, Rollback and Save-point.
|Commit||Commit is used to make the transaction permanent|
Following things happens when 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.
|Rollback||Rollback is used to undo the transaction.|
Following things happens when 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.
|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.|
Question (11) 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.
Question (12) How can you find within a PL/SQL block, if a cursor is open?
Use the %ISOPEN cursor status variable.
Question (13) 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.
Question (14) What are PL/SQL exceptions?
Some of them are
Question (15) What is the maximum number of triggers, you can apply on a single table?
Question (16) What are two parts of package ?
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 Overloading of procedures ?
Repeating of same procedure name with different parameter list
Question (18) What are the modes of parameters that can be passed to a procedure ?
IN, OUT, IN-OUT parameters.
Question (19)What command would you use to encrypt a PL/SQL application?
Question (20) 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
◦AFTER INSERT etc.
Question (21) Name the tables where characteristics of Package, procedure and functions are stored?
dba_objects, dba_Source and dba_error.
user_objects,user_Source and user_error.
Question (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.
Question (23) 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
Question (24) What is Raise_application_error?
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.
Question (25) What is Pragma EXECPTION_INIT? Explain the usage?
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 question in the future
Hope you like the compilation of oracle plsql interview questions. Please do provide the feedback