• (cs)

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

  • (cs)

    Dang, almost made first reply.

    RPAD ('', 9, ' '); was apparently too easy.

  • (cs) in reply to christoofar

    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.

  • (cs)

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

     

  • Billy Madison (unregistered) in reply to ItsAllGeekToMe
    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!

  • (cs)

    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.

  • (cs) in reply to Manni

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

  • (cs) in reply to Manni

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

     

  • Ross (unregistered)

    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.

  • Ross (unregistered) in reply to kipthegreat
    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.
  • (cs) in reply to El Duderino
    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.

  • (cs) in reply to Ross

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

     

  • (cs)

    The world definitely doesn't have enough complex padding algorithms.  And if it does, what's one more?  Probably one of those Oracle consultants, just padding his/her check.

  • (cs) in reply to 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.

     

  • (cs)
    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);
     
  • (cs) in reply to El Duderino

    <font size="2">

    El Duderino:
    </font>

    <font size="2">

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

    </font>

    <font size="2">I'm sure there's some WTFers out there that can abuse those in equal degrees.</font>

    <font size="2">

    True. I've seen christoofar's SQL...*shiver*</font>
  • (cs) in reply to A Wizard A True Star

    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.

  • (cs) in reply to JoeS
    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

    v_first_nm := RPAD(nvl(v_first_nm, ''), 9);
    v_last_nm := RPAD(nvl(v_last_nm, ''), 15);
     
    would deal with it just fine.
  • mck9 (unregistered) in reply to El Duderino

    >>

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

    v_first_nm := RPAD(nvl(v_first_nm, ''), 9);
    v_last_nm := RPAD(nvl(v_last_nm, ''), 15);
     
    would deal with it just fine.
    <<
     
    ...for suitable values of "fine".
     
    It doesn't accomplish much to use the nvl function to convert a null value to a null value.  The code above is exactly equivalent to the code it is correcting, except possibly slower.
     
    If the first argument to rpad() is null, then the value returned is null.  An empty string is just another way of representing null.
  • (cs) in reply to Ross
    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.

    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.
  • (cs) in reply to mck9
    So the correct version would be:
     
    v_first_nm := RPAD(nvl(v_first_nm, ' '), 9);
    v_last_nm := RPAD(nvl(v_last_nm, ' '), 15);
     
    Using nvl() to convert any null value into a single space which then gets padded correctly by RPAD() to the proper length.  I can see why the original WTF code came about though.  Nested function calls are just too confusing.  The WTF is able to make the entire process much cleaner.
  • (cs)

    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;

  • (cs) in reply to mck9
    Anonymous:
    An empty string is just another way of representing null.
    In some versions of Oracle, yes.  In many (most?) other relational databases, no.
  • (cs) in reply to foxyshadis

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

    <FONT face="Courier New">SELECT col1, col2
      FROM sometable
     WHERE col3 = 'asdf'
       AND col4 IS NULL</FONT>

  • omg-wtf (unregistered)

     

            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!

     

  • (cs) in reply to mck9
    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.

  • (cs) in reply to mck9
    Anonymous:
    An empty string is just another way of representing null.

    In Oracle, this is unfortunately true (at least in the versions I know) and these NULL strings can give you many happy debugging hours, because

    length('') is not 0, but NULL
    lpad('', 5) is not '     ' but NULL
    rpad('', 5) is not '     ' but NULL

  • (cs) in reply to El Duderino
    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>
    Maybe there are some settings to make '' <> NULL, but in a default install of anything from Oracle 6 to Oracle 9i, '' is NULL
  • (cs) in reply to El Duderino

    I just need to say - it burns, it really, REALLY REALLY burns. This reminds me why I hate Oracle.

  • (cs) in reply to Alex Papadimoulis

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

    That's why we have syntax highlighting now :) Probably just as quick to notice a color as it is to notice UPPERCASE, and it has the upshot that it doesn't make your code look like it was pulled from a vault circa the Nixon administration.

  • Sou|cutter (unregistered) in reply to Alex Papadimoulis
    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.

    Since I asked you for a link, I guess it's only fair to give one to support my view also... http://www.tc-forum.org/topicus/ru28theu.htm
  • boxmonkey (unregistered) in reply to El Duderino
    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.



    I don't know about Oracle, but in MS SQL, NULL <> NULL pi
  • (cs) in reply to Sou|cutter

    UPPERCASE or lowercase is just a matter of personal style in PL/SQL, since PL/SQL is not case sensitive. Anyway, some tools like TOAD have a tendency to force their opinion about "why UPPERCASE is so much better" to innocent programs.

  • boxmonkey (unregistered) in reply to foxyshadis
    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.


    Upper-case SQL is useful for a couple of reasons. As mentioned already, it makes keywords stand out, and it makes it very easy to differentiate between keywords and table and field names (as these are usually lowercase). Finally, it is also useful because SQL frequently appears mixed in with other programming languages, so it makes it easier to tell when you're looking at code that will execute on the server, and code that is part of your application. Imagine if T-SQL looked just like C.
  • (cs) in reply to omg-wtf
    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?

  • Oracle fanboy (unregistered) in reply to El Duderino
    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.

    this is almost a wtf on its own, '' is null is documented

    SQL> select * from v$version;

    BANNER

    Oracle Database 10g Release 10.1.0.3.0 - Production PL/SQL Release 10.1.0.3.0 - Production CORE 10.1.0.3.0 Production TNS for Linux: Version 10.1.0.3.0 - Production NLSRTL Version 10.1.0.3.0 - Production

    SQL> select nvl('', 'The empty string is NULL') wtf from dual;

    WTF

    The empty string is NULL

  • (cs) in reply to Alex Papadimoulis
    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 ...

    <font face="Courier New">SELECT col1, col2
      FROM sometable
     WHERE col3 = 'asdf'
       AND col4 IS NULL</font>



    Yes, the fifth dentist recommends it!

        dZ.

  • (cs) in reply to Ross
    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.


    Don't you mean Brillant?

        dZ.

  • (cs) in reply to Billy Madison
    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 !

  • (cs) in reply to Sou|cutter
    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.

  • Gary (unregistered) in reply to emurphy
    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 );

  • (cs) in reply to Alex Papadimoulis
    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.


    I can get behind that. It doesn't work for me, but if it's better on average... Personally, I find too many letters in a row with straight lines and the same metrics blends into line noise for me; lowercase offers more variety for the pattern-matching, even without color. I don't know eclipse highlights, haven't used sql in it yet, but I think phped did and I know textpad and editplus don't.

    On the other hand the whole second half of that is uppercase, I guess he was never taught that sql variables aren't keywords. =p

    I love your spelling of highlightning. I know it's becoming more common, but it always makes me think of h-dawg and his buddy's hilita fix. The onion used to be so cool. =D
  • Dalamar (unregistered) in reply to emurphy

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

  • (cs) in reply to Dalamar

    You guys think style guides reccomending uppercase are a WTF? Wait until you've programmed in Oberon. Since it's case sensitive, Wirth (yes, the same guy who invented Pascal) had the 'brillant' idea to make keywords all uppercase. Imagine tapping CAPS LOCK every 7 characters on average. I gave up after a few hours. I mean, I like to try new things, but that was too much.

    Remember: things can always, ALWAYS get worse.

  • (cs) in reply to Alex Papadimoulis
    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.



    If you do that in PL/SQL, also writing the PL/SQL keywords (like FUNCTION, IF, WHILE etc.) in uppercase, you end up with about 60% of all words in uppercase, which is a PITA to read.
    Some editors, e.g. Slickedit, provide SQL syntax highlighting for SQL code embedded in C.
  • Kuri (unregistered) in reply to zephc

    My eyes have no cones you insensitive clod!

  • Shawn Smith (unregistered) in reply to boxmonkey

    In most RDBMSs (Oracle, MS SQL, MySQL, PostgreSQL, Ingres) null <> null. Oracle is the only one I've seen where isnull('') = true. It's great (NOT) when you're using trim(firstname) = trim(:c-variable) in your where clause and wondering why your counts are incorrect.

  • (cs) in reply to ammoQ

    The ''=NULL thingy has a "nice" side effect in Java: of course, the JDBC driver will give you a null pointer instead of an empty string for columns containting ''. A good oportunity to test how the system handles null pointer exceptins.

  • (cs) in reply to Dalamar
    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.


    Bzzt, wrong:

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

    The only assignment is to PADVAR. PADVAR does not occur on the right hand side of the assignment.
  • (cs)
    Alex Papadimoulis:

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



    Did anyone else check this link out?

    I especially like the bit where he says "both my great grandparents". I guess those red necks have close families

Leave a comment on “Horrible Orable Code”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article