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