The job duties of a PL/SQL developer are very much the same as programmers in non-database environments, but PL/SQL programmers must know more than the PL/SQL language syntax. A PL/SQL programmer’s job duties may involve Oracle-centric concepts, such as writing triggers, Oracle stored procedures and advanced PL/SQL communications with Oracle.
Take help from these top 10 Oracle PL/SQL Interview Questions highly asked in job interviews. These questions are picked from the book, Oracle PL/SQL Interview Questions You’ll Most Likely Be Asked
1: Differentiate between Decode and Case.
Decode and Case are two conditional statements used in PL/SQL.
To differentiate both case and Decode:
a) Case can work only with all other conditional operators except ‘=’, while Decode can only work with an equal conditional operator.
b) Case can work with searchable subqueries and predicates, while decode works only scalar values.
c) A Case statement can be effectively substituted as If-then-else statement as well as a parameter to any procedure call, but Decode can only be used inside the function.
d) Case and Decode handles Null values differently
e) Case is ANSI compatible while Decode is not.
f) Case cannot handle data type inconsistency.
g) Decode statements are easily readable as compared to Case.
2: How many rows will the following SQL return?
Select * from emp Where rownum < 10;
A maximum of 9 rows will be returned for the SQL Select * from emp Where rownum < 10. Rownum is the number of a particular row and it increases by one with each row selected. If there is an order by or another where condition or subquery based on which the rows are selected, the rownum applies to the selected number of rows as per the conditions and sequence. So even if the given query actually fetches more than 10 records, only the first 9
records are returned.
3: What are the Unnamed exceptions? Explain.
Unnamed exceptions are system errors that are not as common as the named exceptions. They have error codes but it might get difficult to remember specific error codes to handle them all. Instead, the ‘When Others’ section of Exception handling is used to handle such unnamed system errors. Or else, if you know the error code, you can create a user-defined exception and handle that with the help of the Pragma Exception_Init.
4: What is Collection in Oracle?
A collection is a Composite datatype in Oracle that stores multiple values of the same type. Collections can be used to store records from a dataset, strings, dates etc. A Collection is always stored sequentially or will be ordered. You can access any element of a collection using its index. The main characteristics of a collection are:
a) It is an ordered list
b) It contains the same datatypes
c) Any element can be accessed using its index or position in the collection.
5: What are Pseudocolumns?
Pseudocolumns are actually system functions that can be used without arguments and hence they appear just like columns. You can include them in the SELECT statement just like a table column. Some examples of pseudocolumns are:
RowID – gives the unique RowID assigned by Oracle internally
RowNum – gives the number of the record or row in the dataset
NextVal – returns the next value from a sequence
Sysdate – returns the system date
6: What is an Explicit cursor?
When you explicitly assign the resultset of a select statement into a variable, it is done using an explicit cursor. It is best used when your resultset is sure to contain many records. Otherwise, it will become a burden on the memory. To create an explicit cursor, you can use the Cursor – IS statement. This statement only defines the cursor which has to be explicitly opened, fetched, traversed and closed.
7: What is the use of AUTHID Clause in Create Procedure or Function?
The AUTHID clause is added in a Create Procedure or Create Function command to authorize a particular user to execute the same. By default, the user who has created the Procedure or Function is only authorized to execute the same. If you want to authorize other users to execute the Procedure or Function you have created, you have to specify those users’ user id along with the AUTHID option.
8: What is the difference between Inner Join and Outer Join?
An Inner Join retrieves the records only where the join condition meets. The outer join does not require any join condition to be satisfied. Inner Join has only one type which specifies the related columns to join. Outer Joins can be done in three ways – Left Outer, Right Outer and Full Outer. Left Outer Join retrieves all records from the first table and the related records in the second table. Right Outer Join retrieves all records from the second table and the related records from the first table. Full Outer Join retrieves all records from both tables irrespective of any relation.
9: How to invoke a Trigger on demand?
It is not possible to invoke a trigger on demand. They are automatically invoked by the Oracle server whenever the data in a table or view is changed. When we create a trigger, we specify which event is connected to the trigger like before or after insert / update or delete on a specific table and whether it should be fired for every row or for the entire batch processed. Whenever that particular even happens on the table, the trigger is automatically invoked. Triggers are perfect for maintaining logs / audit data and to check for authorization on specific events on specific tables.
10: What is the use of CACHE and NO CACHE while creating a sequence?
The CACHE keyword instructs the database to keep the last ‘n’ number of sequential values in the cache memory so that they can be easily retrieved. So CACHE 20 would mean that the last 20 values of the sequence are in the cache memory which would make the job of NEXTVAL very simple. One issue with this option is that in case a memory crash occurs, the sequences that are not being used also will be lost resulting in a gap and inconsistent data. When the system recovers from the crash, Oracle will assign new values in the sequence based on the last committed sequence value in the database. When you specify NO CACHE, the system does not store any value in cache memory. This might make the particular program a little slow but it is definitely more reliable than having a gap if memory crashes.
Appearing for an interview well prepared to take on any questions that may come can surely elevate your chances of being selected. Our book Oracle PL/SQL Interview Questions You’ll Most Likely Be Asked will guide you through the most likely asked questions and their answers to ace your next Oracle PL/SQL interview.