Code Comment WTF? Part 209

Found this in a snippet today:

-- ********************************
-- End of Package Body

END package_pkg ;
/

Seriously? Was that necessary? Could I possibly be under the illusion that it is not the end of the package?

Stop it.

Now.

Writing Maintainable Code

The last few years I've inherited quite a bit of code. The vast majority of it reads terribly.

I get newbies; those who have yet to find their style. Those that I run into I highly encourage to find a style and stick with it...even if the commas are in the front of the line. Of course I wish my style would be the standard. Wait a minute, it already is. Check out this snippet from the Official Oracle documentation:



Let me live in my own fantasy world...

What about the rest though? A friend recently told me he thinks it takes about 2 years for someone to develop their own distinctive style. So why doesn't it seem to happen? Or, do I just see all newbie code?

I doubt that's the case.

I try to write code so that it is readable. Others may not like my style, but it is consistent. A former colleague once said that my code (a 5,000 line ETL package) read very easily. It had a natural flow to it. That was one of the best compliments I have ever received. The point is, I write code with the assumption that someone else will (have to) read it.

I find myself in SQL*Plus, ad-hoc queries, writing them the way I would if I were to check it into source control.

SELECT OWNER, TABLE_NAME FROM DBA_TABLES

...that's pretty rare. I find myself backspacing to fix it

SELECT owner, table_name
FROM dba_tables

That might not be the best example (I have a script for that anyway), but you get the point.

"Practice like you play" is a common sports phrase. Perhaps that's why I write ad-hoc queries in that fashion.

I would offer tips, but that would just lead me back to style. I don't want to force people into using my style (that's a lie, but I accept that I am not Supreme Ruler of Code Style), I just want them to choose.

At a minimum, run your code through one of the various code formatters. Toad has one, SQL Developer has one, I think most tools have one. You can even set them up to match your personal preferences (which should align perfectly with mine).

When asking for help from someone else, don't use the default font in your email client (unless your a plain text email person), highlight the code and put it in Courier New. Please.

Finally, write your code as if someone else has to read it!

Top Ten Grammatical Errors That Make People Look Stup--Silly....

I had been planning and scheming to be the "first" to have a guest blogger...but Lewis beat me to the punch. Yes, yes, I know, neither of us were the first to think of it.

I'm part of this the Network with Gators group on LinkedIn and one of the daily digests came through my email titled, "How many Gators are making the leap to entrepreneurship or business owners?" (I've probably done the quotation marks wrong). Anyway, I saw one of the comments by one Tiffany Morgan and decided to reply to her privately.


