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
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
| Function | Procedure |
| The 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; |
| The function can be used in SQL with some restriction | The procedure cannot be called directly from SQL. |
Question (6) 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 | Fewer rollback segments are used and it is much faster |
Question (7) 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 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.
Answer.
| Commit | Commit 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. |
| Rollback | Rollback 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. |
| Savepoint | 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?
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.
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
Hope you like the compilation of oracle plsql interview questions. Please do provide the feedback