Oracle PL/SQL Interview Questions & Answers – Part I
Ans. Following are some of the major differences between Stored Procedure and Function
a. Stored procedure can call function but function cannot call stored procedure.
b. Stored procedure may or may not return value but function should always return value.
c. Stored procedure can be called from DML statement but function can be called.
d. Stored procedure have input & output parameter but function have only input parameter.
e. Stored procedure does not have RETURN clause in header but function should have RETURN clause.
2. What is a mutating table error and how to resolve it?
Ans. Whenever trigger tries to update the same row which is currently in use then this error occurs. To resolve this issue either one has to use view or temporary table so that database will select from one while updating the other.3. What is a difference between %ROWTYPE and %TYPE in Oracle PL/SQL?
Ans. %ROWTYPE allows us to associate variable with an entire table row while %TYPE associates variable only with single column type.
4. What is a use of PL/SQL table?
Ans. PL/SQL tables are scalar arrays referenced by binary integer which can hold values for use in later queries or calculations. They will be able to be either of %ROWTYPE or of RECORD type.
5. When is a DECLARE statement needed?
Ans. DECLARE is optional statement in a PL/SQL block. It is mainly used for declaration of variables, procedures or functions to be used within the PL/SQL block.
6. What should be the order for open/fetch/loop commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the EXIT WHEN statement?
Ans. Here, it always should start with OPEN then FETCH and then LOOP followed by the EXIT WHEN. If not specified in this order then it will result in the final return being done twice because of the way the %NOTFOUND is handled.
7. What are SQLCODE and SQLERRM in Oracle PL/SQL?
Ans. SQLCODE always returns the error number value while the SQLERRM returns the actual error message. They are used in handling exceptions in PL/SQL block.
8. How to check if a CURSOR is open in PL/SQL block?
Ans. %ISOPEN can be used to check cursor status.
9. How to generate debugging output from PL/SQL?
Ans. DBMS_OUTPUT package can be used to debug in PL/SQL. SHOW ERROR command is also one of the method but it will show only error. The DBMS_OUTPUT package can be used to display intermediate results from loops and the variables values as the procedure is executed.
10. What are the types of triggers?
Ans. There are total 12 types of triggers in PL/SQL that consist of combinations of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words i.e. BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT etc.