Interveiw Questions - PL/SQL

[SN060619-1202]
1. Describe the difference between a proceudcure , function and anonymous pl/sql bock
Ans:
- A procedure is a subprogram that performs a specific action.
- A function is a subprogram that computes a value.
- Funciton adn procedures are structured alike, except that funcjitons have a RETURN clause
- PL/SQL is a block-structured language. Procedure, funciton and annymous PL/SQL bock are basic units of PL/SQL. They are logical blocks.
- a PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part.

2. What is a mutating table error and how can you get around it?
ans: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The ususal fix involes either use of views or temporary tables so the database is selecting from one while updating the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
ans: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

4. What packages(if any) has Oracle provided for use by developers?
ans: DBMS_series of packages. e.g DBMS_SQL, DBMS_PIPE, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE

YS: how about for use by DBA?

5. Describe the use of PL/SQL tables
YS:
- not sure what PL/SQL tables refer to. Assume it refers to the nested tables
- Nested tables is one of the three collection types offered by PL/SQL. Other two types are index-by tables and Varrays.
- Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.

8. What are SQLCODE and SQLERRM and why they are important for PL/SQL developers?
ans:
- SQLCODE returns the value of error number for the last error encountered.
- SQLERRM returns the actual error message for the last error encountered.
- used in exception handling to report, or store in an error log table, the error that occurred in the code. There are especially useful for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block if a cursor is open?
ans: use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?
Ans:
(1) Use the DBMS_OUTPUT package. it can be used to show intermediate results from loops and the status of variables as the procedure is executed
(2) use SHOW ERROR command: this only shows errors
(3) UTLL_FILE can also be used

11. What are the types of triggers?
ans:

- A database trigger is a stored subprogram associated with a database table, view, or event.
- One of the many uses for database triggers is to audit data modifications
- Triggers can be:
* DML triggers on tables
* INSTEAD OF triggers on view
* system triggers on DATABASE or SCHEMA

Note: The provided ansewer is: there are 12 types of triggers that conisist of combinations of (BEFORE, AFTER) (ROW, TABLE) (INSERT, UPDATE, DELETE)