PL SQL programming Interview questions and answers

James Koopmann shares five common issues that could get you into trouble when writing PL/SQL (and SQL), and how you might answer those questions within the confines of a PL/SQL job interview.

An interviewer should see your attention to detail and desire to improve the environment, even if they, themselves, have tendencies to crank out code without regard to standards.

It is amazing to me that many writers of PL/SQL never give much thought as to how they access (query) data from within the database. For this reason, an overwhelming phrase that rings from many DBAs goes something like this, "All applications would be perfect if they didn't access my data "or" My database wouldn't have any performance problems if we just eliminated the applications. Either way we all understand that applications are a necessity. However, it is not necessarily true that applications cause or should cause database performance issues. This article looks at some of the more common issues when writing PL/SQL (and SQL), in the confines of a PL/SQL job interview, that could get you into trouble and how you might answer those questions.

1. How do you go about tuning your PL/SQL code?

This really hits at the core of this article. We must all understand, and relate this to our interviewer that we know that it is the SQL that will always cause the most difficulty, performance wise, integrity wise, bug wise, within our PL/SQL code. We can always talk about EXPLAIN plan usage, TKPROF, gathering runtime statistics, index optimization, and the list goes on, but let me suggest another tactic here that might get you noticed. Try working in the fact that you understand that data can change drastically within an organization and a static application (PL/SQL code) often does not cut it. What is needed, and what you will bring to the table is an ability to place an abstraction layer, using views, functions, triggers, procedures, etc. that maintains the integrity of the PL/SQL logic but allows for simplified maintenance to the data the PL/SQL code requires.

As a very simplistic example, imagine you needed to select a number of employees within your PL/SQL code. A very simple solution would be to SELECT directly all the employees form the EMP table. However, let's say we acquired another company and wanted this code to work with two different EMP tables. The old code would have to be modified to possibly perform a join. The better solution, one not affecting the code, would be to always use a view and then modify the view when the new company was acquired. A little abstraction goes a long way when requirements change.

2. How might you get around hard coding the elements in a fetch cursor?

I'd have to say that this is one of the most common forms of hard coding, other than actual values/IDs being used in a SQL statement. Practitioners will often use the %TYPE notation for individual variables, which is fine and well, within the declaration section but seem to lose sight of the %ROWTYPE. When fetching a cursor INTO variables those variables are often strung out in a list such as: FECTH empId, empFname, empLname INTO vempId, vempFname, vempLname; clearly requiring the addition of another variable in the declaration section and at the end of the INTO clause. What should happen here is use the %ROWTYPE and just issue something like: FETCH empId, empFname, empLname INTO empRowtype; removing all hard coding in the body of the PL/SQL code.

3. How do you get around repeating SQL code?

The answer seems simple and many would agree that repeating code is an accident waiting to happen; increasing the probability of changing all but one code segment and having a very difficult bug to find. Instead, we should always, for straight code or SQL statements, ensure we never perform the same function in two different places in our code. Instead, we should hide the SQL behind subprograms and then call those subprograms repeatedly. Not only will this make your code more efficient and maintainable but these subprograms can be called by other applications; creating a much more flexible environment.