I liked the fact that she was creating something all her own, especially that she was so young in doing so. I was probably face down...I mean, doing something else, something non-constructive, at her age. (Seriously, it's not like I'm 70 or something, so why does early 20's sound so young?)

Anyway, I thought many of us could use some good writing tips. If you're like me, you won't necessarily go looking for them. So I'm bringing right to you.

Without further ado, Tiffany Morgan.

---

Words are one of the few things in this world that aren’t discovered—they are entirely manmade. Because of this, those of us who love the things fight among ourselves (and with those who hate the things) to form some semblance of consistency through them, to make the words that make the world make sense be sensical themselves, to give them empirical, scientific substance when they inherently have none. So we draft up things like dictionaries, thesauruses, and style books. Few people read them, fewer love them, but everyone needs them. A typo can be easily overlooked, but not knowing the difference between “who” and “whom” or “its” and “it’s” can make the difference between acceptance and rejection, publication and dismissal, credibility and incredibility. It’s not necessary to beat yourself up memorizing the rules, but it is helpful to know at least some of the common issues our tenth grade English teachers should have taught instead of harping on the state assessment exam.

Top Ten Grammatical Errors That Make People Look Stup—Silly... .

I just received a tongue lashing from Tiffany because I tried to give her crap about the ellipsis

10. Putting quotation marks inside periods and commas – This is never, ever right. No matter what anyone tells you, quotation marks (and yes, they are called "quotation" marks, not "quote" marks) always (times infinity squared) go outside periods and commas. This rule does not always apply to other punctuation marks, however, so just memorize it for these two for now.

Wrong: "I love Twitter", she said.
Right: "I hate Twitter," he said.

9. Using "and" when you mean "to"

Wrong: I’m going to call my dad and wish him a happy Father’s Day.
Right: I’m going to call my dad to wish him a happy Father’s Day.

8. Mixing up "then" and "than" – "Then" denotes time, sequence, or addition. "Than" is a comparative word.

Example:
I’m going to school, and then I’m going to work.
She likes cake better than ice-cream.

7. "Lose" v. "Loose" – "Lose" means something is or may no longer be possessed. "Loose" means something is slack.

Example:
I don’t want to lose my job.
My shoelace is loose.

6. There, Their, and They’re – "There" refers to a place, stage, or relation. "Their" is possessive for a group. "They’re" is the contraction form of "they are." No examples here. They are seriously self-explanatory.

5. Saying "whether or not" – The word "whether" means there is an option. To say "whether or not" is redundant.

Wrong: I don’t care whether or not you like my Hammer pants!
Right: I don’t care whether you like my Hammer pants!

4. Using "of" when you mean "have" – We might say it this way, but writing it this way is a big no-no.

Wrong: You should of listened to your boss.
Right: You should have listened to your boss.

3. "Affect" v. "Effect" – People who have never learned the difference between these words generally use "effect" for everything, but I promise you: They are different! "Affect" is a verb and means "to influence," but "effect" is a noun (except when it’s a verb, meaning "to cause," but we’ll leave that alone for now).

Example:
Having the puppy in the house positively affected her mood.
The cute puppy had no effect on her mood.

2. "You’re" v. "Your" – "You’re" is the contraction of "you are," and "your" is possessive. Once again, no examples here. We can do this, people!

And the number-one-please-never-get-this-wrong-again-or-the-world-might-implode-grammatical-error-that-makes-people-look-stupid-er-I-mean-silly error is:

1. "Its" v. "It’s" – "Its" is possessive. "It’s" is the contraction form of "it is."

Example:
It’s going to be a rainy day.
The dog wagged its tail.

There are others, and maybe we’ll get to those if Chet is gracious enough to invite me back. This is by no means an exhaustive list, and I barely even began to touch on word usage, which is actually more bothersome in some ways than misusing contractions and possessives. Until then, try remembering these rules in your day-to-day activities—when writing papers, preparing memos, e-mailing, and if you want to get really fancy, texting. When you don’t have time to get it completely right, though, just get it written, and contact a professional. I’m a professional editor, and you can contact me through my website, www.WriteWordEdit.com, for an extra set of eyes or for extensive editing of pretty much anything (personal statements, essays, research papers, resumes, fiction, non-fiction, and more).

Lastly, as a general disclaimer, any errors or typos found herein are the sole fault of an early a.m. dysfunctional brain. Coffee, please?

Happy writing,

Tiffany Morgan
Write Word Edit
Founder/Editor in Chief
www.WriteWordEdit.com

Commas: Before or After the Line?

I'm in a new group again which means I have to learn (and accept) other people's style. No one at WellCare put commas before the line (thankfully), but I've found a few here.

I've finally come to accept that this is just style and doesn't really matter, as long as the code does what it's intended to do.

Yes, it's silly, but we all have our little quirks right?

Code Style: Functions

Functions

Functions are used to return a value. This value can take the form of a set of records (ref cursor), a collection, or a single value (NUMBER, VARCHAR2, etc.).

Functions (or procedures for that matter) should be logically grouped within a package. Only on the rare occasion should you store them individually.

That said, here is the syntax to create a function in Oracle:

CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
  l_age_in_months INTEGER;
BEGIN
  --instrumentation calls
  debug( 'GET_AGE_IN_MONTHS' );
  debug( 'P_ID:  ' || p_id );
  debug( 'select value into variable' );

  SELECT age_in_months
  INTO l_age_in_months
  FROM people_tab
  WHERE id = p_id;

  debug( 'L_AGE_IN_MONTHS:  ' || l_age_in_months );

  RETURN l_age_in_months;

EXCEPTION
  WHEN no_data_found THEN
    debug( 'no data found' );
    --here you need to decide what exactly you want to do.  If this is 
    --used in a SQL statement then you probably don't want to halt 
    --processing, so returning NULL will work just fine. However, if 
    --this function is called by another function or procedure and you 
    --need this value to continue processing, you WOULD want to know
    --that the value is not there and you would issue a RAISE after you've
    --logged the error
    RETURN NULL;
END get_age_in_months;
/
show errors

The "debug" call is just instrumentation of the code. It will allow you to step through the code more easily.

In the above function, you would also need to be aware of too_many_rows, but since I know that "id" is the primary key on the table, I have ommitted it.

1. Instrument your code.


2. Name the function something descriptive. There is a 30 character limit so don't be lazy.


3. Name your functions something descriptive. I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.


4. Name your variables something descriptive. There is a 30 character limit so don't be lazy. Please just spell out "no". Is it "No" or "Number?" Why make the next person think, just spell it out. They'll thank you for it.


5. If there are more than 1 input parameters, start at the next line.


6. CREATE OR REPLACE goes on the top line, nothing else


7. Use spaces liberally


8. Comment where necessary. If you name things descriptively though, you'll find you won't need a lot of comments.

I tend to name variables thusly (I think I got that from Mr. Kyte):

• p_ = parameters passed (though you could use i_ for in and o_ for out as well)


• l_ = local variables


• g_ = global variables

Code Style: Package Specification

Package Specification

CREATE OR REPLACE
PACKAGE p_foo
AS
  FUNCTION get_something
    ( p_id IN NUMBER,
      p_thing_im_looking_for VARCHAR2 ) RETURN NUMBER;
  PROCEDURE do_something( p_id IN NUMBER );
  PROCEDURE insert_something
    ( p_first_name IN PEOPLE_TAB.FIRST_NAME%TYPE,
      p_last_name IN PEOPLE_TAB.LAST_NAME%TYPE,
      p_email_address IN PEOPLE_TAB.EMAIL_ADDRESS%TYPE,
      p_middle_initial IN PEOPLE_TAB.MIDDLE_INITIAL%TYPE DEFAULT NULL,
      p_prefix IN PEOPLE_TAB.PREFIX%TYPE DEFAULT NULL );
  PROCEDURE update_something
    ( p_id IN NUMBER,
      p_first_name IN VARCHAR2,
      p_last_name IN VARCHAR2 );
  PROCEDURE delete_something( p_id IN NUMBER );
END p_foo;
/
show errors

1. Name your package something descriptive. There is a 30 character limit so don't be lazy.


2. Name your procedures and functions something descriptive. There is a 30 character limit so don't be lazy. UPDATE this or INSERT that. Try to give a small clue as to what it does. If it's a function, I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.


3. Name your variables something descriptive. There is a 30 character limit so don't be lazy. Please just spell out "no". Is it "No" or "Number?" Why make the next person think, just spell it out. They'll thank you for it.


4. Use the package name after the END statement.


5. If there are more than 1 input parameter, start at the next line.


6. CREATE OR REPLACE goes on the top line, nothing else


7. Use spaces liberally


8. Comment where necessary. If you name things descriptively though, you'll find you won't need a lot of comments.


9. Declare parameters using %TYPE. If the parameter doesn't map to a table column declare SUBTYPEs somewhere.

Code Style: Index

My ongoing contribution to...pretty much myself. Perhaps you can find this useful, or amusing, whichever.

• General Principles


• Tables


• Functions


• Package Specification

Code Style: Tables

Tables are easy.

CREATE TABLE t
(
  col1 NUMBER(10,0)
    CONSTRAINT  pk_col1 PRIMARY KEY,
  col2 VARCHAR2(32)
    CONSTRAINT nn_col2_t NOT NULL
    CONSTRAINT uq_col2_t UNIQUE,
  col3 VARCHAR2(400),
  col4 VARCHAR2(1) DEFAULT 'N'
    CONSTRAINT ck_yorn_col4_t CHECK ( col4 IN ( 'Y', 'N' ) )
    CONSTRAINT nn_col4_t NOT NULL
);

Remember to always name your constraints. While I am at, use constraints as much as humanly possible, at least in your OLTP systems. You'll be able to reduce the amount of code you need to write and actually let the database do it's job. I'd much rather let the database do it than rely on code to maintain my data integrity.

For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.

For child tables:

CREATE TABLE s
(
  col5 NUMBER(10,0)
    CONSTRAINT pk_col5 PRIMARY KEY,
  col1
    CONSTRAINT fk_col1_s REFERENCES t( col1 )
    CONSTRAINT nn_col1_s NOT NULL,
  col6 VARCHAR2(30)
);

For Foreign Key constraints, you do not have to declare the type as it will be inherited from the parent table.

This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).

