Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 4, 2010

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

By James Koopmann

James Koopman discusses how to give/take/study for an Oracle Database PL/SQL developer position, with a look at the top five questions he would ask potential Oracle Database PL/SQL Developers to help assess his/her basic knowledge.

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
2 var1 INTEGER;
4 var1 := 1;
5 DBMS_OUTPUT.PUT_LINE('Invoked callit, var1 is : '||var1);
9* END callit;
SQL> /
Procedure created.
SQL> exec callit(1);
Invoked callit, var1 is : 1
PL/SQL procedure successfully completed.

Where lines:

1.    Label for procedure

2.    Declarative section

3.    Keyword BEGIN

4.    Start of the execution section

5.    More execution section

6.    Keyword EXCEPTION

7.    Start of the exception section

8.    More exception section

9.    Keyword END

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.

 name   VARCHAR(50);
 fname  name%TYPE;
 lname  name%TYPE;
 city   name%TYPE;
 country name%TYPE;
 Execution section;

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.

-------- -----------------------------------------------------------------
4/10 PLS-00103: Encountered the symbol "=" when expecting one of the
:= . ( @ % ;
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;
------ ------------ -------- ---- -------- -------------------- --------- --------------
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
2 x VARCHAR2(1); -- scope of zero.x begins
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
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.

-------- -----------------------------------------------------------------
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.

2 PROCEDURE callit (anumber INTEGER) IS
4 DBMS_OUTPUT.PUT_LINE('Invoked number callit');
5 END callit;
7 PROCEDURE callit (acharacter VARCHAR2) IS
9 DBMS_OUTPUT.PUT_LINE('Invoked character callit');
10 END callit;
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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM