How to interview an Oracle Developer

It looks like the job market for Oracle is opening up. I'm seeing many of my friends find new jobs and I'm doing a lot of interviewing at work. I hope this is a trend that continues. In this entry, I share my method for interviewing Oracle resources and provide some sample interview questions and answers for developers.

Interviewing anyone can be difficult. Interviewing technical resources is very difficult. To me, probably the hardest thing is pinpointing exactly what you want this person to do. What, exactly, will this person be doing in their day-to-day job? It's easy to say having a good job description makes it easier but in many cases, employers have a generic template when looking for someone.

First off, is there a difference between a developer and a programmer? I think there is. A programmer is a coder. I don't mean that as a bad thing. Every project needs coders. A developer should be part analyst and part coder. A developer should be able to handle requirements gathering through implementation. Every project should have at least one good developer. In your interview, you should distinguish between the two.

If you're looking for an Oracle resource, there is a huge possible range of knowledge. Does a developer code for the front end? If so, which tools? Forms, Reports, HTML DB? I hope that you aren't looking for Java because wouldn't that make them a java programmer and not an Oracle developer?

Before you start to interview people, make sure you really know what you're looking for. It's not fair to the candidate to say you're looking for forms experience and then spend all of your time on advanced back end programming. And don't ask a backend coder the fine details of forms.

When I interview someone, I look for more than yes/no answers to my questions. In an interview, I expect a candidate to communicate with me. I'm looking for a comfort level. If the person says they don't know a particular topic, that's acceptable. If they fumble and make something up, that's a problem to me. I don't look for textbook definitions. I want to know they understand what I'm asking and what they're answering. I also don't believe in tricky interviews. What's the point?

Regardless of the exact position, any Oracle resource, including a DBA, should know some basic things about SQL and PL/SQL. Some sample questions I ask are:

For Basic SQL:



* How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.


* What is an aggregate function? I'm looking for "grouping", sums or counts, etc.


* What is an interval? Specifies a period of time.


* What is a nested subquery? A subquery in a where clause.


* What is the dual table? A single row table provided by oracle for selecting values and expressions.




For Basic PL/SQL:



* Describe the block structure of PLSQL. Declaration, Begin, exception, end.


* What is an anonymous block? Unnamed PL/SQL block.


* What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.


* What is the difference between an explicit cursor and a select into. You might get something about performance but that's a myth. An explicit cursor is just more typing. A cursor for loop would be used to return more than a single row.


* Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a "good thing".




These are pretty basic questions. If I don't get a warm fuzzy from these, and they are 100% answerable by anyone with some real experience, then the person goes no further.

So, where do you go after the basics? That really depends on what you're looking for. If you are hiring a Java coder to work with your Oracle group or you're looking for a DBA, you might end the coding part here. You would expect a DBA to know more but I would move on to administrative questions. You might also stop here if you're looking for a junior developer to train.

If you're looking for a senior PL/SQL coder type, you will want to go deeper. You need to remember to ask specific questions about a person's background and forms developers will have different experience than a back-end developer. But either should have a good grasp of advanced topics.

The hard part is that there are so many advanced topics; it's hard to know what to ask. You need to tailor it for your environment. If you use a lot of AQ, ask AQ questions. If you're very OO, ask OO questions.

Here are some more advanced, but still generic questions:

For Advanced SQL:



* What is the difference between an aggregate and an analytic function? I'm looking for them knowing that a sum aggregate (or any other aggregate function) will return one row for a group and a sum analytic will return one result for each row in the group. If they mention the "Window", they get a bonus point. ;-)


* How do you create a hierarchical query? Connect by.


* How would you generate XML from a query? The answer here is "A lot of different ways". They should know that there are SQL functions: XMLELEMENT, XMLFOREST, etc and PL/SQL functions: DBMS_XMLGEN, DBMS_XMLQUERY, etc.


* What do you need before implementing a member function? You need to create a type.


* How do you tune a query? I'm looking for a discussion of autotrace and/or explain plan. Ask them what they're looking for in a plan. This should not be a single sentence. Look for a comfort level.




For Somewhat Advanced PL/SQL:



* What is the default value of a boolean? NULL. This is somewhat tricky but apparently there are languages that default boolean to false. A PL/SQL developer needs to know all variables default to NULL.


* Why is using implicit conversions a poor programming practice? For dates, you must ASSUME that the default date format will always be the same (and it won't be). In some cases, implicit conversion is slower. I want to feel like they don't believe writing to_char or to_number is more work than it's worth. BTW, this also applies to SQL.


* How can you tell if an UPDATE updated no rows? SQL%NOTFOUND.


* How can you tell if a SELECT returned no rows. NO_DATA_FOUND exception.


* How do you run Native Dynamic SQL? Execute immediate.


* What is an autonomous transaction? Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.




Those are some items that should also give you a warm fuzzy. If a person makes it to here, you can ask the questions specific to your organization, i.e. the AQ, LOB, Forms, HTML DB, etc.

At this point I usually ask the candidate to explain specific statements on the resume. If they say they tuned queries or improved performance, I say how? What did you do? What tools did you use?

That's my interviewing method. I hope that helps you get the best people for your organization.

Lewis