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:
1. How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.
2. What is an aggregate function? I’m looking for “grouping”, sums or counts, etc.
3. What is an interval? Specifies a period of time.
4. What is a nested subquery? A subquery in a where clause.
5. What is the dual table? A single row table provided by oracle for selecting values and expressions.
For Basic PL/SQL:
1. Describe the block structure of PLSQL. Declaration, Begin, exception, end.
2. What is an anonymous block? Unnamed PL/SQL block.
3. What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.
4. 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.
5. 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:
1.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. 😉
2. How do you create a hierarchical query? Connect by.
3. 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.
4. What do you need before implementing a member function? You need to create a type.
5. 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:
1. 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.
2. 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.
3. How can you tell if an UPDATE updated no rows? SQL%NOTFOUND.
4. How can you tell if a SELECT returned no rows. NO_DATA_FOUND exception.
5. How do you run Native Dynamic SQL? Execute immediate.
6. What is an autonomous transaction? Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.