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.
Related Articles
User Defined Encryption Questions for Your Next PL/SQL Developer Job Interview
Addressing Security in Your Next PL/SQL Developer Job Interview
Top 10 PL/SQL Developer Job Interview Questions to Demonstrate Your Coding Skill
Top 5 SQL Questions for an Oracle Database PL/SQL Job Interview
Oracle Technical Interview
Oracle Technical Interview Questions Answered - Part 1
Oracle Technical Interview Questions Answered - Part 2
Back to DBAsupport.com
