• NULLPTR (unregistered)

    INSERT (NULL) INTO COMMENTS;

  • Jaloopa (unregistered) in reply to NULLPTR

    INSERT (NULL) INTO COMMENTS FROM DUAL;

    FTFY

  • (nodebb)

    It's ok, Oracle will just acquire Microsoft and that way get a SQL language which doesn't have ANY problems!

  • LCrawford (unregistered)

    I agree that Oracle is TRWTF of all time. I tried to install a development version last year. Five installs on 4 different OS later, I gave up. The final solution - find a link to an archived old version which actually installed and worked correctly.

  • snoofle (unregistered)

    I remember a few cases where sequences were set up to reset at the end of the day. To this end, they had a maximum value beyond which they would cycle back to the starting value. The idea was that the sequence ranges were set based upon the expected volume. You had to check for the sequence cycling back to the starting value, as it create problems when people ordered query results by sequence_id. Triggers of this sort to check for the sequence cycling to the starting value turned out to be needed to at least alert support people that the transaction volume had increased enough to cause the sequence range to be exceeded.

    Of course, that's no defense for this particular coding style, using a cursor or not freeing resources.

  • (nodebb)

    I'm looking forward to the day when Oracle acquires PHP. It'd be a match of the gods. The old gods, with tortured spatial geometries, tentacles, human sacrifices and stuff…

  • Dave (unregistered)

    Is there an inverse correlation between hubristic grandiosity in naming, and the quality of product that comes out?

    Oracle vs Microsoft, for example. Can't get much less grand than small and floppy, and they've taken over the world.

  • (nodebb) in reply to snoofle

    The idea was that the sequence ranges were set based upon the expected volume.

    That reminded me of the somewhat panicked reaction of the world's stock market information services to the IPO of Bank of China in about 2007.

    They pitched the price of the shares really low so that ordinary Chinese citizens would be able to buy a few, so there were lots of shares created, and they traded briskly on the first day.

    Um.

    Fourteen billion. That kind of briskly. It revealed 32-bit signed integer daily volume counters in information systems around the world, including one I was responsible for, so I had to patch in a fix and deploy it intra-day to stop it showing that the average price was negative.

    "Expected volume" is always fictional.

  • Retired Oracle DBA (unregistered)

    This isn't anywhere near the worst Oracle code I've seen. It actually is pretty close to the textbook way of doing this type of stuff back in the day. If you want to see some truly horrible Oracle code, there are much better (worse?) examples available.

    FYI... Oracle automatically closes open cursors and releases local things as the containing blocks are completed or exceptioned out of. The PL portion of PL/SQL is based on Ada. Ada feels like it was designed in the 1970s because it was designed in the late 1970s for/by (USA) governmental functions (ie: military) and their commercial contractors. I was at one when Ada was the new hot language on the block.

  • (nodebb)

    Yeah. PL/SQL has a bunch of concepts from Ada as well as other languages.

    And, the hack described in today's article is the way you implement sequences in SQL variants lacking them. If you can't use autoincrementing primary keys / identities().

  • Dave (unregistered) in reply to Steve_The_Cynic

    I once worked on a dataset where there were extreme outliers and also data errors. The only way to distinguish an outlier from an error was to have a subject-expert look at each questionable record. It would have taken forever to go through every single record, but setting up some expected ranges let us filter out the correct records so only a small proportion had to be checked.

    Similarly in the case you mention, it's fine to have an expected range that you use as a red flag requiring human intervention, but quite another to rely on it.

  • Charvell (unregistered)

    You don't have to select into a variable to use a sequence, in the simple example ":new.id := myseq.nextval;" would actually be the preferred method of using a sequence (in PL/SQL assignments are done with :=, since "=" in PL/SQL is the equivalent of "==" in most OO languages).

    I think this actually makes the WTF worse though, since that is using ~17 lines of code to do what should be a simple assign statement.

  • (nodebb)

    TRWTF is an excellent example of bad programming (as there are so many on thedailywtf.com) is somehow the fault of the language.

    BASIC is its own WTF to begin with, but if instead of:

       FOR X=1 TO 10
   PRINT X
   NEXT X

    you say:

       LET X=1
   IF X=11 THEN END
   PRINT X
   LET X=X+1
   IF X=11 THEN END
   PRINT X
   LET X=X+1
   IF X=11 THEN END
   .....

    then maybe TRWTF is not the language?

  • (nodebb) in reply to LCrawford

    Five installs on 4 different OS later, I gave up.

    You definitely did something wrong, repeatedly. It works every time, if you follow instructions and the OS is supported.

  • Vault_Dweller (unregistered) in reply to Bananafish

    Yes, he did do something wrong. He tried to install Oracle.

  • Raj (unregistered) in reply to Bananafish

    No it doesn't "work every time" and often it's not related to the o/s, it's related to specific requirements for specific o/s that are poorly documented, like shared memory settings.

    Java adds a fun layer. I fondly remember trying to install Oracle 8i on the first generation of pentium 4 and it would simply not work. The issue turned out to be that java jit didn't work on those cpu for some reason and this took a solid year to surface in oracle documentation.

  • that other guy (unregistered) in reply to dkf

    Oi! Stop trying to summon codethulhu!

  • David (unregistered)

    Actually, I'm fairly sure in (some versions of?) oracle you can delete the single dummy row from dual (with sysdba privileges).

    This causes all sorts of fun "can't happen" things to happen. It also may not have any effect at all on some queries, if the optimiser has used the "FAST DUAL" optimization to avoid actually accessing the dual table...

  • Tom (unregistered)

    I'm a PL/SQL Developer and yes, there are a lot of gotchas in this. But as with every programming language, there are right and wrong ways to perform a task. Its a pretty easy language to pick up and its nice that you can always refer to the base table to see how the data is displayed. Been my bread and butter for close to 15 years now.

  • (nodebb) in reply to Charvell

    An assignment from a sequence was not possible before Oracle version 10g (I think). Before that, use of sequence was restricted to SQL (as opposed to the PL part). This code certainly looks like it was much older than 2004, when 10g was release.

  • (nodebb) in reply to Raj

    Yes, it does work every time.

    I've been running Oracle for over 25 years. I've never had a problem installing it, getting it to run, running out of memory, or getting it to do what I want it to. I have run every type of installation you can think of, short of Exadata. I have never had any problems with it.

    OS settings are well documented. If you have memory issues, it's because you didn't read the instructions. I can't speak to Oracle 8i on a pentium specifically, but I can say that Oracle on Windows, UNIX, or any of the supported Linux platforms has always run flawlessly for me.

    I will give you that there are Oracle-isms that can be frustrating (like fetching the next 100 rows, for instance), but it's what people do instead of what they're supposed to do that is the real WTF.

  • (nodebb) in reply to David

    Yes you could delete (or insert) rows from the DUAL table, in Oracle version ancient. But not in a version from this century. DUAL evolved from a real table into a dummy table into a language-construction that requires no data access.

  • Baboon (unregistered)

    Not quite true - if the connection used does a commit the cursor gets closed. I once spent a fair bit of time tracking down a really badly written stored proc that happened to depend on that behaviour and we only discovered the bug when changing some application code. The kicker here was that we just rewrote the stored procedure about a 1000 line monstrosity into a view with a query of about 20 lines ... the senior developer who wrote that didn't know about window functions ...

  • medievalist (unregistered) in reply to Bananafish

    @Bananafish has been present for every attempted Oracle install ever, throughout history.

  • Stephen (unregistered)

    This code looks like defensive programming against a case where the sequence, itself, hadn't been created. Yes, you needed a "create sequence" command. It looks very oracle 6-ish, maybe oracle 7-ish. And the author may have had problems catching cases that the cursor simplified (again, see old versions of Oracle).

    Whilst it looks "wrong", it looks more like a series of defenses piled on top of each other.

  • Corpsomium (unregistered)

    Wow, so much useless code.

    Oracle > 10g :

    BEGIN
  :new.id := table1_seq.nextval;
