Top 5 Basic Concept Job Interview Questions for Oracle Database PL/SQL Developers

Interviewing for any technical position can be quite a stressful experience. Ever since I initially wrote “Oracle Technical Interview Questions Answered”, part One and part Two, more than seven years ago, I’ve been continually asked to increase the breadth of these questions to take on various roles, responsibilities, and job functions for Oracle professionals. So, in this article, I’ve decided to take a step down the path of how I might give/take/study for an Oracle Database PL/SQL developer position. This particular article takes the top five questions I personally would ask, straight off the bat, to someone looking to fill a PL/SQL developer role.

As you read these, especially if you’re getting ready for an interview, remember that these questions are not hard and fast, there are many different off-shoots that the interviewer can go. Thus, these questions should serve as a jumping-off spot for your initial answer. I personally always like to give, and be given, real-world examples where appropriate that help solidify the concepts and prove your expertise in them.

Good luck. I hope these questions get you started down the right path, and please come back for more as I’m sure to have a few more articles, I’d assume a total of 20 questions, that help with a PL/SQL Developer job role.

1. What is the basic PL/SQL block structure?

The basic unit of a PL/SQL program is the block, which may consists of a label, declarative section, execution section, and exception section. Keywords include DECLARE, BEGIN, EXCEPTION, and END where BEGIN and END are the only required keywords and the execution section is the only required section. The individual pieces would look like the following:

SQL> l
1 CREATE OR REPLACE PROCEDURE callit (anumber INTEGER) IS
2 var1 INTEGER;
3 BEGIN
4 var1 := 1;
5 DBMS_OUTPUT.PUT_LINE('Invoked callit, var1 is : '||var1);
6 EXCEPTION
7 WHEN OTHERS THEN
8 DBMS_OUTPUT.PUT_LINE('Error!');
9* END callit;
SQL> /
Procedure created.
SQL> exec callit(1);
Invoked callit, var1 is : 1
PL/SQL procedure successfully completed.

Where lines:

2. What is the difference between %ROWTYPE and %TYPE and what is the main advantage to using these?

The %ROWTYPE allows the coder to indirectly represent a full or partial row of a database table or view, whereas the %TYPE allows for the coder to indirectly represent the data type from a previously declared variable or column. Basically, %ROWTYPE works on a full object whereas %TYPE works on a single column. The advantage to using either of these enables the coder to maintain data type declarations without ever having to know or change the data type for the items that use these. Below is an example of how the %TYPE allows for a layer of abstraction between names; allowing the coder to just change the first occurrence of the data type.

DECLARE
 name   VARCHAR(50);
 fname  name%TYPE;
 lname  name%TYPE;
 city   name%TYPE;
 country name%TYPE;
BEGIN
 Execution section;
END;
/

3. How might you display compile time warnings for PL/SQL code?

There are actually two methods to show compile time warnings. While both ‘SHOW ERRORS’ and the *_errors views (USER_ERRORS used here) show basically the same information; I tend to like the SHOW ERRORS command as it seems quicker to type. The advantage to using the *_errors views is that you can actually monitor every developer’s current errors when using a view such as DBA_ERRORS, as there is an additional column for OWNER that will tell you the user encountering errors.

SQL> SHOW ERRORS
Errors for PROCEDURE CALLIT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/10 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.
8/7 PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
one of the following:
then or
The symbol "then" was substituted for "DBMS_OUTPUT" to continue.
SQL> SELECT * FROM user_errors;
NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
------ ------------ -------- ---- -------- -------------------- --------- --------------
CALLIT PROCEDURE 1 4 10 PLS-00103: Encounter ERROR 103
ed the symbol "=" wh
en expecting one of
the following:
:= . ( @ % ;
The symbol ":= was i
nserted before "=" t
o continue.
CALLIT PROCEDURE 2 8 7 PLS-00103: Encounter ERROR 103
ed the symbol "DBMS_OUTPUT" when expecti
ng one of the following: then or
The symbol "then" was substituted for "D
BMS_OUTPUT" to continue.

4. Define ‘scope’ and ‘visibility’ for PL/SQL variables.

The definition of scope and visibility for a variable is actually quite close with the only difference being if you have to qualify the variable. The scope of a variable refers to the region (breadth) of code where the variable can be referenced. The visibility refers to the region of code you can reference the variable without qualifying it. So, hopefully you can see, visibility is a subset of the scope and requires the variable to be qualified (told where it comes from) in order to use. An example is clearly the best option here to help explain. Consider the PL/SQL code:

SQL> l
1 CREATE OR REPLACE PROCEDURE zero IS
2 x VARCHAR2(1); -- scope of zero.x begins
3 PROCEDURE a
4 IS
5 x VARCHAR2(1); -- scope of a.x begins
6 BEGIN -- visible a.x
7 x := 'a';
8 DBMS_OUTPUT.PUT_LINE('In procedure a, x = ' || x);
9 -- even though zero.x is not visible it can still be qualified/referenced
10 DBMS_OUTPUT.PUT_LINE('In procedure a, zero.x = ' || zero.x);
11 END; -- scope of a.x ends
12 PROCEDURE b
13 IS
14 BEGIN -- visible zero.x
15 DBMS_OUTPUT.PUT_LINE('In procedure b, x(zero) = ' || x);
16 DBMS_OUTPUT.PUT_LINE('In procedure a, zero.x = ' || zero.x);
17 END;
18 BEGIN -- visible zero.x
19 x:='0';
20 DBMS_OUTPUT.PUT_LINE('In zero, x = ' || x);
21 a;
22 b;
23* END; -- scope of zero.x ends
SQL> exec zero
In zero, x = 0
In procedure a, x = a
In procedure a, zero.x = 0
In procedure b, x(zero) = 0
In procedure a, zero.x = 0
PL/SQL procedure successfully completed.

Probably the biggest thing to notice about the scope of a variable, while all variables referenced (‘x’) are the same, just ask yourself if you need to qualify it and that will determine if it is visible. Notice in ‘PROCEDURE b’ where there is no local ‘x’ variable so the ‘x’ from ‘PROCEDURE zero’ is still visible and really doesn’t need to be qualified, even though you still can. Moreover, if you ever get lost, Oracle is sometimes gracious to help by telling you something is out of scope.

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/2 PL/SQL: Statement ignored
15/44 PLS-00225: subprogram or cursor 'A' reference is out of scope

5. What is an overloaded procedure?

An overloaded procedure is nothing more than the a mechanism that allows the coder to reuse the same procedure name for different subprograms inside a PL/SQL block by varying the number of parameters or the parameter data type. Below is an example of where the same subprogram (callit) is reused but the data type for the input parameter is changed from INTEGER to VARCHAR2; Oracle is smart enough to know the input parameter type and call the proper subprogram.

SQL>
1 DECLARE
2 PROCEDURE callit (anumber INTEGER) IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Invoked number callit');
5 END callit;
6
7 PROCEDURE callit (acharacter VARCHAR2) IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE('Invoked character callit');
10 END callit;
11
12 BEGIN
13 callit(1);
14 callit('1');
15* END;
SQL> /
Invoked number callit
Invoked character callit
PL/SQL procedure successfully completed.

Well, that would be my top 5 questions I’d ask a potential PL/SQL Developer to help assess his/her basic knowledge. These are also the top 5 areas I personally would brush up on if going in on an interview for a PL/SQL Developer position. Yes, some of them are a bit easy. However, remember, these questions and answers should only be used as an initial starting point for questions. Use them to investigate syntax rules, calling methods, and basic style. Never go into an interview with straight answers and always be ready to relate to specific job-related examples.

» See All Articles by Columnist James Koopmann