As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).

To recap:

• Use constraints as much as possible


• Always name your constraints


• Work with your DBA for table options


• Always name your constraints

Code Style: General Principles

I have always wanted to put together my personal code style guide. Now that I have a blog, I can do so rather easily.

This will be the first among many that details my approach to coding style. Yes, it is very particular to me. No, I don't expect anyone in their right mind to follow it. Though perhaps someone can use it as a general guideline.

I am fairly fanatical about style.

Parenthesis go on separate lines, always (I'll eventually contradict myself I'm sure). None of this my line starts with a comma crap. My only concession is that it makes commenting out lines easier. Otherwise, I can't stand it. It's ugly to me and I don't like ugly.

Tabs = 2 spaces

CREATE OR REPLACE is the top line followed by the particular object on the second line (I don't know why, it's my style though).

CREATE OR REPLACE
PACKAGE p_package
IS
...

Always put "show errors" at the bottom of procedure/function/package scripts.

Always end your procedure or function with the name after END.

CREATE OR REPLACE
PACKAGE p_package
IS
...
END p_package;
/
show errors

Align SELECT statements left, not right:

Evil

SELECT blah, t.id, s.x
  FROM t, s
 WHERE t.id = s.id
   AND t.x = s.x; 

Correct

SELECT 
  blah,
  t.id,
  s.x
FROM 
  t, 
  s
WHERE t.id = s.id
  AND t.x = s.x; 

I believe but can't confirm that Toad Formatter is the main perpetrator of this travesty.

Instrumentation. Use lots of it. Home grown or Mr. Kyte's debug routine. You can also use the Oracle supplied package DBMS_APPLICATION_INFO. I have gotten into the habit of using set_session_longops and it helps tremendously in monitoring those pesky long running datawarehouse programs.

Be liberal in your use of spaces: ADD_MONTHS( TRUNC( SYSDATE, 'MONTH' ), 2 )
Capitalize keywords like SELECT, INSERT, UPDATE and DELETE.
Standard functions should also be capitalized.

I'm sure I've got more and I'll add them here as time goes on.