Comment On Horrible Orable Code

christoofar:

It's cr@p like this that reminds me why I prefer to use DB/2 and Microsoft SQL Server.

I'm sure there's some WTFers out there that can abuse those in equal degrees.

What's up with special cases for 1 and 2 space pads?  Bizarre.

Oracle...........more like............Boracle....................right?

ItsAllGeekToMe:

Oracle...........more like............Boracle....................right?

 

NO I WILL NOT MAKE OUT WITH YOU!

This girl wants to make out with me in the middle of class!

I don't write any functions inside SQL/Oracle queries, so this one took me a few minutes to figure out. Then I realized it's because I don't have nearly enough brain damage for this to make sense. Our "programmer" made a function that recreates the functionality of a built-in function but inexplicably gives special circumstances to a few situations depending on the parameter passed to it.

This is especially funny because RPAD seems to pad spaces on by default, so there is no reason to create a wrapper function that uses the space as the parameter to pass to RPAD. Unless ya got the dain brammage, that is.

it's....it's beautiful..........

kipthegreat:

Dumb question from someone who is certainly not
a brillant database genius... Is it normal for everything except
variable names to be in upper case?  That is really bad netiquette
(codiquette?) if you ask me....

El Duderino:

christoofar:

It's cr@p like this that reminds me why I prefer to use DB/2 and Microsoft SQL Server.

I'm sure there's some WTFers out there that can abuse those in equal degrees.

What's up with special cases for 1 and 2 space pads?  Bizarre.

I know ...  before yesterday this code looked like

  IF SPACES = 1
    THEN
        RETURN PAD;
    ELSIF SPACES = 2
    THEN
        RETURN PAD || PAD;

    ELSIF SPACES = 3
    THEN
        RETURN PAD || PAD || PAD;

    ELSIF SPACES = 4
    THEN
        RETURN PAD || PAD || PAD || PAD;

....

    ELSIF SPACES = 99
    THEN
        RETURN PAD || PAD || ..|| PAD||PAD || PAD || PAD;

But thanks to yesterday's WTF State Management post, someone actually tried to clean up this code getting rid of nearly all those if statements.  That has to be what is going on here.

I love it. "I'm gonna call this custom function to do the right-padding. And just in case it doesn't pad it with the right number of spaces, I'm going to call the built-in function to do the right-padding all over again."

Translation: "Sometimes this RPAD_SPACES function works, and sometimes it doesn't, but I'll be damned if I spend the 30 minutes it would take to figure out why. So let me just do this double-checking thing and be done with it. Sure it's going to increase maintenance time in the future, but I am a highly-paid consultant and I will be long gone by then."

Wait, it's worse than I thought! It's calling the RPAD_SPACES function when the value is NULL. So they could have just hardcoded a string of nine or fifteen spaces, and not even called RPAD_SPACES in the first place.

El Duderino:

christoofar:

It's cr@p like this that reminds me why I prefer to use DB/2 and Microsoft SQL Server.

I'm sure there's some WTFers out there that can abuse those in equal degrees.

A Wizard A True Star:

Wait, it's worse than I thought! It's calling the RPAD_SPACES function when the value is NULL. So they could have just hardcoded a string of nine or fifteen spaces, and not even called RPAD_SPACES in the first place.

No way dude, you never know when 'space' will change.

JoeS:

I've never worked with Oracle, and I spent about 15 seconds reading the link to the RPAD function.  I'm also totally taking syntax based on the WTF code.  But wouldn't this do the same thing?  Unless RPAD doesn't treat NULL values as blanks in which case you just need a simple IF to eliminate that case.

 

/* Preparing First and Last Name */

v_first_nm := RPAD(v_first_nm,9);

v_last_nm := RPAD(v_last_nm,15);

Without checking I couldn't say exactly how RPAD handles nulls but I know that

Without checking I couldn't say exactly how RPAD handles nulls but I know that

Anonymous:

kipthegreat:

Dumb question from someone who is certainly not
a brillant database genius... Is it normal for everything except
variable names to be in upper case?  That is really bad netiquette
(codiquette?) if you ask me....

Yes, it is typical for SQL reserved words / built-in functions to be in
upper case, and database / table  / column names to be in mixed
case.  That's the style in MS SQL Books Online, anyway. But it's a
rule most often observed in the breach.

Here's a question for you, don't ponder it too hard...

How many drugs would you have to take, over what period of time, for this loop to actually make any sense?

        FOR LOOPVAR IN 1 ..SPACES - 2
        LOOP
            PADVAR := PAD || RPAD ( PAD
                                  , LOOPVAR + 1
                                  , ' ' );
        END LOOP;
        RETURN PADVAR;

Anonymous:

An empty string is just another way of
representing null.

foxyshadis:

Upper-case SQL always seemed like a throwback to the COBOL and FORTRAN days to me; after all, they are contemporaries. Weird how that particular habit has survived the lower_casing and then CamelCasing of every other language, even if it is slowly dying.

It's actually the recommend way of doing it. Studies have shown that UPPERCASE keywords are much easier to read ...

SELECT col1, col2
  FROM sometable
 WHERE col3 = 'asdf'
   AND col4 IS NULL

        FOR LOOPVAR IN 1 ..SPACES - 2
        LOOP
            PADVAR := PAD || RPAD ( PAD
                                  , LOOPVAR + 1
                                  , ' ' );
        END
        LOOP;
        RETURN PADVAR;