END;

    And before that ?

    BEGIN
  SELECT table1_seq.nextval
  INTO :new.id
  FROM dual;
END;

    The trigger won't compile if the sequence does not exist, so the raise_application_error is not even relevant.

  • Anon (unregistered) in reply to Retired Oracle DBA

    @Retired, This isn't near the "textbook" way to do this stuff. Any book I have seen uses the 'SELECT seq.nextval INTO :new.id FROM dual' method. Granted, I haven't used PL/SQL for 15 years. Since then, the textbooks probably show the direct assignment described by @Charvell .

  • sizer99 (google)

    The biggest TRWTF of a language (that weren't TRYING to be WTFs, like Brainf@#$) is easily MUMPS, aka M. It's designed for the healthcare industry, which immediately tells you it's a crawling horror, but it's amazing just how bad it is.

    • There's only one datatype (da blob), which is implicitly coerced based on what you're doing.
    • There are no reserved words, feel free to redefine language keywords
    • Global variables, like ^x, which are shared among /every running program/ and are persistent. They're megaglobal.
    • IF/ELSE/FOR are for the remainder of the current line, so you /must/ cram multiple statements on the line.
    • Abbreviation is a 'feature' to fit stuff on that one line, so instead of typing RETURN you just put R, and instead of THEN you put T. Convenient, rite?
    • Whitespace is /forbidden/ in a line except where mandatory. 'SET a = 2 + 3' is a syntax error.
    • ELSE does not require an IF - it implicitly uses the contents of the $test variable.
    • All operators have the same precedence, just operate left to right.
    • If routine FOO calls routine BAR which calls routine BAZ, BAZ can modify BAR's locals and FOO's locals and there is no way to prevent this.

    BEER ; 99 bottles of beer song written in mumps (Brian Buydens) N STR,I,BOT S STR="bottle^bottles^of beer^on the wall^Take one down and pass it around" F I=99:-1:1 D . S BOT=$P(STR,"^",2) I I=1 S BOT=$P(STR,"^",1) . W !,!,I_" "BOT" "$P(STR,"^",3)" "$P(STR,"^",4)"." . W !,I_" "BOT" "$P(STR,"^",3)"." . W !,$P(STR,"^",5)", " . S BOT=$P(STR,"^",2) I I=2 S BOT=$P(STR,"^",1) . I I>1 W !,(I-1)" "BOT" "$P(STR,"^",3)" "$P(STR,"^",4)"." . I I=1 W !,"No more bottles of beer on the wall."

    Addendum 2019-03-27 16:01: Formatting destroyed, see http://www.99-bottles-of-beer.net/language-mumps-416.html

  • (nodebb)

    As a simpleton non-Oracle-expert without an auto-identity-value-creating (call it whatever you want according to your least unfavourite dbms) field type in Oracle (not having used the latest version), I would create one single sequence and use that as a sort of global unique identifier within my database for each and every table (and possibly combine that with a sort-of-unique-across-databases prefix. I do not specifically need my primary keys to be numeric, since I will do no math on them once created). But somehow people 'in the know' always advise strongly against this 'for performance reasons' and yet I keep forgetting the arguments they provide.

    But BTW, why not just use GUIDs for primary keys in the first place anyway?

  • NevemTeve (unregistered)

    Or simply, without trigger, you can do this: INSERT (key,value) VALUES (seq.NEXTVAL, 'Foo')

  • JohnSmith (unregistered) in reply to dkf

    Aahh…. I inherited a dataproject once that was built with PHP5 that created PHP, PL/SQL and bash scripts on the fly, depending on clicks of the user. A horrible Mikado game of tightly coupled non documented code, touch something and just wait and see what will collapse.. And to make things worse (really?) they only had a production system with a lot of people depending on it. It made me believe that walking on water or make pigs fly is easier. But the worst part was PL/SQL. I have never ever worked with such a counter-intuitive horrible language. And the 'oracle consultants'.. especially good in creating powerpoints with lots of managementspeak for astonishing amounts of money...

    A little later I found out I was developer # 5 or 6 and my predecessor left the building with slamming doors. I just left to another shop with SQL Server. Count your blessings.

  • Simon (unregistered) in reply to JiP

    But BTW, why not just use GUIDs for primary keys in the first place anyway?

    Depends on your requirements, but sequences are valuable when you need your data to be inherently ordered... sure, you could add a timestamp column of some kind, but the primary key is already there and indexed.

    Plus, if you do also have some kind of date information, you can tell that when "old" data has lower sequences than "new" data, the client has been stuffing around with SQL again, so don't be looking for any kind of data integrity...

  • Peter W. (unregistered) in reply to Mr. TA

    No problems concerning ANY is a good thing for there are so few problems not concerning ANY.

  • Christian (unregistered)

    Yes, Oracle is the champion of all WTFs. Having worked almost 15 Years with all sorts of Oracle products I've had my fair share of WTFs when it comes to installing and using their products. This code might not be the worst PL/SQL code I have seen but it's close.

    But in most cases when it comes to bad code the developer producing the bad code is to be blamed, not the Language. This one is no exception. In a "current" (meaning: 5 years old or newer) Versions of Oracle (12.1) this code isn't necessary at all, and in the release before that (10 years old) this would be a one-liner (and not the one shown in that article).

    As far as this article goes, well...

    And yes, if you want to really approximate structured error handling, you have to start nesting blocks inside of each other. Java / C# try catch? No?

    Cursors let you iterate across records. They’re very expensive, and in Oracle-land, they’re a resource that must be released. Every Statement you issue from any language is a cursor. Not sure why they are "expensive". Releasing resources after you use them is also extremely uncommon (try with resources in java?) - as far as this trigger goes the cursor will be released when it goes out of scope automatically - wheter the exception is thrown or not.

    Cursors, as mentioned, are expensive, and Oracle only lets you have so many of them. Limiting resources (like the amount of memory a session is allowed to allocate - and a cursor is just that) instead of crashing the entire OS when too much resources are allocated is a sane thing to do and is done by almost every RDBMS I have been working.

    Oracle is undoubtedly the champion of all WTFs, but if you want to bash a product then at least do it right. This code is bad - but not because Oracle is a piece of Monkey doo-doo but because the developer who wrote it was too stupid to read the documentation.

    cheers

  • Xorium (unregistered) in reply to NevemTeve

    I think you can even specify sequence.nextval as default value for the column.

  • (nodebb)

    I'm getting PTSD just from the intro, before the WTF even started.

    At my last job, we maintained a multi-thousand-line stored procedure (to be fair, probably at least 500 lines were just commented out code and comments with nothing but ticket references. We were supposed to do version control IN the code. It was full of hellishly complicated business logic: there were probably four people who mostly understood the logic we were implementing, and only two of them knew anything about the implementation.

    It was nested and looped and sublooped and full of nested queries, complex queries differing in subtle ways, and of course lots of cursors.

    I called it Grendel, privately. It was a monster. I found out after I'd been working on it for about a year, that Grendel had a mother. This creation had been spawned from another gargantuan SP. The offspring had extra logic, extra calculations, was longer and more complicated in what it did each calculation, but the parent was a more hulking and cumbersome beast entirely because the entire thing was nested within more loops and more queries, to run a whole batch of calculations at once.

    Even running Grendel's Mother against the test database was a challenge that took me days of work to unlock but I did eventually manage to write a script that would allow the devs and testers' to run it - provided they had set up all the test data perfectly. I don't want to try and estimate the hours that were spent working through tickets (against both monsters) trying to work out which unexpected results were bugs and which were correct results from very subtly incorrect test data.

    I invested about a week once going through both SPs with a fine toothed comb, reverse-engineering the business logic and reducing it to pseudo-code and descriptions a mere couple of hundred lines long. Partly because of that, I was pulled over to a project to redevelop them from scratch without any SQL and with all the shiniest modern practices. I privately called it Beowulf.

  • omnominous (unregistered) in reply to David

    And i remember the day when some had added a second record into DUAL on production, you can imagine the fun that gave

    And actually, DUAL was originally supposed to have 2 records, hence the name

  • siciac (unregistered) in reply to Bananafish

    I've been running Oracle for over 25 years. I've never had a problem installing it, getting it to run, running out of memory, or getting it to do what I want it to.

    That is one of the most audacious lies I've ever read.

  • (nodebb) in reply to siciac

    That is one of the most audacious lies I've ever read.

    I dunno.... Maybe I know what I'm doing.....?

  • (nodebb) in reply to Corpsomium

    The trigger won't compile if the sequence does not exist, so the raise_application_error is not even relevant.

    And what if someone drops or renames the sequence after the trigger has been compiled?

Leave a comment on “This Is Your Brain on PL/SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article