Hi, I'm Stephen Feuerstein and I write
practically perfect pl/sql.
[music]
So I talked in general about what constitutes hard coding. This idea is that
something's not going to change so I can
repeat it in multiple places, pretty much hope and pray that it doesn't change.
Let's take a look at a specific example, so let's take a look at line 5
those code
and link to it line 10 and link to that
line 17, just on those three different lines. I mentioned there are
three different hard coding, but I'm not to worry about all that right now.
But I want to focus on is the declaration line 5
only missed to declare to be varchar2
100. So here's my initial tip. I suggest that you consider
any varchar2 declaration with hardcoded value and you always have to
have a hard coded value
to be a bug, a bug in your code, unless it's the single point definition
for that data type and I'll come back and explain what that means in just a
moment. But in general
if you see a varchar2 declaration in your programs in a declaration sections
in your code
bug. How can that be?
You might be saying or another way of saying is, oh my god! He is saying I
have 10,000 more bugs than I thought I had my code.
Bad news! So why? Well, let's take a look at how it's being used
I'm fetching the full name of employee, last come first
into that local variable own the local variables being populated online 17.
So I needed to declare a variable
to hold that expression last name come first name. Now
what I'd like to be able to do and I'm sure you are aware this feature is percent type.
In other words, say that emp name should be called table dot com
percent type. Every time my table structure changes.
My code will be automatically recompiled and we will take the new definition that
column.
Beautiful feature. The problem is I have to derive the column.
Last come first and I can't do like percent type of this plus percent type
that. It
just doesn't work. So what do I do? Well, I need to make sure that the variable
is big enough to hold my last name first name. So
what I'm going to do is take a look at the employees table. Hm, I am looking a
description of employees it shows me that
the last name is up to 25 characters in length, the first names up to 20 characters
in length
let's have one for the common 20,25 comma,
46 great. I'm gonna to double that. I'm gonna make it 100,
100, that way will never get to be too big. It'll never exceed that size and will
never get value
error exception when the fetch performed, right? No, I'm sure you're saying no
Stephen
that's not true at all. Why is that not true? Because
it's not guarantee that the employees table always gonna look like that.
What if the DBA comes along and says well we got some new employees they got
a really long last names.
We better to extend the sizes of these columns. Last name is now five hundred.
First name is now two hundred. We changed the table
or the DBA changed the table. My code doesn't automatically recompile
or doesn't automatically take up the new declaration on the new
sizes on those columns because it's hard coded
And then the first time that a person's name a special it's a really big name
my code blew up. Well, so how can I get around that?
Well, the actually a couple of possibilities, one is I could add a
virtual comment , my table that has this expression and would automatically be
sized appropriately
I can create a view that has this expression and it will automatically
name to that column size of view would have that size dynamically created.
And it may be a good reason for doing that. But in general
you don't want to create a new database object to a new column table
just handle the problem declaring something in my code that can be
expressed with percent type.
A more general and really excellent solution is to take advantage of
subtypes.
Subtypes, what are subtypes? Well the subtypes is a new application specific
type that you can define your code
that is based on something else. So in general, what you want to do is you
subtypes to declare
application specific types that can be expressed to percent type
or percent role type. Let's take a look.
So I have a package called employeesRP.
That means employees rules package. So all the rules associate with employees
table.
At the minimum salary for a CEO must be $10 million.
What is and how do you construct the full name employees, it's all container.
So I have a function called full name that takes the last name first name returns.
What is that returns fullname? You think it would return varchar2, right?
What is fullname? Fullname is a subtype.
So fullname on line 10, I declared it to be a subtype. And it is
nothing more than and nothing less than a varchar2
10,000 work or maybe it's a varchar2 1000
or maybe it is, it doesn't matter what it is. The point is that it's hint behind
the fullname_t and then
I can go back to my code and I can say I don't wanna hard code in this declaration.
and I wanna soft code. I want soft code to be employees
_rp.fullname_t. So now my declaration of name
is a redirect to that package definition. It's really the same as a percent type
in the sense that they haven't said what it is I said what it's based on
and now every time I need to change this, for example the DBA says ha
crew got really long names come in now. We got a first name that could be five
hundred; got a last name could be
20,000. What do you do about that, developer? No big deal.
I'm going to my package back. I'm gonna change this to 32767.
Biggest impossible today in Oracle 11 or 12 whatever version you are working on
I recompile the package: my
procedure recompiles as a result and my new definitions in place.
So what I've done
is take a constrained declaration of varchar2 declaration.
Every varchar2 declaration is a constraint. You have to provide a maximum
size. And I've taken that out this specific instances code
and replaced with a name and in general that's where you get your
hard coding.
You replace the details of the implementation with a name.
In the name hides the details and tells you what it is.
So my codes have been more self documenting. I don't need a comment with that.
It's a,
it's a full name. it's pretty obvious in terms of the expression
that creates the fullname, that's an another hard coating and it should just be put
inside the function
and you call the function. I will talk about that in a different presentation.
But again I would go back to the very simple rule that you can follow
Every varchar2 declaration should be considered a bug in your code.
In less, it's this kind of declaration and less
it's the single point definition through the subtype.
If you follow this rule, you have fewer chance of breakage of overtime.
And this is one of those classic examples where you write the code today.
It works fine.
Goes in production works fine years ago by
years ago by. You're gone, maybe your son or daughter now maintains your code.
Is this what you wanna leave this to your son or daughter? No, you want to make that
as these data structures change. I heard a data mining grows as things modify
you could automatically adapts as much as possible or
if you can automatically adapt because there's nothing on automatically this,
you change in one place the single point of definition.
So thanks for joining me on this talk. Thanks for getting rid of all your var
char2 declarations and putting them into subtypes.
Happy coding!
[music]
[ exit music]