So, let me see if I have this straight - if I call RPAD_SPACES(99) (assume I have a head injury or whatever), I have the satisfaction of knowing that it constructed the return values for RPAD_SPACES(3) through RPAD_SPACES(98) (using RPAD, 'natch) and promptly tossed them aside.  Brillant!

Mck9:

>>

An empty string is just another way of representing null.

I've only worked with Oracle version 8 and higher and I guarantee that '' <> NULL.

Anonymous:

An empty string is just another way of representing null.

El Duderino:

I've only worked with Oracle version 8 and higher and I guarantee that '' <> NULL.

SQL*Plus: Release 8.1.7.0.0 - Production on Di Aug 30 23:46:03 2005





(c) Copyright 2000 Oracle Corporation.  All rights reserved.








Connected to:


Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production


With the Partitioning option


JServer Release 9.2.0.6.0 - Production





SQL> select * from dual where '' is null;





D


-


X





SQL>

Studies have shown that UPPERCASE keywords are much easier to read

El Duderino:

Mck9:

>>

An empty string is just another way of representing null.

I've only worked with Oracle version 8 and higher and I guarantee that '' <> NULL.

foxyshadis:

Upper-case SQL always seemed like a throwback to the COBOL and FORTRAN
days to me; after all, they are contemporaries. Weird how that
particular habit has survived the lower_casing and then CamelCasing of
every other language, even if it is slowly dying.

Anonymous:

So, let me see if I have this
straight - if I call RPAD_SPACES(99) (assume I have a head injury
or whatever), I have the satisfaction of knowing that it constructed
the return values for RPAD_SPACES(3) through RPAD_SPACES(98) (using
RPAD, 'natch) and promptly tossed them aside.  Brillant!

 

Actually, no, you won't have that satisfaction.

Exercise for the student:  Why not?

El Duderino:

Mck9:

>>

An empty string is just another way of representing null.

I've only worked with Oracle version 8 and higher and I guarantee that '' <> NULL.

Alex Papadimoulis:

foxyshadis:

Upper-case
SQL always seemed like a throwback to the COBOL and FORTRAN days to me;
after all, they are contemporaries. Weird how that particular habit has
survived the lower_casing and then CamelCasing of every other language,
even if it is slowly dying.

It's actually the recommend way of doing it. Studies have shown that UPPERCASE keywords are much easier to read ...

SELECT col1, col2
  FROM sometable
 WHERE col3 = 'asdf'
   AND col4 IS NULL

Anonymous:

This code is brilliant. It optimizes out the function RPAD, which is
known to be slow, with faster code for one or two char strings.

Anonymous:

ItsAllGeekToMe:

Oracle...........more like............Boracle....................right?

 

 

NO I WILL NOT MAKE OUT WITH YOU!

This girl wants to make out with me in the middle of class!

well done !

Anonymous:

Studies have shown that UPPERCASE keywords are much easier to read

Care to cite any studies?  A quick google found studies supporting mixed case, but I haven't seen anything supporting your claim.  As far as I am aware there is no consensus on that topic outside of specific contexts (and even then...).  I do know that for me it is more difficult to read all uppercase than mixed or lowercase.  When reading literature, mixed and all-lowercase is the norm, so I guess my theory is that the eye is quicker to recognize words presented in this, the normal manner.

I seem to have misplaced my references. They were in SQL PROGRAMMING STYLE by Joe Celko. I used to do lower case with syntax hilighting, but he had good evidence from recent studies (1997 I think) which demonstrated that SQL code with the upper case keywords was easier to read, understand, and therefore less error prone.

Consider that, especially when it comes to SQL, syntax hilighting is less avaiable than other languages. SQL Strings in applications for example.

emurphy:

Anonymous:

So, let me see if I have this
straight - if I call RPAD_SPACES(99) (assume I have a head injury
or whatever), I have the satisfaction of knowing that it constructed
the return values for RPAD_SPACES(3) through RPAD_SPACES(98) (using
RPAD, 'natch) and promptly tossed them aside.  Brillant!

 

Actually, no, you won't have that satisfaction.

Exercise for the student:  Why not?

Because it will fall over on anything over 60 characters

PADVAR VARCHAR2 ( 60 );

Alex Papadimoulis:

to have misplaced my references. They were in SQL PROGRAMMING STYLE
by Joe Celko. I used to do lower case with syntax hilighting, but he
had good evidence from recent studies (1997 I think) which demonstrated
that SQL code with the upper case keywords was easier to read,
understand, and therefore less error prone.

Consider that, especially when it comes to SQL, syntax hilighting is
less avaiable than other languages. SQL Strings in applications for
example.

Alex Papadimoulis:

I seem to have misplaced my
references. They were in SQL PROGRAMMING STYLE by Joe Celko. I used to
do lower case with syntax hilighting, but he had good evidence from
recent studies (1997 I think) which demonstrated that SQL code with the
upper case keywords was easier to read, understand, and therefore less
error prone.

Consider that, especially when it comes to SQL, syntax hilighting is
less avaiable than other languages. SQL Strings in applications for
example.

Anonymous:

Because those values, once calculated, are concatenated onto the
working value, not tossed aside. Each calculation is an integral part
of the final result.

PADVAR := PAD || RPAD ( PAD, LOOPVAR + 1, ' ' );

Alex Papadimoulis:

or even so I could share the obligatory Oracle Consultant link.