In interviews for PLSQL developers, I usually ask a question of the form:
If you write a procedure to do <specific operation>, what will be the signature of such a procedure?
In response, the developer often veers off into describing the logic/body of the procedure, which is not what was asked.
If you’re among those likely to answer that question similarly, this article will clarify for you the concept of "signature" of a PLSQL stored procedure.
Oracle Signature Model
Whenever a stored program unit is created in Oracle, a signature is associated with and stored in the database in addition to the program.
Components that make the PLSQL signature:
- name of the program unit (package, procedure, or function name)
- types of each of the parameters of the subprogram
- modes of the parameters (IN, OUT, IN OUT)
- number and sequencing of parameters
- type of return value for a function
Components that do not make the PLSQL signature:
- pseudocode
- implementation logic
- syntax
The signature of a program changes when the parameters (names, datatypes, etc) change.
The signature of a program does not change when its code changes.
Why do you ask about the PLSQL signature? Isn’t it better to ask about code?
Indeed. What’s the big deal if a developer can only define the *signature*? Wouldn’t we gauge knowledge of PLSQL better if the candidate can talk about the code?
Actually, how you construct the signature reveals a lot about your maturity with programming.
Suppose the question is to define the PLSQL signature of a program to create a customer. The inputs are first name, last name, DOB, Passport.
How do you construct the PLSQL signature for this requirement?
- Do you define separate IN parameters for each of those inputs, or do you create an SQL Type or record with those attributes? Note that if in future, "phone" is to be added as another input, the first approach will change the signature, the second approach will not.
- Do you consider an output from that procedure? This was not explicitly asked for, but a thoughtful answer will include a return from the program including details such as the return status and the sequence-generated identifier generated for a customer record within the PLSQL.
