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.
4. How many COMMIT statements do you put in your code?
This is somewhat of a tricky question and I hope you are following the general theme of this article, that of making your PL/SQL code flexible and more importantly conveying to your interviewer that you have this mindset. However, the real answer here is that you should really have no COMMIT statements within your application code. The better way is to call a procedure to do the commit for you. I can see a lot of funny faces while you are reading this but the example I draw upon is very simple. Just ask yourself how many times you’ve commented out the commit statement for testing purposes. It is our duty to make our applications as flexible as possible and with hard coded commit points in our applications, we are telling ourselves we know exactly how the application will run, when we need to commit, and it will never change. I have all too often had to modify the commit frequency within an application that I hold to this rule very strictly.
5. What are the four dynamic SQL methods?
This is the first distinction you should make when analyzing the type of dynamic SQL you should be implementing. Understand what these are and how you might code them. You should note that as the method number increases, so does the complexity or generality of the type of statement.
1: non-query without host variables; use EXECUTE IMMEDIATE
2: non-query with known number of input host variables; use EXECUTE IMMEDIATE with USING
3: query with known number of select-list items and input host variables; use EXECUTE IMMEDIATE with USING and INTO for single row but EXECUTE IMMEDIATE with USING and BULK COLLECT INTO or OPEN FOR with dynamic string for multi-row
4: query with unknown number of select-list items or input host variables; use DBMS_SQL
Writing PL/SQL code is easy to some extent. We can easily drop in SQL code around some logic and we will have an application that will more than likely satisfy the requirements we have before us. The problem with this is that our query tactics within that code can very easily fail if we are unaware of some common pitfalls or coding practices. Take these five questions, they are just the tip of the iceberg, and think about making your PL/SQL code more general and dynamic. An interviewer should see your attention to detail and desire to improve the environment, even if they have tendencies to crank out code without regard to standards.
